【MySQL】数据类型

距离上次更新本文已经过去了 372 天，文章部分内容可能已经过时，请注意甄别

mysql 的数据类型老多了，单开一篇博客记录一下吧

plaintext

1	mysql Ver 15.1 Distrib 10.3.28-MariaDB, for Linux (x86_64) using readline 5.1

1. 数值类型

数据类型	说明	字节
BIT(M)	位类型，M 指定位数，默认位 1，可 1-64
TINYINT [UNSIGNED]	有符号 `-127 ~ 128`；无符号 `0 ~ 255`	1
BOOL	用 0 和 1 表示真假（实际为 `tinyint(1)`)	1
SMALLINT [UNSIGNED]	有符号 `-2^15 ~ 2^15 -1`; 无符号 `2^16 -1`	2
MEDIUMINT [UNSIGNED]	有符号 `-2^23 ~2^23 -1`; 无符号 `2^24 -1`	3
INT [UNSIGNED]	有符号 `-2^31 ~ 2^31 -1`; 无符号 `2^32 -1`	4
BIGINT [UNSIGNED]	有符号 `-2^63 ~ 2^63 -1`; 无符号 `2^64 -1`	8
FLOAT [(M,D)] [UNSIGNED]	M 指定显示长度，D 指定小数位数	4
DOUBLE [(M,D)] [UNSIGNED]	同上，精度更高	8
DECIMAL (M,D) [UNSIGNED]	同上，M 和 D 必须给出

1.1 插入超范围数据

和编译器中会对数据进行截断不同，mysql 中的数据范围必须严格遵守其定义的范围

plaintext

1
2
3

MariaDB [hello]> create table test(
    -> id TINYINT UNSIGNED);
Query OK, 0 rows affected (0.021 sec)

这里我创建了一个使用 TINYINT 类型的表，向里面插入如下的正确数据

plaintext

MariaDB [hello]> insert into test value (3);
Query OK, 1 row affected (0.040 sec)

MariaDB [hello]> insert into test value (0);
Query OK, 1 row affected (0.040 sec)

MariaDB [hello]> insert into test value (10);
Query OK, 1 row affected (0.007 sec)

再尝试插入负数和超过范围的数

plaintext

MariaDB [hello]> insert into test value (-1);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
MariaDB [hello]> insert into test value (300);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
MariaDB [hello]>

可以看到，mysql 阻止了我们的插入。

1.2 约束

这就是 mysql 对应用层的约束，你必须遵守先前对表中对字段类型的定义和范围的要求，才能将数据插入到数据库中。

这样就保证了，我们在 mysql 中看到的数据，是能保证范围正确的！

并不会出现这个数据是因为截断而被存入的缺省值，从而造成的非精确问题。

当然，你要是在代码中强转数据插入，mysql 肯定看不出来。但那样就是程序云的锅了，和 mysql 本身无关！

注意，当一个无符号数据无法被 int 类型存放的时候，我们不要用无符号 int 来存放这个数据，而应该使用 bigint。这样才能保证数据一定能被存入。

而我们要存放的数据没有负数的时候（比如年龄、当前时间戳）则建议使用无符号数来存放。因为这样能显式告知其他需要操作这个数据库的人，当前字段是没有负数的。

1.3 BIT 类型

BIT 类型就是用比特位来表示一些 0/1 的信息

用户在线状态
用户性别（不考虑不选择的情况下）
用户是否已经验证邮箱
用户是否绑定了手机
用户一系列权限位
…

这些数据都是只有真假区分的，使用 bit 来保存的时候，就能节省一定的空间。

1.4 浮点数

plaintext

1	FLOAT [(M,D)] [UNSIGNED]

浮点数这里的 M 和 D 是什么意思呢？

sql

1	alter table test add ft float(4,2) after id;

这里我先往之前已有的 test 表中插入一个浮点数，来测试一下情况

plaintext

MariaDB [hello]> desc test;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id    | tinyint(3) unsigned | YES  |     | NULL    |       |
| ft    | float(4,2)          | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
2 rows in set (0.002 sec)

当插入一个整数时，会自动后补 0

plaintext

MariaDB [hello]> insert into test values (1,99);
Query OK, 1 row affected (0.007 sec)

MariaDB [hello]> select * from test;
+------+-------+
| id   | ft    |
+------+-------+
|    3 |  NULL |
|    0 |  NULL |
|   10 |  NULL |
|    1 | 99.00 |
+------+-------+
4 rows in set (0.000 sec)

插入负数也是没问题的

plaintext

MariaDB [hello]> insert into test values (1,-99.3);
Query OK, 1 row affected (0.008 sec)

MariaDB [hello]> select * from test;
+------+--------+
| id   | ft     |
+------+--------+
|    3 |   NULL |
|    0 |   NULL |
|   10 |   NULL |
|    1 |  99.00 |
|    1 | -99.30 |
+------+--------+
5 rows in set (0.001 sec)

但当你想插入如下数字的时候，就开始报错了

plaintext

MariaDB [hello]> insert into test values (1,-99.99542);
ERROR 1264 (22003): Out of range value for column 'ft' at row 1
MariaDB [hello]> insert into test values (1,100.99542);
ERROR 1264 (22003): Out of range value for column 'ft' at row 1
MariaDB [hello]> insert into test values (1,100.9942);
ERROR 1264 (22003): Out of range value for column 'ft' at row 1
MariaDB [hello]> insert into test values (1,100.99);
ERROR 1264 (22003): Out of range value for column 'ft' at row 1

简单总结

浮点数的 M 代表浮点数总共的位数（包括整数部分和小数部分）
后续的 D 代表小数点之后的位数
如果 D 为 2，则可以插入 0.994，不可以插入 0.995（临界数据只能四舍，不支持五入，因为五入了之后就相当于超限了）
插入 30.945 会被四舍五入为 30.95
float(4,2) 的范围就是 -99.99 ~ 99.99; 如果是无符号，则为 0 ~ 99.99

plaintext

MariaDB [hello]> insert into test values (1,30.945);
Query OK, 1 row affected (0.003 sec)

MariaDB [hello]> select * from test;
+------+--------+
| id   | ft     |
+------+--------+
|    3 |   NULL |
|    0 |   NULL |
|   10 |   NULL |
|    1 |  99.00 |
|    1 | -99.30 |
|    1 | -99.34 |
|    1 |  30.95 |
+------+--------+
7 rows in set (0.000 sec)

1.5 decimal

sql

1	DECIMAL (M,D) [UNSIGNED]

这个数据类型和 float 的区别也是精度上的

float 最大精度约 7 位
decimal 最大 M 为 65，D 为 30；省略时 D 默认为 0，M 默认为 10

在我当前的 mariadb 中进行测试，同样是插入 10.123456789，float 显示的是 10.12345695，decimal 显示的是 10.12345679

很明显，decimal 的精准度更高，正确对 10.123456789 进行四舍五入了。而 float 就把数据给搞坏了。

plaintext

MariaDB [hello]> drop table test;
Query OK, 0 rows affected (0.046 sec)

MariaDB [hello]> create table test (
    -> f1 float(10,8),
    -> f2 decimal(10,8));
Query OK, 0 rows affected (0.058 sec)

MariaDB [hello]> insert into test values (10.123456789,10.123456789);
Query OK, 1 row affected, 1 warning (0.010 sec)

MariaDB [hello]> select * from test;
+-------------+-------------+
| f1          | f2          |
+-------------+-------------+
| 10.12345695 | 10.12345679 |
+-------------+-------------+
1 row in set (0.001 sec)

所以，如果需要存储小数，最好使用 decimal

1.6 浮点数取整的几种方式

这个和 mysql 无关，只是记录一下

零向取整：丢弃小数部分
向上取整：2.1 视作 3，-2.5 视作 - 2（2.0 不变）
向下取整：2.1 视作 2，-2.5 视作 - 3（2.0 不变）
四舍五入（刚刚的测试能看到，mysql 采用的是这个）

2. 文本、二进制类型

数据类型	说明
CHAR (SIZE)	定长字符串，最大 255
VARCHAR (SIZE)	可变长度字符串，最大长度 65535
BLOB	二进制数据
TEXT	大文本，支持全文索引

TEXT 和 VARCHAR 都是支持全文索引的！

sql

MariaDB [hello_mysql]> CREATE TABLE articles (
    ->     id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ->     title VARCHAR(200),
    ->     body TEXT DEFAULT '',
    ->     FULLTEXT (title,body) 
    -> )engine=MyISAM;
Query OK, 0 rows affected (0.003 sec)

2.1 char

在 mysql 中，size 并不是字节，而是字符数量。

plaintext

char(2)

它可以存放两个英文字母，两个符号，也可以是两个中文。但实际上两个中文占用的字节数并非 2。

比如下方 c1 和 c2 分别是 char(3)/varchar(3)，可以插入 3 个字的中文

plaintext

MariaDB [hello]> select * from test;
+---------+-----------+
| c1      | c2        |
+---------+-----------+
| 1ca     | 23a       |
| 123     | 234       |
| 3       | 23        |
| 你好     | 我在呢    |
+---------+-----------+
4 rows in set (0.001 sec)

超过 2 个字符的数据会被拒绝插入；

plaintext

1 2	MariaDB [hello]> insert into test values ('1ca','23a3'); ERROR 1406 (22001): Data too long for column 'c2' at row 1

2.2 char 和 varchar 的区别

表中提到，这两个除了最大长度的限制，还有一个区别是定长/变长

简单理解为，char 是 C 语言中的 char* 类型的字符串，如果定义为 CHAR(3)，就无论如何都会开 3 个字符的空间来存放数据，不管你的字符串是 1 个字符还是 2 个字符；

而 varchar 是 C++ 的 string，它可以动态开辟长度，如果 VARCHAR(20)，当字符串只有 3 个的时候，只会占用 3 字节的空间。既然是 string，那就需要有额外空间来存放字符串的长度。在 mysql 中，varchar 会采用 1-3 字节的空间来存放 VARCHAR 数据类型的当前长度。

当我需要一个长度固定的 8 位 uuid，可以选择 char 来节省空间（不需要额外空间存放长度）
当我需要存放名字、地址等信息的时候，选择 varchar（但要保证长度不超过 varchar 的上限）

3. 时间类型

数据类型	说明
DATE	yyyy-mm-dd
DATETIME	yyyy-mm-dd hh:mm:ss
TIMESTAMP	时间戳

这个应该很好看明白，时间类型可以很方便的表示一些时间

数据更新时间
数据插入的时间
当前数据关联的操作时间
…

3.1 示例

在我的视频点播项目中，就是用了如下语句设置了一个 TIMESTAMP 字段，做为视频的上传时间

sql

1	insert_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP comment '视频创建时间',

但我发现，即便是用了 TIMESTAMP，查询的时候依旧显示的是可读时间

需要使用如下的语句进行查询，才会显示时间戳的数字

sql

1	SELECT UNIX_TIMESTAMP(insert_time) AS timestamp FROM tb_video;

效果如下

plaintext

MariaDB [vod_system]> SELECT UNIX_TIMESTAMP(insert_time) AS timestamp FROM tb_video;
+------------+
| timestamp  |
+------------+
| 1683213543 |
| 1683256245 |
+------------+
2 rows in set (0.001 sec)

一般情况下，保存时间戳是更好的选择，因为不管你是在那个时区，时间戳都是统一的。我们可以在应用层将时间戳通过函数（一般都有专门的库函数来转化）成对应时区的可读时间。

但如果你能特别确定你的应用只会在当前时区使用，比如我写了一个数据纯本地化的应用，在哪里部署就使用那个地方的时间。这时候就可以选择使用 date 类型来存放 str 字符串，省去了在应用层重新对时间操作的繁琐。

而且，像 python 这样的语言，也是支持将特定格式的 str 时间转回时间戳的。

3.2 timestamp 和 datetime 的区别

在 MySQL 中，TIMESTAMP 类型和 DATETIME 类型都用于存储日期和时间信息，但它们之间有几个重要的区别：

存储范围：
- TIMESTAMP 类型：使用 4 个字节进行存储，可以表示的范围为 1970-01-01 00:00:01 到 2038-01-19 03:14:07。因为使用较少的字节，所以 TIMESTAMP 类型在存储上更加节省空间。
- DATETIME 类型：使用 8 个字节进行存储，可以表示的范围为 1000-01-01 00:00:00 到 9999-12-31 23:59:59。由于使用更多的字节，DATETIME 类型可以表示的时间范围更广。
自动更新功能：
- MySQL8.0 中，TIMESTAMP 和 DATETIME 类型都可以设置为自动更新（DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP），这个列在每次更新数据时会自动将其设置为当前时间。
- 旧版本中只有 TIMESTAMP 才能实现自动更新。
时区处理：
- TIMESTAMP 类型：存储的值是相对于时区的，即它会根据数据库会话的时区进行自动调整。当从数据库中检索 TIMESTAMP 类型的值时，MySQL 会将其转换为客户端连接的时区。因此，TIMESTAMP 类型适用于存储基于时区的日期时间信息。
- DATETIME 类型：不受时区影响，它表示一个特定的日期和时间，不会随时区变化而变化。当从数据库中检索 DATETIME 类型的值时，它会保持存储的原始日期时间值。

通常情况下，如果你需要存储未来较远的时间或者需要进行跨时区的时间操作，你可能更倾向于选择 DATETIME 类型。而如果你对存储空间敏感或只需要存储近期时间范围内的数据，并且希望利用 ON UPDATE CURRENT_TIMESTAMP 功能，那么 TIMESTAMP 类型可能更合适。选择使用哪种类型取决于你的具体需求和应用场景。

慕雪个人想法：如果为了数据的长远考虑，使用 bigint 来直接存储毫秒级时间戳数字是更好的选择，这样我们能保证对于时间的处理可以在用户层面进行，而不用关注数据库里面的 date 类型到底是怎么处理的。

4.String 类型

数据类型	说明
ENUM	ENUM 是一个字符串对象，其值来自表创建时在列中规定的枚举变量其中之一的值
SET	SET 可以有 0 个或多个值，其值来之表创建时规定允许的一列值。指定包括多个 set 成员的 set 列值时，各成员之间需要用逗号风格。set 成员值本身不能有逗号

需要注意的是，虽然插入的是我们预先设定的枚举值，但后台存放的时候，依旧是以 数字 的方式存放的（节省空间）

4.1 enum

枚举类型和我们在 C 语言中定义的类似，可以作为一种数据的选项

sql

1	weekday ENUM('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday') NOT NULL

比如我们可以定义一个 weekday 的枚举类型，来表示星期几

这样就能保证，这一列的数据，就只能是这个枚举类型之中的数据，而不会出现 mon、wed 这样的缩写星期

相当于对使用这个数据库所有开发者的约束，必须要遵守枚举内部的数据要求。

4.1.1 通过数字插入

除了通过直接指定元素来插入，还可以通过数字来插入

我通过数字 3 来指定 weekday 里面的值，可以看到插入后的结果为 wednesday，即枚举中的第三个值

plaintext

MariaDB [hello]> insert into test  values ('游泳',3);
Query OK, 1 row affected (0.001 sec)

MariaDB [hello]> select * from test;
+------------------------------------------+-----------+
| hobby                                    | weekday   |
+------------------------------------------+-----------+
| 跑步,游泳                                | Monday    |
| 跑步                                     | Monday    |
| 羽毛球                                   | Monday    |
| 跑步,羽毛球                              | Monday    |
| 乒乓球                                   | Monday    |
|                                          | Monday    |
| 跑步,羽毛球,乒乓球,游泳,跳绳             | Monday    |
|                                          | Monday    |
| 游泳                                     | Wednesday |
+------------------------------------------+-----------+
9 rows in set (0.000 sec)

再试试数字 6，插入的是周六，也是第六个值

plaintext

MariaDB [hello]> insert into test  values ('游泳',6);
Query OK, 1 row affected (0.008 sec)

MariaDB [hello]> select * from test;
+------------------------------------------+-----------+
| hobby                                    | weekday   |
+------------------------------------------+-----------+
| 跑步,游泳                                | Monday    |
| 跑步                                     | Monday    |
| 羽毛球                                   | Monday    |
| 跑步,羽毛球                              | Monday    |
| 乒乓球                                   | Monday    |
|                                          | Monday    |
| 跑步,羽毛球,乒乓球,游泳,跳绳             | Monday    |
|                                          | Monday    |
| 游泳                                     | Wednesday |
| 游泳                                     | Saturday  |
+------------------------------------------+-----------+
10 rows in set (0.000 sec)

通过以上测试，我买可以知道，枚举值用数字指定的时候，使用的是实际的第 n 位，而并不是使用下标。比如我想插入 0 是会报错的，要想往 weekday 里面插入数据，正确的数字范围是 1-7

plaintext

1 2	MariaDB [hello]> insert into test values ('游泳',0); ERROR 1265 (01000): Data truncated for column 'weekday' at row 1

但是，使用数字会大大降低代码可读性，不符合我们使用枚举的初衷。还是更推荐大家直接指定枚举中的元素值！

4.2 set

set 和 enum 是类似的，但是可以多选

sql

1	hobby set('跑步','羽毛球','乒乓球','游泳','跳绳')

4.2.1 插入多个

当我们插入数据的时候，可以用逗号插入多个预定义set 中的值

sql

1	insert into test values ('跑步,游泳');

plaintext

MariaDB [hello]> select * from test;
+---------------+
| hobby         |
+---------------+
| 跑步,游泳     |
+---------------+
1 row in set (0.000 sec)

如果尝试插入 set 中不包含的值，则会报错

plaintext

1
2
3

MariaDB [hello]> insert into test values ('跑步,游泳,代码');
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1
MariaDB [hello]>

这个 set 就可以用于收集表来统计用户固定的爱好的情况，让用户来多选，再插入数据库。

4.2.2 元素不能有，

因为 set 可以插入多个，这就要求 set 中每一个元素本身不能包含 , （不能和 mysql 的关键字冲突，就好比 c 语言中你不能将关键字定义为成员变量一样。）

比如，如下的设置就是不允许的

sql

1	hobby set('跑步','羽毛球','乒乓球','游泳','跳,绳')

这张表会被拒绝创建，报错也告诉你 跳,绳 是不合法的 set 值

plaintext

MariaDB [hello]> create table test (
    -> hobby set('跑步','羽毛球','乒乓球','游泳','跳,绳'));
ERROR 1367 (22007): Illegal set '跳,绳' value found during parsing
MariaDB [hello]>

4.2.3 用数字插入？

除了用 set 中的数据，我们还可以用数字来插入这些元素

plaintext

MariaDB [hello]>  insert into test values (1);
Query OK, 1 row affected (0.008 sec)

MariaDB [hello]> select * from test;
+---------------+
| hobby         |
+---------------+
| 跑步,游泳     |
| 跑步          |
+---------------+
2 rows in set (0.001 sec)

多尝试几个，会发现结果很奇怪，并不是当时定义表中，set 元素的下标

plaintext

MariaDB [hello]> insert into test values (2);
Query OK, 1 row affected (0.009 sec)

MariaDB [hello]> insert into test values (3);
Query OK, 1 row affected (0.008 sec)

MariaDB [hello]> insert into test values (4);
Query OK, 1 row affected (0.003 sec)

MariaDB [hello]> select * from test;
+------------------+
| hobby            |
+------------------+
| 跑步,游泳        |
| 跑步             |
| 羽毛球           |
| 跑步,羽毛球      |
| 乒乓球           |
+------------------+
5 rows in set (0.001 sec)

比如你打算插入 0 的时候，会发现是一个空（但不是 NULL）

plaintext

MariaDB [hello]> insert into test values (0);
Query OK, 1 row affected (0.041 sec)

MariaDB [hello]> select * from test;
+------------------+
| hobby            |
+------------------+
| 跑步,游泳        |
| 跑步             |
| 羽毛球           |
| 跑步,羽毛球      |
| 乒乓球           |
|                  |
+------------------+
6 rows in set (0.001 sec)

4.2.3 位图

实际上，这里是用位图来表示每一个数据的位置的

sql

1	hobby set('跑步','羽毛球','乒乓球','游泳','跳绳')

这里一共有 5 个数据，对应就是 5 个字节。需要注意，其将 set 中的第一个元素放置在了二进制的低位。比如 跑步 对应的是二进制数码最右侧的（低位）的第一个二进制位

bash

0 0 0 0 0 # 啥都不插入
0 0 0 0 1 # 插入跑步
0 0 0 1 0 # 插入羽毛球
0 0 0 1 1 # 插入跑步,羽毛球
0 0 1 0 1 # 插入跑步,乒乓球
## 以此类推

但当我们尝试插入一个超过 5 个比特能表示的数据（0~31）时，会发生什么呢？

plaintext

MariaDB [hello]> insert into test values (1000);
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1
MariaDB [hello]> insert into test values (32);
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1

不给插入辣！

而插入 31 是可以的，也就是 5 个爱好都带上

plaintext

MariaDB [hello]> insert into test values (31);
Query OK, 1 row affected (0.042 sec)

MariaDB [hello]> select * from test;
+------------------------------------------+
| hobby                                    |
+------------------------------------------+
| 跑步,游泳                                |
| 跑步                                     |
| 羽毛球                                   |
| 跑步,羽毛球                              |
| 乒乓球                                   |
|                                          |
| 跑步,羽毛球,乒乓球,游泳,跳绳             |
+------------------------------------------+
7 rows in set (0.001 sec)

这样我们也可以理解为什么插入 0 是一个空值了。这便代表用户在提供的选项中谁都没有选择（相当于弃权）

4.2.4 查询

既然能用数字插入，就能用数字查询

plaintext

MariaDB [hello]> select * from test where hobby=31;
+------------------------------------------+
| hobby                                    |
+------------------------------------------+
| 跑步,羽毛球,乒乓球,游泳,跳绳             |
+------------------------------------------+
1 row in set (0.001 sec)

4.2.5 条件查询 find_in_set

sql

1	select find_in_set('a','a,b,c');

这个函数能帮我们查看一个数据是否在定义的 set 中，上面这个语句的意思是查询 a 是否在 set a,b,c 中。控制台中测试，结果输出为 1（代表 True）

如果不在，返回的是 0（代表 False）

plaintext

MariaDB [hello]> select find_in_set('a','a,b,c');
+--------------------------+
| find_in_set('a','a,b,c') |
+--------------------------+
|                        1 |
+--------------------------+
1 row in set (0.000 sec)

而在查询的时候，就可以用这个来筛选出爱好有其中一项的人

plaintext

MariaDB [hello]> select * from test where find_in_set('游泳',hobby);
+------------------------------------------+
| hobby                                    |
+------------------------------------------+
| 跑步,游泳                                |
| 跑步,羽毛球,乒乓球,游泳,跳绳             |
+------------------------------------------+
2 rows in set (0.001 sec)

这样就成功把爱好中有游泳的人给筛选出来了！

而且我们还可以用 or (and) 来连接两个条件，实现更大范围的查询。

plaintext

MariaDB [hello]> select * from test where find_in_set('游泳',hobby) or find_in_set('羽毛球',hobby);
+------------------------------------------+
| hobby                                    |
+------------------------------------------+
| 跑步,游泳                                |
| 羽毛球                                   |
| 跑步,羽毛球                              |
| 跑步,羽毛球,乒乓球,游泳,跳绳             |
+------------------------------------------+
4 rows in set (0.001 sec)

The end

Over！

1. 数值类型

1.1 插入超范围数据

1.2 约束

1.3 BIT 类型

1.4 浮点数

1.5 decimal

1.6 浮点数取整的几种方式

2. 文本、二进制类型

2.1 char

2.2 char 和 varchar 的区别

3. 时间类型

3.1 示例

3.2 timestamp 和 datetime 的区别

4.String 类型

4.1 enum

4.1.1 通过数字插入

4.2 set

4.2.1 插入多个

4.2.2 元素不能有，

4.2.3 用数字插入？

4.2.3 位图

4.2.4 查询

4.2.5 条件查询 find_in_set

The end

微信扫一扫：分享