百木园目录
- 1 整数类型
- 1.1 可选属性
- 1.1.1 M
- 1.1.2 UNSIGNED
- 1.1.3 ZEROFILL
- 1.1 可选属性
- 2 浮点类型
- 2.1 精度误差
- 3 定点数类型
- 3.1 数据精度说明
- 3.2 类型介绍
- 4 位类型
- 5 日期与时间类型
- 5.1 TIMESTAMP
- 5.2 TIMESTAMP与DATETIME的区别
- 6 文本字符串类型
- 6.1 CHAR与VARCHAR
- 6.1.1 CHAR
- 6.1.2 VARCHAR
- 6.1.3 哪些情况使用CHAR或VARCHAR更好
- 6.2 TEXT类型
- 6.2.1 使用TEXT类型的经验
- 6.3 ENUM类型
- 6.3.1 使用
- 6.1 CHAR与VARCHAR
- 小结
| 类型 | 类型举例 |
|---|---|
| 整数类型 | TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT |
| 浮点类型 | FLOAT、DOUBLE |
| 定点数类型 | DECIMAL |
| 位类型 | BIT |
| 日期时间类型 | YEAR、TIME、DATE、DATETIME、TIMESTAMP |
| 文本字符串类型 | CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT |
| 枚举类型 | ENUM |
| 集合类型 | SET |
| 二进制字符串类 | BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB |
| JSON类型 | JSON对象、JSON数组 |
| 空间数据类型 | 单值类型：GEOMETRY、POINT、LINESTRING、POLYGON；集合类型：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、 |
- 常见数据类型的属性
| MySQL关键字 | 含义 |
|---|---|
| NULL | 数据列可包含NULL值 |
| NOT NULL | 数据列不允许包含NULL值 |
| DEFAULT | 默认值 |
| PRIMARY KEY | 主键 |
| AUTO_INCREMENT | 自动递增，适用于整数类型 |
| UNSIGNED | 无符号 |
| CHARACTER SET name | 指定一个字符集 |
1 整数类型
TINYINT、SMALLINT、MEDIUMINT、INT（INTEGER）和 BIGINT
| 整数类型 | 字节 |
|---|---|
| TINYINT | 1 |
| SMALLINT | 2 |
| MEDIUMINT | 3 |
| INT（INTEGER） | 4 |
| BIGINT | 8 |
1.1 可选属性
1.1.1 M
M : 表示显示宽度，M的取值范围是(0, 255)，该功能需要搭配“ZEROFILL”使用
从MySQL 8.0.17开始，整数数据类型不推荐使用显示宽度属性
整型数据类型可以在定义表结构时指定所需要的显示宽度，如果不指定，则系统为每一种类型指定默认的宽度值
1.1.2 UNSIGNED
UNSIGNED：无符号类型（非负）
1.1.3 ZEROFILL
ZEROFILL：零填充
2 浮点类型
FLOAT、DOUBLE、REAL
| 整数类型 | 字节 |
|---|---|
| FLOAT | 4 |
| DOUBLE | 8 |
2.1 精度误差
浮点数类型有个缺陷，就是不精准
在编程中，如果用到浮点数，要特别注意误差问题，因为浮点数是不准确的，所以我们要避免使用“=”来判断两个数是否相等。同时，在一些对精确度要求较高的项目中，千万不要使用浮点数，不然会导致结果错误，甚至是造成不可挽回的损失。
那么，MySQL 有没有精准的数据类型呢？当然有，这就是定点数类型： DECIMAL 。
从MySQL 8.0.17开始，
FLOAT(M,D)和DOUBLE(M,D)用法在官方文档中已经明确不推荐使用。另外，关于浮点型的UNSIGNED也不推荐使用了
3 定点数类型
MySQL中的定点数类型只有 DECIMAL 一种类型
DECIMAL(M,D) 的方式表示高精度小数
3.1 数据精度说明
M称为 精度 ，D称为 标度 。(M,D)中 M = 整数位 + 小数位，D = 小数位。 0<=M<=65，0<=D<=30。
例如，定义DECIMAL（5,2）的类型，表示该列取值范围是-999.99~999.99。
3.2 类型介绍
DECIMAL(M,D)的最大取值范围与DOUBLE类型一样，但是有效的数据范围是由M和D决定的。- 定点数在MySQL内部是以 字符串 的形式进行存储，这就决定了它一定是精准的
- 当DECIMAL类型不指定精度和标度时，其默认为
DECIMAL(10,0)。当数据的精度超出了定点数类型的精度范围时，则MySQL同样会进行四舍五入处理。 - DECIMAL可以添加
UNSIGNED属性
4 位类型
BIT
BIT类型中存储的是二进制值，类似010110。
BIT(M)
| 长度 | 长度范围 | 占用空间 |
|---|---|---|
| M | 1 <= M <= 64 | 约为 (M + 7) / 8 个字节 |
BIT类型，如果没有指定(M)，默认是1位。这个1位，表示只能存1位的二进制值。
使用b+0查询数据时，可以直接查询出存储的十进制数据的值
select b + 0 from table;
5 日期与时间类型
MySQL不同的版本可能有所差异
MySQL8.0版本支持的日期和时间类型主要有：YEAR类型、TIME类型、DATE类型、DATETIME类型和TIMESTAMP类型
YEAR类型通常用来表示年DATE类型通常用来表示年、月、日TIME类型通常用来表示时、分、秒DATETIME类型通常用来表示年、月、日、时、分、秒TIMESTAMP类型通常用来表示带时区的年、月、日、时、分、秒
5.1 TIMESTAMP
TIMESTAMP类型也可以表示日期时间，其显示格式与DATETIME类型相同，都是 YYYY-MM-DD HH:MM:SS ，需要4个字节的存储空间。
但是TIMESTAMP存储的时间范围比DATETIME要小很多，只能存储“1970-01-01 00:00:01 UTC”到“2038-01-19 03:14:07 UTC”之间的时间
存储数据的时候需要对当前时间所在的时区进行转换，查询数据的时候再将时间转换回当前的时区。因此，使用TIMESTAMP存储的同一个时间值，在不同的时区查询时会显示不同的时间。
5.2 TIMESTAMP与DATETIME的区别
TIMESTAMP存储空间比较小，表示的日期时间范围也比较小- 底层存储方式不同，
TIMESTAMP底层存储的是毫秒值，距离1970-1-1 0:0:0 0毫秒的毫秒值。 - 两个日期比较大小或日期计算时，
TIMESTAMP更方便、更快。 TIMESTAMP和时区有关。TIMESTAMP会根据用户的时区不同，显示不同的结果。而DATETIME则只能反映出插入时当地的时区，其他时区的人查看数据必然会有误差的。
6 文本字符串类型
MySQL中，文本字符串总体上分为 CHAR 、 VARCHAR 、 TINYTEXT 、 TEXT 、 MEDIUMTEXT 、LONGTEXT 、 ENUM 、 SET 等类型。
6.1 CHAR与VARCHAR
| 类型 | 特点 | 长度 | 长度范围 | 占用的存储空间 |
|---|---|---|---|---|
| CHAR(M) | 固定长度 | M | 0 <= M <= 255 | M个字节 |
| VARCHAR(M) | 可变长度 | M | 0 <= M <= 65535 | (实际长度 + 1)个字节 |
6.1.1 CHAR
CHAR(M)类型一般需要预先定义字符串长度。如果不指定(M)，则表示长度默认是1个字符。- 如果保存时，数据的实际长度比CHAR类型声明的长度小，则会在 右侧填充 空格以达到指定的长度。当MySQL检索CHAR类型的数据时，CHAR类型的字段会去除尾部的空格。
- 定义CHAR类型字段时，声明的字段长度即为CHAR类型字段所占的存储空间的字节数。
6.1.2 VARCHAR
- VARCHAR(M) 定义时， 必须指定 长度M，否则报错。
- 检索VARCHAR类型的字段数据时，会保留数据尾部的空格。VARCHAR类型的字段所占用的存储空间为字符串实际长度加1个字节。
#Column length too big for column \'NAME\' (max = 21845);
CREATE TABLE test_varchar2(
NAME VARCHAR(65535) #错误
);
6.1.3 哪些情况使用CHAR或VARCHAR更好
具体存储引擎中的情况：
MyISAM数据存储引擎和数据列：MyISAM数据表，最好使用固定长度(CHAR)的数据列代替可变长度(VARCHAR)的数据列。这样使得整个表静态化，从而使 数据检索更快 ，用空间换时间。MEMORY存储引擎和数据列：MEMORY数据表目前都使用固定长度的数据行存储，因此无论使用CHAR或VARCHAR列都没有关系，两者都是作为CHAR类型处理的。InnoDB存储引擎，建议使用VARCHAR类型。因为对于InnoDB数据表，内部的行存储格式并没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），而且主要影响性能的因素是数据行使用的存储总量，由于char平均占用的空间多于varchar，所以除了简短并且固定长度的，其他考虑varchar。这样节省空间，对磁盘I/O和数据存储总量比较好。
6.2 TEXT类型
在MySQL中，TEXT用来保存文本类型的字符串，总共包含4种类型，分别为TINYTEXT、TEXT、 MEDIUMTEXT 和 LONGTEXT 类型。
在向TEXT类型的字段保存和查询数据时，系统自动按照实际长度存储，不需要预先定义长度。这一点和VARCHAR类型相同。
每种TEXT类型保存的数据长度和所占用的存储空间不同，如下：
| 类型 | 特点 | 长度 | 长度范围 | 占用的存储空间 |
|---|---|---|---|---|
| TINYTEXT | 小文本、可变长度 | L | 0 <= L <= 255 | L + 2 个字节 |
| TEXT | 文本、可变长度 | L | 0 <= L <= 65535 | L + 2 个字节 |
| MEDIUMTEXT | 中等文本、可变长度 | L | 0 <= L <= 16777215 | L + 3 个字节 |
| LONGTEXT | 大文本、可变长度 | L | 0 <= L <= 4GB | L + 4 个字节 |
- 由于实际存储的长度不确定，MySQL不允许TEXT类型的字段做主键。遇到这种情况，你只能采用CHAR(M)，或者 VARCHAR(M)
- 跟VARCHAR一样，在保存和查询数据时，不会删除数据尾部的空格
6.2.1 使用TEXT类型的经验
- TEXT文本类型，可以存比较大的文本段，搜索速度稍慢，因此如果不是特别大的内容，建议使用CHAR， VARCHAR来代替
- 还有TEXT类型不用加默认值，加了也没用
- 而且text和blob类型的数据删除后容易导致“空洞”，使得文件碎片比较多，所以频繁使用的表不建议包含TEXT类型字段，建议单独分出去，单独用一个表
6.3 ENUM类型
枚举类型
ENUM类型的取值范围需要在定义字段时进行指定
| 范围 | 占用的存储空间 |
|---|---|
| 1 <= L <= 65535 | 1或2个字节 |
- 当ENUM类型包含1～255个成员时，需要1个字节的存储空间；
- 当ENUM类型包含256～65535个成员时，需要2个字节的存储空间。
- ENUM类型的成员个数的上限为65535个。
6.3.1 使用
CREATE TABLE test_enum(
season ENUM(\'春\',\'夏\',\'秋\',\'冬\',\'unknow\')
);
INSERT INTO test_enum VALUES(\'春\'),(\'秋\');
# 忽略大小写
INSERT INTO test_enum VALUES(\'UNKNOW\');
# 允许按照角标的方式获取指定索引位置的枚举值
INSERT INTO test_enum VALUES(\'1\'),(3);
# Data truncated for column \'season\' at row 1
INSERT INTO test_enum VALUES(\'ab\');
# 当ENUM类型的字段没有声明为NOT NULL时，插入NULL也是有效的
INSERT INTO test_enum VALUES(NULL);
小结
在定义数据类型时，如果确定是 整数 ，就用 INT ； 如果是 小数 ，一定用定点数类型DECIMAL(M,D) ； 如果是日期与时间，就用 DATETIME 。
阿里巴巴《Java开发手册》之MySQL数据库：
- 任何字段如果为非负数，必须是
UNSIGNED 【 强制 】小数类型为 DECIMAL，禁止使用 FLOAT 和 DOUBLE。- 说明：在存储的时候，FLOAT 和 DOUBLE 都存在精度损失的问题，很可能在比较值的时候，得到不正确的结果。如果存储的数据范围超过 DECIMAL 的范围，建议将数据拆成整数和小数并分开存储。
【 强制 】如果存储的字符串长度几乎相等，使用 CHAR 定长字符串类型。【 强制 】VARCHAR 是可变长字符串，不预先分配存储空间，长度不要超过 5000。如果存储长度大于此值，定义字段类型为 TEXT，独立出来一张表，用主键来对应，避免影响其它字段索引效率。
来源：https://www.cnblogs.com/xnmk-zhan/p/15993602.html
本站部分图文来源于网络，如有侵权请联系删除。