【高效MySQL】MySQL数据类型

大家好，欢迎来到IT知识分享网。

一、数值类型

(一) 整数类型

类型 存储空间 有符号范围 无符号范围 典型场景 TINYINT 1 字节 -128 ~ 127 0 ~ 255 状态标志（0/1）、布尔值 SMALLINT 2 字节 -32,768 ~ 32,767 0 ~ 65,535 年龄、小范围计数 MEDIUMINT 3 字节 -8,388,608 ~ 8,388,607 0 ~ 16,777,215 中等规模计数（如库存量） INT、INTEGER 4 字节 -2,147,483,648 ~ 2,147,483,647 0 ~ 4,294,967,295 用户ID、订单号 BIGINT 8 字节 -9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807 0 ~ 18,446,744,073,709,551,615 分布式ID、金融大数

• 指定无符号：col int(M) UNSIGNED；
• M 表示最小显示宽度，并不是存储的长度；最大显示宽度255；默认使用空格填充，可启用zerofill用0填充。
• 如果为数值列指定 ZEROFILL ，MySQL 会自动将该列添加 UNSIGNED 属性。

CREATE TABLE `t_user` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `age` int(4) unsigned zerofill DEFAULT NULL, `city` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8; INSERT INTO `t_user` VALUES (1, 'wft', 18, 'nanjing'); SELECT * FROM `t_user` 

(二) 浮点数类型

类型 存储空间 精度特性 适用场景 FLOAT 4 – 8字节 单精度，约7位有效数字 不建议使用 DOUBLE 8 字节 双精度，约15位有效数字 不建议使用 DECIMAL 可变 精确存储（M+2字节） 财务金额、合同数值

• FLOAT(M,D)、DOUBLE(M,D)、DECIMAL(M,D)其中M 是可以存储的总位数，D表示小数点后的位数（最大为30，但不应该大于M-2）。如DECIMAL(5,2)最小值是 -999.99最大值是 999.99。
• 指定无符号：col DECIMAL(M,D) UNSIGNED；与整数类型不同，列值的上限不变。
• 精度问题：FLOAT 和 DOUBLE 存储时可能产生四舍五入误差（如 0.1 无法精确表示）。
• 使用DECIMAL替换FLOAT 和 DOUBLE存储浮点型；或者把小数直接乘以10的N方转换成整数存储。如把存储的金额从元替换成分。

CREATE TABLE `t_user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `age` int(4) unsigned zerofill DEFAULT NULL, `city` varchar(255) DEFAULT NULL, `salary` float DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; INSERT INTO `t_user` (`id`, `name`, `age`, `city`, `salary`) VALUES (1, 'wft', 0018, 'nanjing', 0.2); INSERT INTO `t_user` (`id`, `name`, `age`, `city`, `salary`) VALUES (2, 'zs', 0018, 'nanjing', 0.1); SELECT sum(salary) FROM `t_user` 

• DECIMAL （以及 NUMERIC ）列的值使用二进制格式表示，将九个十进制（基数为 10）数字打包到四个字节中。每个值的整数部分和小数部分的存储分别确定。每个九位数的倍数需要四个字节，而“剩余”的数字需要四字节的一部分。超出数字所需的存储空间由以下表格给出。

剩余数字 字节数 0 0 1-2 1 3-4 2 5-6 3 7-9 4

示例：

• DECIMAL(18,9)：整数部分 9 位（4 字节） + 小数部分 9 位（4 字节） → 总 8 字节。
• DECIMAL(20,6)：整数部分 14 位（前 9 位占 4 字节，剩余 5 位占 3 字节） + 小数部分 6 位（3 字节） → 总 10 字节。

注意：

• 超过最大值会报错

SELECT  + 1 

• 无符号计算结果为负数报错

SELECT CAST(0 AS UNSIGNED) - 1; 

二、字符串类型

类型 存储特性 最大长度 适用场景 存储空间 CHAR 定长存储，尾部填充空格 0-255 字符 固定长度数据（MD5、手机号码） L 字节 VARCHAR 变长存储，仅存实际数据 0-65535 字节 可变长度文本（如用户名、地址） L+1 或 L+2 字节 TEXT 长文本存储（表外存储） 64KB（约65535字节） 文章内容、评论等 L+2字节 BLOB 二进制大对象（表外存储） 64KB（约65535字节） 图片、PDF等二进制文件 L+2字节 ENUM 单值枚举 65535 个选项 固定选项字段（如性别、状态） 1-2字节 SET 多值枚举 64 个选项 多选标签（如兴趣爱好） 1-8字节

• VARCHAR长度不超过255直接，只需要额外的1个直接记录字符串的长度，超过255则需要额外的2个字节来记录字符串的长度。
• varchar是可变长字符串，不预先分配存储空间，长度不要超过5000 ，如果存储长度大于此值，定义字段类型为text，独立出来一张表，避免影响其它字段索引效率
• 分别使用VARCHAR(5)和VARCHAR(200)存储hello字符串，实际数据占用的空间是相同的；但是对此字段的排序和索引在内存占用的大小后者是前者的4倍。

不同编码占用字节情况

编码 类型 字节数 ASCII/Latin1 数字 1字节 字母 1字节 中文 不支持 UTF-8 数字 1字节 字母 1字节 中文 3字节 GBK 数字 1字节 字母 1字节 中文 2字节

枚举类型ENUM

1、枚举的创建（每个枚举都会有有一个对应的索引值，索引值从1开始）

-- 创建线程状态表包含一个枚举字段status CREATE TABLE `thread_status` ( `id` int(11) NOT NULL AUTO_INCREMENT, `status` enum('NEW','RUNNABLE','BLOCKED','WAITING','TIMED_WAITING','TERMINATED') DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- 插入数据，插入的时候可以直接插入具体的枚举值，或者枚举对应的序号（序号从1开始） INSERT INTO thread_status(id, status) VALUES (1, 'NEW'); INSERT INTO thread_status(id, status) VALUES (2, 2); -- 查询数据 SELECT * FROM thread_status; 

2、插入一个异常的数据，直接存储为空字符串

-- 当插入一个不存在的枚举值或者序号时，数据库中直接存储一个空字符串 INSERT INTO thread_status(id, status) VALUES (3, 'STOP'); INSERT INTO thread_status(id, status) VALUES (4, 10); -- 插入null值，数据库存储null INSERT INTO thread_status(id, status) VALUES (5, null); -- 查询数据 SELECT * FROM thread_status; 

可通过索引值为0查询错误的数据

SELECT * FROM thread_status WHERE status = 0; 

可通过索引值为null查询空值

3、枚举的排序：只会按照枚举定义的顺序排序，也可以理解按照枚举的索引值排序

-- 插入测试数据 INSERT INTO thread_status(id, status) VALUES (6, 'BLOCKED'); INSERT INTO thread_status(id, status) VALUES (7, 'WAITING'); INSERT INTO thread_status(id, status) VALUES (8, 'TIMED_WAITING'); INSERT INTO thread_status(id, status) VALUES (9, 'TERMINATED'); -- 查询数据 SELECT * FROM thread_status ORDER BY status; 

完整的枚举和索引对应关系：

Value 值 Index 索引 NULL NULL ”或者不存在的枚举 0 NEW 1 RUNNABLE 2 BLOCKED 3 WAITING 4 TIMED_WAITING 5 TERMINATED 6

4、优点

• 存储空间小，例如 100 万行 ENUM(‘男’) 仅需 1MB，而 VARCHAR 需 2MB。
• 查询数据快，整数比较比字符串更快

5、风险

• 修改成本高
• 排序是按照索引值排序
• 不要创建纯数字的枚举字段，容易跟枚举的索引值混淆

SET类型

1、SET类型的创建（每个枚举值分配 1 位二进制位（从低位到高位依次对应第 1 到第 N 个值）。

-- 创建一张权限模式表， CREATE TABLE `chmod_model` ( `id` int(11) NOT NULL AUTO_INCREMENT, `status` set('read','write','exec') DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- 插入具体的SET元素 INSERT INTO chmod_model VALUES (1, 'read'); INSERT INTO chmod_model VALUES (2, 'write'); INSERT INTO chmod_model VALUES (3, 'exec'); -- 插入SET元素对应的十进制 INSERT INTO chmod_model VALUES (4, 1); INSERT INTO chmod_model VALUES (5, 2); INSERT INTO chmod_model VALUES (6, 4); SELECT * FROM chmod_model; 

SET类型插入多个值

-- 下面3条语句的执行结果是一样的 INSERT INTO chmod_model VALUES (7, 'read,write');-- 多个元素用","分隔 INSERT INTO chmod_model VALUES (8, 'read,write,read,write');-- SET 去重 INSERT INTO chmod_model VALUES (9, 3);-- read = 1, write = 2, 1 + 2 = 3 

对应十进制和二进制的数值

SET成员 十进制值 二进制值 read 1 0001 write 2 0010 exec 4 0100

现在知道Linux中给文件授权的777、750的哪里来的吧。

2、和ENUM一样插入一个异常的数据，直接存储为空字符串，null直接存储为null

查询不存在的元素

SELECT * FROM chmod_model WHERE status = 0; 

查询null

SELECT * FROM chmod_model WHERE status is null; 

3、使用FIND_IN_SET或者LIKE查询元素

SELECT * FROM chmod_model WHERE FIND_IN_SET('read', status) > 0; 

SELECT * FROM chmod_model WHERE status LIKE ‘%read%’;

注意：精确匹配查询必须按照SET元素的顺序查询数据read,write和write,read查询的结果不一样。

SELECT * FROM chmod_model WHERE status = 'read,write';-- 精确匹配可以查询到数据 SELECT * FROM chmod_model WHERE status = 'write,read';-- 必须按照SET的顺序才能查询到数据 

三、时间类型

类型 存储格式 范围 存储空间 时区特性 精度支持 DATE YYYY-MM-DD 1000-01-01 ~ 9999-12-31 3 字节 无 到天 TIME HH:MM:SS[.fff] -838:59:59 ~ 838:59:59 3-6 字节 无 到微秒（需显式声明） DATETIME YYYY-MM-DD HH:MM:SS[.fff] 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 5-8 字节 无 到微秒（需显式声明） TIMESTAMP YYYY-MM-DD HH:MM:SS[.fff] UTC 1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC 4-7 字节 自动转换为 UTC 存储 到微秒（需显式声明） YEAR YYYY 或 YY 1901 ~ 2155（YYYY） 1 字节 无 到年

• 使用 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 列定义子句可以指定自动初始化和更新到当前日期和时间。

四、其他类型

(一) BINARY和VARBINARY类型

类型 存储特性 最大长度 适用场景 存储空间 BINARY 定长存储，尾部填充0x00 0-255 字节 哈希值 L 字节 VARBINAR 变长存储 1-65,535 字节 加密密钥 L+1 或 L+2 字节 BLOB 变长存储 64 KB，0~65,535 字节 图片、音频、视频、文档 L+2 字节

• TEXT 类型包括了 TINYTEXT 、 TEXT 、 MEDIUMTEXT 和 LONGTEXT， BLOB 类型也包括了 TINYBLOB 、 BLOB 、 MEDIUMBLOB 和 LONGBLOB。区别就是可以存储的大小不同。
• BINARY(3) 列， ‘a ‘ 在插入时变为 ‘a \0’ 。 ‘a\0’ 在插入时变为 ‘a\0\0’ 。
• 不建议使用BLOB存储图片、音频、视频和文档，推荐使用专业的对象存储专门存储文件（华为的OBS、阿里的OSS、开源的FastDFS）,数据库只需要存储对应的文件key.

(二) 空间类型

这里不做过多讲解，详细参考。个人建议使用专业的图数据库(Neo4j、JanusGraph、NeblaGraph)。

https://dev.mysql.com/doc/refman/5.7/en/spatial-types.html

五、附录

(一) SQL模式

模式名称 作用描述 典型影响场景 ONLY_FULL_GROUP_BY 强制 GROUP BY子句包含所有非聚合列，避免非确定性查询结果 聚合查询（如 SELECT name, COUNT(*) FROM users GROUP BY name） STRICT_TRANS_TABLES 严格模式，禁止插入非法值（如类型不匹配、超出范围的数值） 事务型表的数据写入（如 INSERT或 UPDATE操作） NO_ZERO_IN_DATE 禁止日期字段中包含零值部分（如2025-00-01），但允许 0000-00-00 日期字段校验（如订单日期、日志时间） NO_ZERO_DATE 禁止插入零日期（0000-00-00），需显式设置默认值或允许 NULL 日期字段默认值设置 ERROR_FOR_DIVISION_BY_ZERO 除数为零时抛出错误（而非返回 NULL），需结合严格模式生效 数学运算（如 SELECT 1/0） NO_AUTO_CREATE_USER 禁止通过 GRANT语句自动创建无密码用户，需显式指定密码 用户权限管理（如 GRANT命令） NO_ENGINE_SUBSTITUTION 创建表时若指定不可用存储引（如 MyISAM被禁用），则报错而非自动替换为 InnoDB。 表引擎配置（如 CREATE TABLE）

1. 查看当前SQL模式

-- 查看全局默认模式 SELECT @@GLOBAL.sql_mode; -- 查看当前会话模式 SELECT @@SESSION.sql_mode; 

2. 设置SQL模式

• 临时修改（仅当前会话）

SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; 

• 永久修改（配置文件）

修改 my.cnf 或 my.ini，在 [mysqld] 段添加以下内容，修改完成后重启服务。

[mysqld] sql_mode = "ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" 

(二) 其他

• MySQL安装包：https://dev.mysql.com/downloads/mysql/
• MySQL数据类型：https://dev.mysql.com/doc/refman/5.7/en/data-types.html