MySQL 数据类型

2021-09-05 06:44:41

7.2 整型

ZEROFILL——不足指定位数的时候用0补齐

CREATE TABLE mytal(
    f1,INT(5),
	f2,INT(5) ZEROFILL
)
#ZEROFILL是一个无符号的数，默认有约束UNSIGN
#8.0.17之后不推荐使用显示宽度

在实际开发中，系统故障产生的成本，远远高于添加几个字段存储空间产生的成本。

7.3 浮点数

FOLAT和DOUBLE的区别

FLOAT占用字节少（4字节），精度低。DOUBLE占用字节大（8字节），精度高。
FLOAT（m.d）m为位数，d为小数位数。如：FOLAT(5,2)表示【-999.99,999.99】

小数位超过范围，则进行四舍五入。

CREATE TABLE mytal(f1 FLOAT(5,2))
INSERT INTO mytal VALUES(123.456)
#结果添加为123.46

整数位超过范围则会报错。

CREATE TABLE mytal(f1 FLOAT(5,2))
INSERT INTO mytal VALUES(1234.45)
#添加失败，报错。

小数位的四舍五入导致整数位超过范围也会报错。

CREATE TABLE mytal(f1 FLOAT(5,2))
INSERT INTO mytal VALUES(999.999)
#添加失败，报错。

精度情况

CREATE TABLE mytal(f1 DOUBLE)
INSERT INTO mytal VALUES(0.19),(0.47),(0.44)
SELECT SUM(f1) FROM mytal
#结果为1.0999……
SELECT SUM(f1)=1.1
FROM mytal;
#结果为0，表示不相等。

7.4 定点数 DECLMAL

浮点数精度不准确。使用定点数DECIMAL(M,N)，占用字节M+2。默认精度（10,0）
同等字节下，没有DOUBLE存储内容多
底层中使用字符串存储

精度问题

CREATE TABLE mytal(f1 DECIMAL)
INSERT INTO mytal VALUES(0.19),(0.47),(0.44)
SELECT SUM(f1) FROM mytal
#结果为1.1

SELECT SUM(f1)=1.1
FROM mytal;
#结果为1，表示相等。

数据需要精准，则使用定点数。浮点数在精度要求不那么高的情况是使用

7.5 位类型（了解）

CREATE TABLE mytal(
    f1 BIT,
    f2 BIT(5),
    f3 BIT(64)
)

7.6 日期与时间类型

YEAR使用四位存储的方式YEAR(4)
YEAR的范围是1970-2155

两位的情况下

0-69指2000年-2069年
70-99指1970年-1999年

CREATE TABLE mytal(
    f1 YEAR,
    f2 YEAR(2)
)
INSERT INTO mytal('1999','70')
#结果为1999和1970
INSERT INTO mytal('1999','69')
#结果为1999和2069

DATE格式'YYYY-MM-DD'或者'YY-MM-DD'使用两位的存储方式规则同上。
DATETIME 和DATESTAMP
- DATETIME需要八个字节，格式：YYYY-MM-DD HH:MM:SS或者YY-MM-DD HH:MM:SS规则同上。
- TIMESTAMP只需要4个字节，但是时间的范围只能在1970-01-01 00:00:01至2038-01-19 03:14:07
- 涉及时区之间的操作的适应TIMESTAMP?
- TIMESTAMP使用毫秒数进行存储，读取时会根据当前时区添加对应的毫秒数（存：东八区，读：东九区。结果+1h）
开发中使用DATETIME最多。

7.7 字符串

CHAR和VARCHAR
- CHAR（M）占用M字节，不满M也会补满至M，默认只有1字节
- VARCHAR（M）占用M+1字节。VARCHAR必须在定义时写上宽度。

哪些情况使用 CHAR 或 VARCHAR 更好

类型	特点	空间上	时间上	适用场景
CHAR(M)	固定长度	浪费存储空间	效率高	存储不大，速度要求高
VARCHAR(M)	可变长度	节省存储空间	效率低	非CHAR的情况

开发中经验：

TEXT文本类型，可以存比较大的文本段，搜索速度稍慢，因此如果不是特别大的内容，建议使用CHAR，VARCHAR来代替。还有TEXT类型不用加默认值，加了也没用。而且text和blob类型的数据删除后容易导致“空洞”，使得文件碎片比较多，所以频繁使用的表不建议包含TEXT类型字段，建议单独分出去，单独用一个表。

7.8 枚举和SET

CREATE TABLE mytal(
	season 			
    ENUM('春','夏','秋','冬','UNKROW')
)
#只能添加枚举中的一个，如下会报错
INSERT INTO mytal VALUES('春','夏')
#除此之外还可以添加NULL，也可以使用索引。
INSERT INTO mytal VALUES(1)
INSERT INTO mytal VALUES('2')
INSERT INTO mytal VALUES(NULL)

CREATE TABLE mytal(
	season 				
    SET('春','夏','秋','冬','UNKROW')
)
#可以填写多个集合里的元素
INSERT INTO mytal VALUES('春','夏')

7.9小结及选择建议

在定义数据类型时，如果确定是整数，就用 INT；如果是小数，一定用定点数类型 DECIMAL(M,D)；如果是日期与时间，就用 DATETIME。

这样做的好处是，首先确保你的系统不会因为数据类型定义出错。不过，凡事都是有两面的，可靠性好，并不意味着高效。比如，TEXT 虽然使用方便，但是效率不如 CHAR(M) 和 VARCHAR(M)。

关于字符串的选择，建议参考如下阿里巴巴的《Java开发手册》规范：

阿里巴巴《Java开发手册》之MySQL数据库：

任何字段如果为非负数，必须是 UNSIGNED
【强制】小数类型为 DECIMAL，禁止使用 FLOAT 和 DOUBLE。
- 说明：在存储的时候，FLOAT 和 DOUBLE 都存在精度损失的问题，很可能在比较值的时候，得到不正确的结果。如果存储的数据范围超过 DECIMAL 的范围，建议将数据拆成整数和小数并分开存储。
【强制】如果存储的字符串长度几乎相等，使用 CHAR 定长字符串类型。
【强制】VARCHAR 是可变长字符串，不预先分配存储空间，长度不要超过 5000。如果存储长度大于此值，定义字段类型为 TEXT，独立出来一张表，用主键来对应，避免影响其它字段索引效率。

码农公寓