【MySQL】数据类型

一. 数值类型

1. tinyint 类型

# 连接mysql数据库
mysql -uroot -p

# 先创建数据库，再使用数据库
create database data;
use data;

有符号 tinyint 范围测试

create table t1(num tinyint);
insert into t1 values (-128); # 插入成功
insert into t1 values (127);  # 插入成功
insert into t1 values (128);  # 插入失败
select * from t1;

• 在 MySQL 中，整型可以指定是有符号的和无符号的，默认是有符号的。
• 由于 tinyint 类型占用1字节，因此有符号 tinyint 的取值范围为 -128 ~ 127，插入该范围内的数据时都能成功插入，如果插入的数据不在 -128 ~ 127范围内，那么插入数据时就会产生报错。
• 可以通过 unsigned 来说明某个字段是无符号的。

无符号 tinyint 范围测试

create table t2(num tinyint unsigned);
insert into t2 values (0);    # 插入成功
insert into t2 values (255);  # 插入成功
insert into t2 values (-1);   # 插入失败
select * from t2;

• 由于tinyint类型占用1字节，因此无符号tinyint的取值范围为 0 ~ 255，插入该范围的数据时都能成功插入，如果插入的数据不在 0 ~ 255范围内，那么插入数据时就会产生报错。
• 其他类型也是如此，对于不同的场景选择合适的数据类型。
• 如果我们向 MySQL 指定的类型插入不合法的数据，MySQL 一般会直接拦截我们，不让我们做对应的操作。反过来，如果我们已经有数据被成功插入到 MySQL 中，插入的数据一定是合法的。所以在 MySQL 中，数据类型本身也是一种约束，约束程序员进行正确的插入！
• 除非场景要求数值类型必须是无符号，否则尽量不要使用无符号，因为有符号的数值类型存不下的数据，其对应的无符号类型同样可能存不下，这时应该直接将数值类型进行提升。

2. bit 类型

• bit[(n)]：位字段类型，n表示每个值的位数，范围从1到64，如果n被忽略，默认为1

bit 类型的范围测试

创建一个表 t3，表当中包含用户值 id 和用户是否在线 online，其中 online 的类型可以指定为 1 位 bit 类型，因为性别只有两种取值，使用1个比特位来表示用户是否在线就可以节省空间，再插入一些数据，最后查看表中的数据，如下：

create table t3(
	id int,
	online bit(1)
);
insert into t3 values (111, 0); # 插入成功
insert into t3 values (222, 1); # 插入成功
insert into t3 values (333, 2); # 插入失败
select * from t3;
select id, hex(online) from t3;

• 如果插入 online 列的数据不是0或1，那么插入数据时就会产生报错。
• 默认查看到的是十六进制的数据，可以用 hex(online) 转换为十进制数据。

修改表中 online 列的属性为 bit(10)，再插入数据，最后查看表中的数据。

alter table t3 modify online bit(10);
desc t3;
insert into t3 values (111, 48);
select * from t3;
select id, hex(online) from t3;

3. 浮点数类型

1. float 类型

• float[(m, d)] [unsigned]：m数值的位数，d小数的位数，占用空间4个字节。

有符号 float 范围测试

create table t4(
	id int,
	salary float(4, 2)
);
insert into t4 (id, salary) values(1, -99.99); # 插入成功
insert into t4 (id, salary) values(1, 99.99);  # 插入成功
insert into t4 (id, salary) values(1, 88);     # 插入成功
insert into t4 (id, salary) values(1, 100.0);  # 插入失败
select * from t4;

insert into t4 (id, salary) values(1, 12.234); # 插入成功
insert into t4 (id, salary) values(1, 12.235); # 插入成功
insert into t4 (id, salary) values(1, 99.994); # 插入成功
insert into t4 (id, salary) values(1, 99.995); # 插入失败
select * from t4;

• float(4,2) 表示有符号的数，范围是 -99.99 ~ 99.99。
• float(4,2) unsigned 表示无符号的数，范围是 0 ~ 99.99。
• MySQL在保存值时会进行四舍五入。

create table t5(
	id int,
    salary float
);
insert into t5 (id, salary) values (1, 123456789.123456);
insert into t5 (id, salary) values (1, 123456789123.123);
select * from t5;

结论：float 存储是有精度损失的！

2. decimal类型

• decimal(m, d) [unsigned]：m数值的位数，d小数的位数。
• decimal(5,2) 表示的范围是 -999.99 ~ 999.99
• decimal(5,2) unsigned 表示的范围 0 ~ 999.99
• decimal 和 float 很像，但是有区别：float 和 decimal 表示的精度不一样。

create table t6(
    f1 float(10, 8),
    f2 decimal(10, 8)
);
insert into t6 (f1, f2) values (12.12345678, 12.12345678);
select * from t6;

结论：decimal的精度更准确，因此如果我们希望某个数据表示高精度，推荐使用decimal

二. 字符串类型

1. 定长 char 类型

• char(L)：固定长度字符串，L是可以存储的长度，单位为字符，最大长度值可以为255

char 类型测试

create table if not exists t7(
    id int,
    name char(2)
);
insert into t7 (id, name) values (1, 'a');       # 插入成功
insert into t7 (id, name) values (1, 'ab');      # 插入成功
insert into t7 (id, name) values (1, 'abc');     # 插入失败
insert into t7 (id, name) values (1, '你');      # 插入成功
insert into t7 (id, name) values (1, '你好');    # 插入成功
insert into t7 (id, name) values (1, '你好码吗');  # 插入失败
select * from t7;

• char(2) 表示可以存放两个字符，可以是字母或汉字，但是不能超过2个， 最多只能是255
• 在 gbk 编码1个汉字对应2个字节，在 utf-8 中1个汉字对应3个字节。
• MySQL 限定字符的概念不是字节，这样用户就不用关心复杂的编码细节了。

2. 变长 varchar 类型

• varchar(L)：可变长度字符串，L表示字符长度，最大长度65535个字节。

varchar 类型测试

create table t8( 
	id int, 
	name varchar(7) 
);
insert into t8 (id, name) values (1, '江山如此多娇');     # 插入成功
insert into t8 (id, name) values (1, '引无数英雄竞折腰');  # 插入失败
select * from t8;

关于 varchar(L) 中的 L 到底是多大，这与表的编码密切相关：

• varchar 的字节可以指定为 0~65535 之间的值，但是有1 - 3 个字节用于记录数据大小，所以说有效字节数是65532
• 当我们的表的编码是 utf-8 时，varchar(n) 的参数 L 最大值是 65532/3=21844 (因为 utf-8 中，一个字符占用3个字节)，如果编码是 gbk，varchar(n) 的参数 L 最大是 65532/2=32766 (因为 gbk 中，一个字符占用2字节)

create table t9(
	name varchar(21845)
)charset=utf8; # 建表失败

create table t10(
	name varchar(21844)
)charset=utf8; # 建表成功

3. char 与 varchar 的比较

如何选择定长或变长字符串？

• 如果数据长度都一样，就使用定长 char，比如：身份证，手机号。
• 如果数据长度有变化，就使用变长 varchar，比如：名字，地址。但要保证最长的能存的进去。
• 定长的磁盘空间比较浪费，但是效率高。
• 变长的磁盘空间比较节省，但是效率低。
• 定长的意义是，直接开辟好对应的空间。
• 变长的意义是，在不超过自定义范围的情况下，用多少，开辟多少。

4. 日期和时间类型

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

• date：日期 'yyyy-mm-dd'，占用3个字节。
• datetime：日期时间 'yyyy-mm-dd HH:ii:ss' 表示范围从 1000 到 9999，占用8个字节。
• timestamp：时间戳，从1970年开始的 yyyy-mm-dd HH:ii:ss 格式和 datetime 完全一致，占用4个字节。

create table t11(
    t1 date,
    t2 datetime,
    t3 timestamp
);
alter table t11 modify column t3 timestamp default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP;
desc t11;

# 添加数据时，时间戳更新为当前时间
insert into t11 (t1, t2) values ('2025-4-23', '1970-01-01 00:00:00'); 
select * from t11;

# 更新时间t1时，时间戳自动更新为当前时间
update t11 set t1='2025-4-24';
select * from t11;

• date：记录日期。
• datetime：记录日期 + 时间。
• timestamp：程序员不需要修改，当修改表中其它字段时，时间戳自动更新为当前时间。通常用于在社交平台中评论，你当前的时间会显示出来。

timestamp 类型使用案例

create table t12(
	comment text, 
	time timestamp 
);
alter table t12 modify column time timestamp default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP;
insert into t12 (comment) values ('你好!');
select * from t12;

update t12 set comment='你还好吗!';
select * from t12;

评论区：如果评论人修改了评论内容，那么就需要对评论表进行更新，更新表的同时评论的发布时间也会更新为修改表的时间。

5. enum 和 set

• enum(‘选项1’, ‘选项2’, ‘选项3’ …);
• set(‘选项1’, ‘选项2’, ‘选项3’ …);
• 在定义 enum 字段时需要提供若干个选项的值，在设置 enum 字段值时只允许选取其中的一个值。
• 在定义 set 字段时需要提供若干个选项的值，在设置 set 字段值时可以选取其中的一个或多个值。

调查表案例

create table votes(
    name varchar(20),
    gender enum('男', '女'),
    hobby set('敲代码', '打游戏', '游泳', '乒乓球', '羽毛球')
);
desc votes;

insert into votes values ('刘备', '未知', '敲代码'); # 插入失败
insert into votes values ('刘备', '男', '敲代码');   # 插入成功
insert into votes values ('刘备', '女', '敲代码');   # 插入成功
insert into votes values ('刘备', '0', '敲代码');    # 插入成功
insert into votes values ('刘备', NULL, '敲代码');   # 插入成功
insert into votes values ('刘备', '1', '敲代码');    # 插入成功
insert into votes values ('刘备', '2', '敲代码');    # 插入成功
insert into votes values ('刘备', '3', '敲代码');    # 插入失败
select * from votes;

• enum 提供了若干个选项，但是只能选择一个选项，但是不能插入不存在的选项。
• 出于效率考虑，这些值实际存储的是"数字"，每个选项值依次对应如下数字：1、2、3 … 65535。当我们添加枚举值时，也可以添加对应的数字编号。
• 插入 0 / NULL 也能成功插入且对应的是：有但是为空串 / 什么都没有。

insert into votes values ('关羽', '男', '敲代码');
insert into votes values ('关羽', '男', '打游戏');
insert into votes values ('关羽', '男', '敲代码,打游戏,游泳');
insert into votes (name) values ('张飞');
insert into votes values ('曹操', '男', 7);   
insert into votes values ('孙权', '男', 31);  
insert into votes values ('孙尚香', '女', 0);
insert into votes values ('孙策', '男', 1);
insert into votes values ('周瑜', '男', 2);
select * from votes;

• set 提供了若干个选项，可以选择多个选项。
• 而且出于效率考虑，这些值实际存储的是“数字”，且以位图的方式进行存储，每个选项值依次对应如下数字1、2、4、8、16、32 … 最多64个。当我们添加枚举值时，也可以添加对应的数字编号 (例如：7的二进制为00111，对应前三个选项。31的二进制为11111，对应所有选项)
• 不建议在添加枚举值，集合值的时候采用数字的方式，因为不利于阅读。

查找数据：

select * from votes where gender='男';

# 只能找出爱好是打游戏的，但是不能找出爱好有打游戏但还有其他爱好的人
select * from votes where hobby='打游戏'; 

• 集合查询使用find_ in_ set函数： find_in_set(sub, str_list)
• 如果 sub 在 str_list 中，则返回下标，如果不在，返回0，str_list 用逗号分隔的字符串。

select * from votes where find_in_set('打游戏', hobby);

# 但是这样无法找出两个爱好的
select * from votes where find_in_set('打游戏,游泳', hobby); 

# 必须这样写
select * from votes where find_in_set('打游戏', hobby) and find_in_set('游泳', hobby); 

select * from votes where hobby=31;