MySQL——数据类型

目录

一、数值类型

1. 整型

2. 小数类型

FLOAT与DOUBLE

DECIMAL

3. BIT类型

二、字符串类型

1. CHAR与VARCHAR

2. BLOB与TEXT

三、日期与时间类型

四、ENUM与SET类型

1. ENUM（单选）

2. SET（多选）

五、选择数据类型的建议

六、常见问题

---

一、数值类型

1. 整型

MySQL支持多种整型，根据存储范围和需求选择：

类型	字节	带符号范围	无符号范围
TINYINT	1	-128 ~ 127	0 ~ 255
SMALLINT	2	-32768 ~ 32767	0 ~ 65535
INT	4	-2147483648 ~ 2147483647	0 ~ 4294967295
BIGINT	8	-2^63 ~ 2^63-1	0 ~ 18446744073709551615

注意事项：

• 默认情况下，整型为有符号。若需无符号，使用UNSIGNED关键字。

• 不建议过度依赖无符号类型，超出范围时直接升级到更大类型（如BIGINT）更稳妥。

示例：

-- 越界插入会报错
CREATE TABLE t1(num TINYINT);
INSERT INTO t1 VALUES(128); -- ERROR 1264: Out of range value

 

tinyint类型是8个字节，超过这个范围mysql会自动检查，并不会插入到表中； 

说明:

在MySQL中，整型可以指定是有符号的和无符号的，默认是有符号的。

可以通过UNSIGNED来说明某个字段是无符号的

无符号案例

---

2. 小数类型

FLOAT与DOUBLE

• FLOAT：4字节，精度约7位，适用一般科学计算。

• DOUBLE：8字节，精度约15位，适合高精度需求。

FLOAT(M,D) -- M为总位数，D为小数位

 

小数：float(4,2)表示的范围是-99.99 ~ 99.99，MySQL在保存值时会进行四舍五入。

---

DECIMAL

定点数类型，精确存储小数，适合财务计算：

decimal整数最大位数m为65。支持小数最大位数d是30。如果d被省略，默认为0.如果m被省略，默认是10。
 

DECIMAL(M,D) -- M总位数，D小数位（默认M=10, D=0）

 

关于参数的最大比特位数； 

对比：

CREATE TABLE t2 (salary FLOAT(10,8), salary2 DECIMAL(10,8));
INSERT INTO t2 VALUES(23.12345612, 23.12345612);
-- 查询结果：FLOAT显示23.12345695（精度丢失），DECIMAL保持原值

 

---

3. BIT类型

存储位字段，常用于布尔值或状态标志：

BIT(M) -- M范围1~64，默认1

特性：

• 插入非0/1值会越界报错。

• 显示时按ASCII码转换（如65显示为'A'）。

示例：

CREATE TABLE t3 (status BIT(1));
INSERT INTO t3 VALUES(0), (1); -- 成功
INSERT INTO t3 VALUES(2); -- ERROR 1406: Data too long

 

我们发现给个1和0成功插入了，但是显示不出来；因为这里使用的 按ASCII码转换（如65显示为'A'）；

转为10进制显示就ok了； 

---

 

二、字符串类型

1. CHAR与VARCHAR

类型	特点	适用场景
CHAR	定长，速度快，可能浪费空间	固定长度数据（如MD5）
VARCHAR	变长，节省空间，效率略低	可变长度数据（如地址）

编码影响：

• UTF-8下，VARCHAR最大长度为21844字符（每个字符占3字节）。

• GBK下，最大为32766字符（每个字符占2字节）。

我们知道每个汉字占3个字节，那么在c语言中，6个字节就只能存2个汉字，但在mysql当中一个汉字就是一个char。

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

 

 

 说明：

关于varchar(len),len到底是多大，这个len值，和表的编码密切相关：

varchar长度可以指定为0到65535之间的值，但是有1 - 3 个字节用于记录数据大小，所以说有效字节数是65532。

当我们的表的编码是utf8时，varchar(n)的参数n最大值是65532/3=21844[因为utf中，一个字符占用3个字节]，如果编码是gbk，varchar(n)的参数n最大是65532/2=32766（因为gbk中，一个字符占用2字节）。

示例：

CREATE TABLE t4 (phone CHAR(11), address VARCHAR(100));

---

2. BLOB与TEXT

• BLOB：存储二进制数据（如图片）。

• TEXT：存储大文本，不支持默认值和全文索引（需MySQL 5.6+）。

---

三、日期与时间类型

类型	格式	字节	范围
DATE	YYYY-MM-DD	3	1000-01-01 ~ 9999-12-31
DATETIME	YYYY-MM-DD HH:MM:SS	8	同DATE
TIMESTAMP	时间戳（同DATETIME）	4	1970-01-01 ~ 2038-01-19

特性：

• TIMESTAMP自动更新为当前时间，适合记录数据修改时间。

示例：

CREATE TABLE logs (
    event_time DATETIME, 
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

 

---

四、ENUM与SET类型

1. ENUM（单选）

从预定义选项中选择一个值，内部存储为数字（1,2,3...）：

ENUM('男', '女') -- 插入时可用'男'或1

2. SET（多选）

支持多个选项，内部按位存储（1,2,4,8...）：

SET('登山', '游泳', '篮球')

查询技巧：
使用FIND_IN_SET函数匹配多选值：

SELECT * FROM votes WHERE FIND_IN_SET('登山', hobby) > 0;

---

五、选择数据类型的建议

1. 精确性优先：财务计算用DECIMAL，避免FLOAT精度丢失。

2. 空间优化：小范围数据用TINYINT而非INT。

3. 性能权衡：频繁查询字段用CHAR，变长数据用VARCHAR。

4. 时间记录：需要自动更新时间戳时选TIMESTAMP。

---

六、常见问题

1. 数值越界：插入前校验范围，或使用SIGNED/UNSIGNED约束。

2. 字符长度超限：根据编码计算VARCHAR最大长度。

3. 日期格式错误：严格遵循YYYY-MM-DD等标准格式。

---

合理选择数据类型是高效数据库设计的基石。通过理解各类型的特性及适用场景，开发者可以优化存储结构，提升系统性能。