【MySQL】数据类型

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

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中的数据范围必须严格遵守其定义的范围

1
2
3

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

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

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)

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

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 浮点数

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

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

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

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

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

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)

插入负数也是没问题的

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)

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

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

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

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就把数据给搞坏了。

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都是支持全文索引的！

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并不是字节，而是字符数量。

char(2)

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

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

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

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

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字段，做为视频的上传时间

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

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

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

1	SELECT UNIX_TIMESTAMP(insert_time) AS timestamp FROM tb_video;

效果如下

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语言中定义的类似，可以作为一种数据的选项

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

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

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

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

4.1.1 通过数字插入

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

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

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，插入的是周六，也是第六个值

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

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是类似的，但是可以多选

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

4.2.1 插入多个

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

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

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

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

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语言中你不能将关键字定义为成员变量一样。）

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

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

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

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

4.2.3 用数字插入？

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

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元素的下标

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）

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 位图

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

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

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

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）时，会发生什么呢？

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个爱好都带上

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 查询

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

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

4.2.5 条件查询 find_in_set

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

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

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

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)

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

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

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

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

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