MySQL的数据类型-EW帮帮网

MySQL的数据类型

数据类型分类

数值类型

bit类型

MySQL的数据类型

数据类型的作用：

决定了存储数据时应该开辟的空间大小。
决定了如何识别一个特定的二进制序列。
决定了数据的取值范围。

数据类型分类

说明一下：MySQL本身是不支持bool类型的，当把一个数据设置成bool类型时，数据库会自动将其转换成tinyint(1)的数据类型，其实这个就是变相的bool类型，因为tinyint(1)只有1和0两种取值，可以分别对应bool类型的true和false。

数值类型

tinyint类型

有符号tinyint范围测试

创建一个表，表当中包含一个tinyint类型的列，默认其为有符号类型。如下：

由于tinyint类型占用1字节，因此有符号tinyint的取值范围为-128~127，插入该范围内的数据时都能成功插入。如下：

如果插入的数据不在-128~127范围内，那么插入数据时就会产生报错。如下：

无符号tinyint范围测试

创建一个表，表当中包含一个tinyint类型的列，并指定其为无符号类型。如下：

由于tinyint类型占用1字节，因此无符号tinyint的取值范围为0~255，插入该范围的数据时都能成功插入。如下：

建议：

除非场景要求数值类型必须是无符号，否则尽量不要使用无符号，因为有符号的数值类型存不下的数据，其对应的无符号类型同样可能存不下，这时应该直接将数值类型进行提升。

bit类型

bit类型的显示方式

创建一个表，表当中包含一个int类型的id列和一个8位bit类型的a列。如下：

向表中插入一条记录，记录中指定id和a的值均为10，插入记录后查看表会发现a的值显示的十六进制的10（mysql的版本不同可能bit类型的呈现方式不一样）。如下：

bit类型的范围测试

创建一个表，表当中包含用户名name和用户性别gender，其中gender的类型可以指定为1位bit类型，因为性别只有男和女两种取值，使用1个比特位来表示用户的性别就可以节省空间。如下：

如果规定gender列插入0表示男，插入1表示女，那么在插入用户信息时就可以通过插入0和1来指定用户的性别。如下：

如果插入gender列的数据不是0或1，那么插入数据时就会产生报错。如下：

建议：

虽然MySQL提供了位类型bit，但一般不建议将数据类型设置成位类型，除非将来这个数据本身就只是给程序看的，并且数据本身非常占用资源。
因为查询位类型数据时，默认会按照十六进制对应的值进行显示，这对于将来数据库管理员维护数据库或程序员调试程序都是不太方便的。

float类型

有符号float范围测试

创建一个表，表当中包含一个float(4,2)类型的列，默认其为有符号类型。如下：

由于float(4,2)的取值范围为-99.99~99.99，因此插入该范围内的数据都能成功插入。如下：

此外，由于MySQL在保存值时会进行四舍五入，因此实际可插入float(4,2)的范围为-99.994~99.994，如果插入的数据不在该范围内，那么插入数据时就会产生报错。如下：

有符号float范围测试

创建一个表，表当中包含一个float(4,2)类型的列，并指定其为无符号类型。如下：

无符号float类型的取值范围，实际就是把对应有符号float类型中的负数部分拿走了，因此float(4,2)的取值范围为0~99.99，实际可插入的范围是0~99.994。如下：

如果插入的数据不在0~99.994范围内，那么插入数据时就会产生报错。如下：

decimal类型

decimal的精度更高

decimal和float类型的使用方式一样，但decimal的精度比float更高。

创建一个表，表当中分别包含一个float(10,8)的列和一个decimal(10,8)的列。如下：

向表当中插入一条记录，指定float和decimal的值均为23.12345612，但最终查表时会发现decimal保持了数据的原貌，而float则会存在一定的精度损失。如下：

字符串类型

char类型

char类型测试

创建一个表，表当中包含一个char(6)的content列。如下：

由于char(6)中最多可存储6个字符，因此只要插入的字符个数不超过6个都是能够成功插入的。如下：

如果插入的字符个数超过了6个，那么在插入数据时就会产生报错。如下：

需要注意的是，这里所说的字符并不只是指一个英文字母，一个汉字也是一个字符，因此只要插入的汉字个数不超过6个也是可以插入的。如下：

好处：

在不同编码中，一个字符所占的字节个数是不同的，比如utf8中一个字符占3个字节，utf8mb4中一个字符站4个字节，gbk中一个字符占2个字节。MySQL限定字符的概念不是字节，这样用户就不用关心复杂的编码细节了。

varchar类型

varchar类型测试

创建一个表，表当中包含一个varchar(6)的content列。如下：

由于varchar(6)中最多可存储6个字符，因此只要插入的字符个数不超过6都是能够成功插入的。如下：

如果插入的字符个数超过了6个，那么在插入数据时就会产生报错。如下：

varchar类型可指定的字符个数上限

varchar类型最多占用65535字节，其中有1~2字节用来表示实际数据长度，还有1字节来存储其他控制信息，因此varchar类型的有效字节数最多是65532字节。

而varchar类型可指定的字符个数上限，与表的编码格式有关：

对于utf8编码来说，一个字符占用三个字节，因此varchar(L)中的L最大可指定为 65532 ÷ 3 = 21844。
对于utf8mb4来说，一个字节占用四个字节，因此varchar(L)中的L最大可指定为 65532 ÷ 4 = 16383
对于gbk编码来说，一个字符占用两个字节，因此varchar(L)中的L最大可指定为 65532 ÷ 2 = 32766。

因此在定义编码格式为utf8mb4的表时，varchar(L)中的L如果超过了16383，则会产生报错。如下：

而在定义编码格式为gbk的表时，varchar(L)中的L如果超过了32766，则会产生报错。如下：

char和varchar比较

char和varchar比较

char和varchar的区别如下：

char类型可存储字符上限为255，varchar类型可存储字符上限与表的编码格式有关。
char(L)定义后，无论存储的字符串长度是否到达L，都会开辟用于存储L个字符的定长空间，如果存储的字符串长度超过L则会报错。
varchar(L)定义后，会根据存储字符串的长度按需开辟空间，并且需要使用1-3字节的空间用于表示存储字符串的长度以及其他控制信息，如果存储的字符串长度超过L则会报错。

如何选取char和varchar类型？

char和varchar的优缺点如下：

char类型的数据是定长的，因此磁盘空间比较浪费，但是效率高（直接访问定长的空间）。
varchar类型的数据是变长的，因此磁盘空间比较节省，但是效率低（需要先读取存储字符串的长度，再访问指定长度的空间）。

如果要存储的数据是定长的，那就使用char类型进行存储，比如身份证号码、手机号、md5等。如果要存储的数据是变长的，那就使用varchar类型进行存储，比如名字、地址等。

时间日期类型

三种时间日期类型

常用的三种时间日期类型如下：

date：日期格式为YYYY-MM-DD，占用三字节。
datetime：时间日期格式为YYYY-MM-DD HH:MM:SS，占用八字节。
timestamp：时间戳，格式为YYYY-MM-DD HH:MM:SS，占用四字节。

创建一个表，表当中包含date、datetime和timestamp三种时间日期类型的列。如下：

查看表结构可以看到，这三种日期类型都是为空的

注意：timestamp类型的t3列在老版本中是不为空的，它的默认值为CURRENT_TIMESTAMP

因此如果插入数据之后如下：

如果想要随时更新时间，可以使用以下语句

ALTER TABLE your_table
MODIFY COLUMN your_timestamp_field TIMESTAMP 
NOT NULL 
DEFAULT CURRENT_TIMESTAMP 
ON UPDATE CURRENT_TIMESTAMP;

各部分说明

ALTER TABLE your_table：指定要操作的表，将 your_table 替换为实际的表名（比如你场景里的 comment_tb ）。
MODIFY COLUMN your_timestamp_field TIMESTAMP：表示要修改名为 your_timestamp_field 的字段，且该字段类型为 TIMESTAMP ，把 your_timestamp_field 换成实际的字段名（比如你提到的 t3 或其他 TIMESTAMP 字段名）。
NOT NULL：设置该字段不允许为空，从你提供的 t3 字段定义里 NO（对应 Null 列）能看出是不允许为空的，所以加上这个约束。
DEFAULT CURRENT_TIMESTAMP：设置默认值为当前时间，这样插入数据时，若没显式给该字段赋值，就会自动填入当前时间。
ON UPDATE CURRENT_TIMESTAMP：配置更新规则，当该行数据有其他字段被更新时，这个 TIMESTAMP 字段会自动更新为当前时间。