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 ~ 2551
BOOL用0和1表示真假(实际为tinyint(1))1
SMALLINT [UNSIGNED]有符号-2^15 ~ 2^15 -1; 无符号2^16 -12
MEDIUMINT [UNSIGNED]有符号-2^23 ~2^23 -1; 无符号 2^24 -13
INT [UNSIGNED]有符号-2^31 ~ 2^31 -1; 无符号2^32 -14
BIGINT [UNSIGNED]有符号-2^63 ~ 2^63 -1; 无符号2^64 -18
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类型的表,向里面插入如下的正确数据

1
2
3
4
5
6
7
8
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)

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

1
2
3
4
5
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表中插入一个浮点数,来测试一下情况

1
2
3
4
5
6
7
8
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

1
2
3
4
5
6
7
8
9
10
11
12
13
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)

插入负数也是没问题的

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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)

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

1
2
3
4
5
6
7
8
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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就把数据给搞坏了。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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都是支持全文索引的!

1
2
3
4
5
6
7
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并不是字节,而是字符数量。

1
char(2)

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

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

1
2
3
4
5
6
7
8
9
10
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.时间类型

数据类型说明
DATEyyyy-mm-dd
DATETIMEyyyy-mm-dd hh:mm:ss
TIMESTAMP时间戳

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

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

3.1 示例

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

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

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

image-20230506151100813

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

1
SELECT UNIX_TIMESTAMP(insert_time) AS timestamp FROM tb_video;

效果如下

1
2
3
4
5
6
7
8
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类型都用于存储日期和时间信息,但它们之间有几个重要的区别:

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

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

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

4.String类型

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

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

4.1 enum

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

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

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

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

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

4.1.1 通过数字插入

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

image-20230713113312618

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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,插入的是周六,也是第六个值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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 ('跑步,游泳');
1
2
3
4
5
6
7
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值

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

4.2.3 用数字插入?

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

1
2
3
4
5
6
7
8
9
10
11
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元素的下标

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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中的第一个元素放置在了二进制的低位。比如 跑步 对应的是二进制数码最右侧的(低位)的第一个二进制位

1
2
3
4
5
6
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)时,会发生什么呢?

1
2
3
4
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个爱好都带上

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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 查询

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

1
2
3
4
5
6
7
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)

1
2
3
4
5
6
7
8
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)

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

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

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

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

1
2
3
4
5
6
7
8
9
10
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!