MySQL数据类型

目录

一、数据类型分类

二、数值类型

1. 整数类型

1.1 TINYINT 类型

1.2 SMALLINT 类型

1.3 MEDIUMINT 类型

1.4 INT 类型

1.5 BIGINT 类型

2. 小数类型

2.1 FLOAT 类型

2.2 DOUBLE 类型

2.3 DECIMAL 类型

3. BIT 类型

3.1 BIT 类型的「显示规则」

3.2 BIT 类型的「范围限制」

三、字符串类型

1. CHAR 类型

2. VARCHAR 类型

3. CHAR vs VARCHAR 

四、日期和时间类型

1. DATE 类型

2. DATETIME 类型

3. TIMESTAMP 类型

五、String类型

1. ENUM类型：单选场景

2. SET类型：多选组合

3. 查询技巧

4. 核心差异

---

一、数据类型分类

分类	数据类型	说明
数值类型	BIT(M)	位类型。M 指定位数，默认值为 1，范围 1 - 64
	BOOL	布尔类型：使用 0 表示假，使用 1 表示真
	TINYINT [UNSIGNED]	占用 1 字节，默认为有符号。带符号范围 -128 ~ 127，无符号范围 0 ~ 255
	SMALLINT [UNSIGNED]	占用 2 字节，默认为有符号。带符号范围 -2¹⁵ ~ 2¹⁵⁻¹，无符号范围 0 ~ 2¹⁶⁻¹
	MEDIUMINT [UNSIGNED]	占用 3 字节，默认为有符号
	INT [UNSIGNED]	占用 4 字节，默认为有符号。带符号范围 -2³¹ ~ 2³¹⁻¹，无符号范围 0 ~ 2³²⁻¹
	BIGINT [UNSIGNED]	占用 8 字节，默认为有符号。带符号范围 -2⁶³ ~ 2⁶³⁻¹，无符号范围 0 ~ 2⁶⁴⁻¹
	FLOAT[(M,D)] [UNSIGNED]	M 指定显示长度，D 指定小数位数，占用 4 字节
	DOUBLE[(M,D)] [UNSIGNED]	M 指定显示长度，D 指定小数位数，占用 8 字节
	DECIMAL(M,D) [UNSIGNED]	M 指定显示长度，D 指定小数位数，每 4 个字节表示 9 个数字，小数点占用 1 字节
文本、二进制类型	CHAR(L)	固定长度字符串。L 指定字符串长度，最大为 255
	VARCHAR(L)	可变长度字符串。L 指定字符串长度上限，最多占用 65535 字节
	BLOB	用于存储二进制数据
	TEXT	用于存储大文本数据
时间日期	DATE	日期类型，格式为 YYYY-MM-DD
	DATETIME	日期时间类型，格式为 YYYY-MM-DD HH:MM:SS
	TIMESTAMP	时间戳，以 YYYY-MM-DD HH:MM:SS 格式进行显示
字符串类型	ENUM	枚举类型，值从预定义成员中选择单个值（如 ENUM('A','B','C')），存储空间由成员数量决定。
	SET	集合类型，值从预定义成员中选择多个值（如 SET('X','Y','Z')），成员间用逗号分隔，存储空间由成员数量决定。

---

二、数值类型

1. 整数类型

1.1 TINYINT 类型

TINYINT 是最小的整数类型，占用 1 个字节。默认是有符号的，范围是 -128 到 127；如果是无符号的，范围是 0 到 255。

数值越界测试：

🌴有符号案例：

-- 创建表
CREATE TABLE t1 (
    num TINYINT
);

-- 插入合法数据
INSERT INTO t1 VALUES (1);

-- 插入越界数据，报错
INSERT INTO t1 VALUES (128);

-- 查询数据
SELECT * FROM t1;

🌴无符号案例：

注意：尽量不使用unsigned，对于int类型可能存放不下的数据，int unsigned同样可能存放不 下，与其如此，还不如设计时，将int类型提升为bigint类型。 

1.2 SMALLINT 类型

SMALLINT 是较小的整数类型，占用 2 个字节。默认是有符号的，范围是 -32768 到 32767；如果是无符号的，范围是 0 到 65535。

数值越界测试：

🌴有符号案例：

-- 创建表
CREATE TABLE t3 (
    num SMALLINT
);

-- 插入合法数据
INSERT INTO t3 VALUES (32767);

-- 插入越界数据，报错
INSERT INTO t3 VALUES (32768);


-- 查询数据
SELECT * FROM t3;

🌴无符号案例： 

1.3 MEDIUMINT 类型

MEDIUMINT 是中等大小的整数类型，占用 3 个字节。默认是有符号的，范围是 -8388608 到 8388607；如果是无符号的，范围是 0 到 16777215。

数值越界测试：

🌴有符号案例：

-- 创建表
CREATE TABLE t5 (
    num MEDIUMINT
);

-- 插入合法数据
INSERT INTO t5 VALUES (8388607);

-- 插入越界数据，报错
INSERT INTO t5 VALUES (8388608);

-- 查询数据
SELECT * FROM t5 ;

🌴无符号案例：  

1.4 INT 类型

INT 是常用的整数类型，占用 4 个字节。默认是有符号的，范围是 -2^31 到 2^31-1；如果是无符号的，范围是 0 到 2^32-1。

数值越界测试：

🌴有符号案例：

-- 创建表
CREATE TABLE t7 (
    num INT
);

-- 插入合法数据
INSERT INTO t7 VALUES (2147483647);

-- 插入越界数据，报错
INSERT INTO t7 VALUES (2147483648);

-- 查询数据
SELECT * FROM t7 ;

🌴无符号案例：   

1.5 BIGINT 类型

BIGINT 是大整数类型，占用 8 个字节。默认是有符号的，范围是 -2^63 到 2^63-1；如果是无符号的，范围是 0 到 2^64-1。

数值越界测试：

🌴有符号案例：

-- 创建表
CREATE TABLE t9(
    num BIGINT
);

-- 插入合法数据
INSERT INTO t9 VALUES (9223372036854775807);

-- 插入越界数据，报错
INSERT INTO t9 VALUES (9223372036854775808);

-- 查询数据
SELECT * FROM t9;

🌴无符号案例：    

---

2. 小数类型

2.1 FLOAT 类型

1. 创建表与字段范围定义

-- 创建一个表，包含 id（整数）和 salary（浮点数）
CREATE TABLE t1 (
    id INT,
    salary FLOAT(4, 2)  -- 总位数 4，小数位 2（等价于 DOUBLE(4,2)）
);

范围说明：

• FLOAT(4,2) 表示总位数最多 4 位，其中小数占 2 位。
• 理论范围：整数部分最多 2 位，小数部分固定 2 位，即 -99.99 ~ 99.99。
• 实际存储范围：由于浮点数精度限制，实际允许插入的范围为 -99.994 ~ 99.994，超出此范围会报错。

---

2. 插入合法数据（范围内）

-- 插入边界值 -99.99（合法）
INSERT INTO t1 VALUES (100, -99.99);

-- 插入 99.99（合法）
INSERT INTO t1 VALUES (200, 99.99);

---

3. 测试四舍五入规则

-- 插入 -99.991（小数部分超 2 位，四舍五入为 -99.99）
INSERT INTO t1 VALUES (101, -99.991);

-- 插入 99.995（四舍五入为 100.00，但整数部分超 2 位，会报错）
INSERT INTO t1 VALUES (300, 99.995);

结果：

• 第三条语句成功插入，四舍五入后为 -99.99。
• 第四条语句报错：ERROR 1264 (22003): Out of range value for column 'salary'at row 1。

---

4. 查询数据验证

SELECT * FROM t1;

输出：

---

5. 边界越界测试

-- 尝试插入 100.00（整数部分超 2 位，报错）
INSERT INTO t1 VALUES (400, 100.00);

-- 尝试插入 -99.995（实际存储范围边界外，报错）
INSERT INTO t1 VALUES (500, -99.995);

报错原因：

• 100.00 超出整数部分最大允许值（99）。
• -99.995 超过实际存储范围下限（-99.994）。

---

2.2 DOUBLE 类型

DOUBLE 和 FLOAT规则类似：

-- 1. 创建测试表
CREATE TABLE t2(
    id INT,
    value DOUBLE(4, 2)
);

-- 2. 插入合法数据
INSERT INTO t2 VALUES (1, -99.99);    -- 成功
INSERT INTO t2 VALUES (2, 99.99);     -- 成功
INSERT INTO t2 VALUES (3, 50.55);     -- 成功

-- 3. 测试四舍五入
INSERT INTO t2 VALUES (4, 99.994);    -- 成功，存储为 99.99
INSERT INTO t2 VALUES (5, 99.995);    -- 报错：超出范围

-- 4. 插入越界数据
INSERT INTO t2 VALUES (6, 100.00);    -- 报错：整数部分超限
INSERT INTO t2 VALUES (7, -99.995);   -- 报错：超出实际范围

-- 5. 查询结果
SELECT * FROM t2 ;

2.3 DECIMAL 类型

1. 基本语法与定义

DECIMAL(M, D) [UNSIGNED]

参数说明：

• M：总位数（整数 + 小数），范围 1~65。
• D：小数位数，范围 0~30，且 D ≤ M。
• UNSIGNED：可选，表示仅允许非负数。

默认值：

• 若省略 M，默认 M=10。
• 若省略 D，默认 D=0（即纯整数）。

---

2. 取值范围示例

1. 有符号 DECIMAL

   -- 范围：-999.99 ~ 999.99
   -- 总位数 5，小数 2 位，整数 3 位。
   DECIMAL(5, 2)  

2. 无符号 DECIMAL

   -- 范围：0.00 ~ 999.99 
   -- 禁止负数，适合金额、数量等场景
   DECIMAL(5, 2) UNSIGNED  

---

3. DECIMAL vs FLOAT/DOUBLE：精度对比

1. 测试案例

2. 关键结论

FLOAT：

• 精度约为 7 位有效数字，超出部分会因二进制浮点数存储特性丢失精度。

DECIMAL：

• 以字符串形式存储十进制数值，完全保留指定位数的小数，适合高精度需求（如财务计算）。

---

3. BIT 类型

一句话解释：BIT 类型就像一个小盒子，专门用来存放二进制数字（0 和 1）。

特点：

• 可以指定盒子的「位数」，比如 BIT(8) 表示能存 8 位的二进制数（如 00000000 到 11111111）。
• 位数越多，能存的数值越大。例如 BIT(8) 最大能存 255（即二进制 11111111）。

3.1 BIT 类型的「显示规则」

1. 基础操作演示

（1）创建表

创建一个表，包含 id（数字）和 a（8位二进制数）

（2）插入数据并查询

插入一条数据：id=10，a=10

注意：早期版本中，BIT 类型尝试按 ASCII 字符显示（例如 10 对应换行符，显示为空），但8.0 版本改为直接显示十六进制值，避免歧义。

---

3.2 BIT 类型的「范围限制」

1. 节省空间的小技巧

如果某个字段只有两种值（比如性别），可以用 BIT(1)：

• 0 表示男，1 表示女，只占 1 位空间（实际存储时占用 1 字节）。

（1）创建性别表

（2）插入合法数据

（3）插入非法数据（会报错）

---

三、字符串类型

1. CHAR 类型

1. 核心特性

定义语法：CHAR(L)，其中 L 表示字符长度（范围 0~255）。

存储规则：

• 固定长度的字符串，无论实际内容长度如何，始终占用 L 个字符的存储空间。
• 支持存储任意字符（包括英文、数字、汉字、符号等），每个字符按编码规则计算字节。
• 字符与字节的区别：

• L 表示字符数，与编码无关。例如：CHAR(6) 可存储 6 个汉字或 6 个字母。

• 实际占用的字节数由字符集决定（如 UTF-8 每个汉字占 3 字节，GBK 占 2 字节）。

2. 操作示例

3. 适用场景

• 手机号、身份证号等固定长度数据。
• 高频查询字段（读写速度优于 VARCHAR）。

---

2. VARCHAR 类型

1. 核心特性

定义语法：VARCHAR(L)（L 范围 0~65535 字节，具体上限依赖编码）。

存储规则：

• 可变长度：仅存储实际数据长度，节省空间。
• 额外开销：占用 1-2 字节记录数据长度。

2. 编码对最大长度的影响

编码	每字符字节数	VARCHAR(L) 最大 L（字符）	计算方式
UTF8	3	21,844	65533 ÷ 3 ≈ 21844
GBK	2	32,766	65533 ÷ 2 ≈ 32766
UTF8MB4	4	16,383	65533 ÷ 4 ​≈ 16383

3. 操作示例

-- 创建 UTF8 编码表
CREATE TABLE test_varchar_utf8 (
    id INT,
    content VARCHAR(21844)  -- UTF8 下最大允许长度
) CHARSET=utf8;

-- 创建 GBK 编码表（报错示例）
CREATE TABLE test_varchar_gbk (
    id INT,
    content VARCHAR(32767)  -- 超出 GBK 最大长度
) CHARSET=gbk; -- ERROR 1074: Column length too big

-- 插入合法数据
INSERT INTO test_varchar_utf8 VALUES 
(1, 'Hello'),               -- 5 字母（成功）
(2, '高性能数据库');         -- 6 汉字（成功）

-- 插入超长数据（报错）
INSERT INTO test_varchar_utf8 VALUES (3, REPEAT('A', 21845)); -- ERROR 1406

4. 适用场景

• 用户昵称、地址等长度可变数据。
• 存储空间敏感的场景。

3. CHAR vs VARCHAR 

特性	CHAR(L)	VARCHAR(L)
存储方式	固定长度，始终分配 L 字符空间	可变长度，按需分配空间
最大字符数	255	依赖编码（UTF8: 21,844；GBK: 32,766）
空间效率	可能浪费空间（填充空格）	节省空间
读写性能	更高（直接访问定长空间）	稍低（需解析长度信息）
适用场景	固定长度数据（如身份证、手机号）	变长数据（如姓名、地址）

选型建议：

1. 优先用 CHAR

• 数据长度严格固定（如身份证号 CHAR(18)、MD5 值 CHAR(32)）。
• 高频读写字段（如状态码 CHAR(1)）。

2. 优先用 VARCHAR

• 数据长度变化较大（如用户评论、地址）。
• 存储空间敏感（如长文本字段）。

3. 通用原则

• 短字段（≤10 字符）可优先 CHAR（如性别 CHAR(1)）。
• 长文本（如文章内容）必须用 VARCHAR 或 TEXT。

四、日期和时间类型

1. DATE 类型

DATE 类型用于存储日期，格式为 'YYYY-MM-DD'。

2. DATETIME 类型

DATETIME 类型用于存储日期和时间，格式为 'YYYY-MM-DD HH:MM:SS'。

3. TIMESTAMP 类型

TIMESTAMP 类型用于存储时间戳，范围从 1970 年开始。

五、String类型

1. ENUM类型：单选场景

生活场景：咖啡店订单系统
假设某咖啡店需要记录顾客选择的杯型（只能选一种规格）

1. 定义与使用

CREATE TABLE 咖啡订单 (
    订单号 INT,
    顾客名 VARCHAR(20),
    杯型 ENUM('中杯','大杯','超大杯') -- 所有可能的选项
);

-- 正确录入（单选）
INSERT INTO 咖啡订单 VALUES 
(1001, '张三', '大杯'),
(1002, '李四', '超大杯');

-- 危险做法（数字映射）
INSERT INTO 咖啡订单 VALUES 
(1003, '王五', 2); -- 2对应"大杯"

2. 重要特性

存储原理：后台用数字1/2/3存储选项，但查询时仍显示文字

优势场景：饮品规格、衣服尺码(S/M/L)、开关状态(on/off)

避坑指南：

• 不要使用连续数字插入（如杯型=2），否则三个月后没人记得2代表什么
• 若需增加"迷你杯"，必须修改ENUM定义顺序：ENUM('迷你杯','中杯','大杯','超大杯')

---

2. SET类型：多选组合

生活场景：披萨定制系统
假设某披萨店需要记录顾客选择的配料（可多选）

1. 定义与使用

CREATE TABLE 披萨订单 (
    订单号 INT,
    顾客名 VARCHAR(20),
    配料 SET('芝士','培根','蘑菇','橄榄','辣椒') -- 允许的组合
);

-- 正确录入（多选组合）
INSERT INTO 披萨订单 VALUES 
(2001, '赵六', '芝士,蘑菇'),
(2002, '孙七', '培根,橄榄,辣椒');

-- 危险做法（数字计算）
INSERT INTO 披萨订单 VALUES 
(2003, '周八', 15); -- 1(芝士)+2(培根)+4(蘑菇)+8(橄榄)=15

2. 重要特性

存储原理：每个选项对应2的幂数（1,2,4,8...），多选时相加

优势场景：用户权限组合、多选兴趣标签、产品特征标记

避坑指南：

• 不要超过64个选项（SET的存储上限）
• 避免使用 配料='芝士'查询，这只会找到"仅选芝士"的订单

---

3. 查询技巧

案例1：查找所有点大杯咖啡的订单

-- 简单查询（ENUM直接匹配）
SELECT * FROM 咖啡订单 WHERE 杯型 = '大杯';

案例2：查找包含蘑菇的披萨订单

-- 必须使用特殊函数（SET多选查询）
SELECT * FROM 披萨订单 
WHERE FIND_IN_SET('蘑菇', 配料) > 0;

-- 进阶查询（同时包含蘑菇和培根）
SELECT * FROM 披萨订单 
WHERE FIND_IN_SET('蘑菇', 配料) > 0 
AND FIND_IN_SET('培根', 配料) > 0;

---

4. 核心差异

特性	ENUM	SET
选择方式	单选（如性别）	多选（如兴趣标签）
典型场景	咖啡杯型/订单状态	披萨配料/用户技能
存储编码	顺序编号（1,2,3...）	位掩码（1,2,4,8...）
最大选项数	65535	64
查询技巧	直接等于判断	必须用FIND_IN_SET()函数
数字陷阱	1=第一个选项	3=1+2（前两个选项的组合）