【MySQL】数据类型

前言

在前一篇文章中，我们介绍了数据库的基本操作，而在插入表时涉及了许多关于表的数据类型，接下来就一起来学习一下MySQL常见的一些文件类型吧。

整形类型

数据类型	字节	最小值	最大值
TINYINT	1	-128	127
UNSIGNED TINYINT	1	0	255
SMALLINT	2	-32768	32767
UNSIGNED SMALLINT	2	0	65535
MEDIUMINT	3	-8388608	8388607
UNSIGNED MEDIUMINT	3	0	16777215
INT	4	-2147483648	2147483647
UNSIGNED INT	4	0	4294967295

可以看到，有非常多的整型可以选择，根据其占有的字节数，可以表示更大的数值。在int之上还有更大的 bigint 占 8 个字节。需要我们通过具体的情况，对数据类型进行选择，进而达到节约内存消耗的作用。

数值越界测试

接下来，我们针对数据类型进行一些插入的测试，这里以 tinyint 为例(极值较小，比较好操作)。

mysql> create table t1(
    -> id tinyint
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> desc t1;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | tinyint(4) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)

如上，我们创建了一个表并设定了一个 tinyint 类型的列，接下来我们尝试往表中插入数据。

mysql> insert into t1 values(100);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values(-100);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(127);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(128);
ERROR 1264 (22003): Out of range value for column 'id' at row 1

mysql> select * from t1;
+------+
| id   |
+------+
|  100 |
| -100 |
|  127 |
+------+
3 rows in set (0.00 sec)

可以看到，最后一个数据插入前，我们都成功地将数据插入了表格，通过上面那个数据类型的范围表中，我们可以知道，tinyint 的最大值只有127，若我们插入128便超过了它能够承受的范围便无法插入。

若我们将列属性转换成无符号，便能够插入128这个数字。

mysql> create table t2 (
    -> id tinyint unsigned 
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t2 values(128);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t2;
+------+
| id   |
+------+
|  128 |
+------+
1 row in set (0.00 sec)

总结

每个数据类型都有其对应的数据范围，无法插入超出数据范围的数。
对于MySQL来说一般会直接拦截我们的非法操作，换言之，若我们成功插入了数据，那么这个操作一定是合法的。
一般而言，数据类型本身也是MySQL的约束，为的是倒逼程序员进行正确的插入。
尽量不适用unsigned，放在使用场景中，一般原类型可能存放不下的数据，再加上无符号大概率也是存放不下的，因此不如在一开始就使用更大的数据类型进行存储。

Bit类型

Bit类型的本质就是一种位图结构，其位数为1~64(默认为1)。

bit(位数)

于是我们创建一个表，其中列的数据类型分别为 int 和 8 位的bit。

mysql> create table t1 (
    -> id int,
    -> b bit(8)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| b     | bit(8)  | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

现在我们开始往其中插入数据。

mysql> insert into t1 value(1,10);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 value(2,66);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 value(3,67);
Query OK, 1 row affected (0.00 sec)

可以看到，虽然插入的是 66 和 67，但实际上打印出来的却是 B 和 C，我们便可以推测，打印 bit 类型的数据时默认情况下是使用 ASCII 码的匹配模式进行打印的。

mysql> select * from t1;
+------+------+
| id   | b    |
+------+------+
|    1 | 
    |
|    2 | B    |
|    3 | C    |
+------+------+
3 rows in set (0.01 sec)

若我们想要将 bit 类型的数据以十六进制的形式进行打印，只需在select时指明要转换的进制即可。

mysql> select id,hex(b) from t1;
+------+--------+
| id   | hex(b) |
+------+--------+
|    1 | A      |
|    2 | 42     |
|    3 | 43     |
+------+--------+
3 rows in set (0.00 sec)

若插入的数据超过了该列能承受的范围，MySQL同样会阻止这个行为。

mysql> insert into t1 value(6,256);
ERROR 1406 (22001): Data too long for column 'b' at row 1

浮点数类型

有了整型自然也会有浮点型的数据类型，其中当属这两个数据类型最为常用。

float

定义浮点数类型时需要进行如下操作，其中 m 表示整个浮点数的显示长度，而 d 表示小数的位数。且这个数据将占用 4 个字节。

float(m,d)

例如，现在我们有一个表，其中float后面跟着的是4和2，不难得知，这个浮点数的数据范围为 -99.99~99.99。

mysql> desc t3;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | int(11)    | YES  |     | NULL    |       |
| score | float(4,2) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

同样，接下来尝试插入几个数据。

mysql> insert into t3 value(1,1.1);
Query OK, 1 row affected (0.04 sec)

mysql> insert into t3 value(2,1.115);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t3 value(3,1.112);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t3 value(4,99.99);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t3 value(5,99.999);
ERROR 1264 (22003): Out of range value for column 'score' at row 1

在我们平时的使用中，大概率插入的都是第一个数据这种数值，而当小数位数不足时，则会默认补0。

若插入的数据的小数位数超过了限定的长度，则会进行四舍(数据3)五入(数据2)。

例如数据四，此时刚好就是当前能存储的最大数值，此时若再进行进位的话便会出现错误(数据5)。

mysql> select * from t3;
+------+-------+
| id   | score |
+------+-------+
|    1 |  1.10 |
|    2 |  1.12 |
|    3 |  1.11 |
|    4 | 99.99 |
+------+-------+
4 rows in set (0.00 sec)

若是定义成 unsigned 类型，便仅仅失去负数部分的数据。

decimal

decimal(m,d)  	m最大为65默认为10，d最大值为30默认为0

decimal使用起来跟float很像但又有些区别。

我们将float和decimal放在同一个表中，插入相同的数据。

mysql> insert into t4 values(12.24141251511,12.24141251511);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t4;
+---------+-------------+
| s1      | s2          |
+---------+-------------+
| 12.2414 | 12.24141252 |
+---------+-------------+
1 row in set (0.00 sec)

可以看到，虽然插入了同样的数据，但decimal对于精度的保存明显强于float。

因此对于小数的精度有要求时，便推荐使用decimal。

字符串类型

之前我们就已经使用过了字符串类型，而其又分成char和varchar两种字符串类型，分别是定长字符串和可变长度字符串。

在MySQL中，字符串既可以用 ’ ’ 圈定，同时也可以使用 " "。

char

char(L)   L表示能存储的字符数，最大长度可以达到255

这里的字符指的就是，表选择的字符集对应的字符，就是实实在在的符号。

若设定的大小超过了最大长度，系统则会提示我们可以换成一个更大的类型进行存储。

mysql> create table t2(str char(256));
ERROR 1074 (42000): Column length too big for column 'str' (max = 255); use BLOB or TEXT instead

如下列 L 为 3 的 char，前几次无论我们插入的是字母还是汉字都能成功插入，而一旦长度超出了限制，便会报错。

mysql> insert into t1 value('a');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 value('aaa');
Query OK, 1 row affected (0.02 sec)

mysql> insert into t1 value('我我我');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 value('aaaa');
ERROR 1406 (22001): Data too long for column 'str' at row 1

之所以叫做定长字符串，正因为它就像数组一样，若插入的数据的大小比申请的空间小，那么这些多出来的空间就被浪费掉了。

varchar

而 varchar 则是可变长度字符串，并非当类型长度不够时会自动变长。

varchar(L)		varchar最大长度为65535个字节

而是在分配空间时只会分配足够使用的空间，L则是决定了可分配空间的上限。

具体细节就和char一样，需要注意插入字符的长度不能超过设定的L。

最大值的计算

在上面的介绍中只说了 varchar 允许的最大长度为 65535 个字节，那么 L 的最大值该怎么计算呢？

首先在空间的开头有 1~3 个字节用于记录数据的大小，极限情况下的最大可用字节为 65532 个。

接下来取决与此张表选择的字符集：

例如表使用的是 utf8，在 utf8 中一个字符的大小为 3 个字节，因此 L 最大值为 65532 / 3 = 21844 .

再大 1 的 21845 便无法容纳了，系统便建议我们换一个数据类型。

mysql> create table t1(
    -> str varchar(21844)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> create table t2( str varchar(21845) );
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

若使用 gbk 编码时，一个字符占 2 个字节，则 L 最大值为 65532 / 2 = 32766 .

如何选择char和varchar

若数据确定长度都一样就使用char (如: 身份证)。
若数据长度有变化就使用varchar (如: 姓名,地址)。
定长字符串占用的磁盘空间相对浪费，但访问的效率较高，而变长反之。

日期和时间类型

date

日期类型，只包含日期，格式为 ‘yyyy-mm-dd’ ,占用三个字节。

datetime

时间日期类型，从年一直到秒，格式为 'yyyy-mm-dd HH:ii:ss ’ ,占用八个字节。

timestamp

时间戳，从1970年开始，格式与 datetime 一致, 占用四个字节。

前两种类型的数据需要程序员自己插入，而时间戳在每次更新数据时便会自动更新。

mysql> desc t2;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| d1    | date      | YES  |     | NULL              |                             |
| d2    | datetime  | YES  |     | NULL              |                             |
| d3    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
3 rows in set (0.00 sec)

如上表，我们开始往表中插入数据。

值得注意的一点是，插入时需要按类型规定的格式插入，才能被准确识别。

mysql> insert into t2(d1,d2) values('2023-9-21','2023-9-21 21:13:50');
Query OK, 1 row affected (0.02 sec)

mysql> select * from t2;
+------------+---------------------+---------------------+
| d1         | d2                  | d3                  |
+------------+---------------------+---------------------+
| 2023-09-21 | 2023-09-21 21:13:50 | 2023-09-21 21:13:42 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)

enum和set类型

enum

相信枚举类型对于学习 C/C++ 的同学而言并不陌生，该类型中的值必须是先前设定好的，一旦试图插入其他数据就会报错。

enum (选项1,选项2,...)

mysql> create table t3(
    -> 科目 enum('语文','数学','英语')
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc t3;
+--------+----------------------------------+------+-----+---------+-------+
| Field  | Type                             | Null | Key | Default | Extra |
+--------+----------------------------------+------+-----+---------+-------+
| 科目   | enum('语文','数学','英语')       | YES  |     | NULL    |       |
+--------+----------------------------------+------+-----+---------+-------+
1 row in set (0.01 sec)

因此，我们只能在这三个选项中选择一个插入，若出现了系统不认识的选项，便会被拦截起来。

mysql> insert into t3 values('语文');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t3 values('数学');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t3 values('英语');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t3 values('体育');
ERROR 1265 (01000): Data truncated for column '科目' at row 1

mysql> select * from t3;
+--------+
| 科目   |
+--------+
| 语文   |
| 数学   |
| 英语   |
+--------+
3 rows in set (0.00 sec)

不仅如此，我们还可以根据声明的顺序使用下标(从1开始)选择选项进行插入。

mysql> insert into t3 values(1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t3 values(2);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t3 values(3);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t3;
+--------+
| 科目   |
+--------+
| 语文   |
| 数学   |
| 英语   |
| 语文   |
| 数学   |
| 英语   |
+--------+
6 rows in set (0.00 sec)

若插入时什么都不写则为 NULL，代表无，而插入 0 代表这里有东西，但是只是进行占位，没有实际意义。

set

set 表示的是一个集合，每次插入只能插入它的子集。

set(选项1，选项2...)

mysql> create table t4(
    -> 爱好 set('唱','跳','rap','篮球')
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> desc t4;
+--------+---------------------------------+------+-----+---------+-------+
| Field  | Type                            | Null | Key | Default | Extra |
+--------+---------------------------------+------+-----+---------+-------+
| 爱好   | set('唱','跳','rap','篮球')     | YES  |     | NULL    |       |
+--------+---------------------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

同样，若想选择不存在的选项则会被制止。

mysql> insert into t4 values('唱,跳,篮球');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t4 values('唱,rap,篮球');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t4 values('唱,篮球');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t4 values('sing,dance');
ERROR 1265 (01000): Data truncated for column '爱好' at row 1

mysql> select * from t4;
+----------------+
| 爱好           |
+----------------+
| 唱,跳,篮球     |
| 唱,rap,篮球    |
| 唱,篮球        |
+----------------+
3 rows in set (0.00 sec)

那么 set 也有像 enum 那样以数字的形式作进行插入吗？

当然有了，但是由于 set 是以子集的形式插入，自然不能直接使用下标作为依据。

而是需要先将数字转换成对应的位图，以位图的状态决定某选项选择与否。

例如 3 转换成二进制就是 0011，第一位和第二位为 1，因此下标为 1 和 2 的被选中，所以就选中了唱和跳。

mysql> insert into t4 values(3);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t4;
+---------+
| 爱好    |
+---------+
| 唱,跳   |
+---------+
1 row in set (0.00 sec)

enum和set类型的查找

对于 enum 来说，直接进行严格匹配即可完成查找。

select * from 表名 where 列名 = ...;

指定列名后加上要查找的选项即可完成查找。

mysql> select * from t3 where 科目 = '语文';
+--------+
| 科目   |
+--------+
| 语文   |
| 语文   |
+--------+
2 rows in set (0.00 sec)

若是通过这个方式对set类型进行查找便只会进行严格的匹配。

mysql> select * from t4 where 爱好='跳';
+--------+
| 爱好   |
+--------+
| 跳     |
| 跳     |
+--------+
2 rows in set (0.00 sec)

哪天我们想要找爱好有跳即可的人该怎么办？

我们便可使用 find_in_set(a,b) 这个函数。

该函数会在 b 中查找是否存在 a，并返回真假。

于是我们的查找操作便可以这样写。

mysql> select * from t4 where find_in_set('跳',爱好);
+-------------+
| 爱好        |
+-------------+
| 唱,跳       |
| 跳          |
| 跳,rap      |
| 唱,跳,rap   |
| 跳          |
+-------------+
5 rows in set (0.00 sec)

这下只要爱好有跳的人就都被筛选出来了。

好了，今天【MySQL】数据类型的相关内容到这里就结束了，如果这篇文章对你有用的话还请留下你的三连加关注。