IT策士 10余年一线大厂经验专注 IT 思维、架构、职场进阶。我会在各个平台持续发布最新文章助你少走弯路。建好了库和表接下来最让人纠结的就是这一列该用什么数据类型选错了轻则浪费空间、重则查询缓慢甚至数据丢失。今天我们就来一场 MySQL 数据类型的“选品大会”配合 Python 实打实地看看每种类型怎么用、什么时候用、有什么坑。1. 数值类型不只是 INT 和 DECIMALMySQL 的数值类型分为整数、浮点数、定点数。选型核心在于范围够不够、精度准不准、空间省不省。1.1 整数类型选型原则能用小的就别用大的。状态标识0/1用 TINYINT年龄用 TINYINT UNSIGNED主键量级上亿才考虑 BIGINT。用 Python 建表验证一下整数范围importmysql.connector connmysql.connector.connect(host127.0.0.1,port3306,userroot,passwordMyNewPass123!,databaseshop)cursorconn.cursor()# 建一张测试表cursor.execute( CREATE TABLE IF NOT EXISTS int_test(idINT AUTO_INCREMENT PRIMARY KEY, age TINYINT UNSIGNED, status TINYINT DEFAULT0, big_id BIGINT)ENGINEInnoDB)print(✅ 整数测试表创建成功)# 插入正常数据cursor.execute(INSERT INTO int_test (age, status, big_id) VALUES (25, 1, 9000000000))conn.commit()print(✅ 插入 25 岁、状态1、big_id90亿 成功)# 尝试插入超出 TINYINT UNSIGNED 范围的年龄255以上try: cursor.execute(INSERT INTO int_test (age) VALUES (256))conn.commit()except mysql.connector.DataError as e: print(f❌ 年龄 256 插入失败: {e})预期输出✅ 整数测试表创建成功 ✅ 插入25岁、状态1、big_id90亿 成功 ❌ 年龄256插入失败:1264(22003): Out of range valueforcolumnageat row11.2 浮点数与定点数FLOAT/DOUBLE近似值存在精度丢失绝不用于金额。DECIMAL(M,D)精确值M 为总位数D 为小数位数。如DECIMAL(10,2)表示最大99999999.99。cursor.execute( CREATE TABLE IF NOT EXISTS money_test(price_float FLOAT, price_double DOUBLE, price_decimal DECIMAL(10,2)))cursor.execute(INSERT INTO money_test VALUES (0.10.2, 0.10.2, 0.10.2))cursor.execute(SELECT * FROM money_test)rowcursor.fetchone()print(fFLOAT: {row[0]})print(fDOUBLE: {row[1]})print(fDECIMAL:{row[2]})预期输出可能略有差异FLOAT:0.30000001192092896DOUBLE:0.30000000000000004DECIMAL:0.30结论涉及金钱、库存扣减一律用 DECIMAL永不踩坑。2. 字符串类型VARCHAR 与 CHAR 的真相2.1 VARCHAR(n)可变长存储实际字符 1~2 字节长度前缀。最大 65535 字节但实际受行大小和字符集影响。VARCHAR(255)曾为经典但现在 8.0 更推荐按需定义避免临时表被撑大。2.2 CHAR(n)定长始终占用 n 个字符的空间末尾空格会被截断。适合存储固定长度的值手机号、MD5 哈希、身份证号18位。读写效率稍高因为长度固定无需计算变长头部。2.3 字符集占用字节的影响VARCHAR(10)在 utf8mb4 下最大可能占用 40 字节一个字符最多 4 字节。定义时n指的是字符数不是字节数。Python 演示插入与长度截断cursor.execute( CREATE TABLE IF NOT EXISTS str_test(name VARCHAR(5), code CHAR(5)))# CHAR 自动补空格cursor.execute(INSERT INTO str_test VALUES (Tom, A))cursor.execute(SELECT name, code, CHAR_LENGTH(code), LENGTH(code) FROM str_test)rowcursor.fetchone()print(fname{row[0]}, code{row[1]}, 字符数{row[2]}, 字节数{row[3]})# VARCHAR 超长报错try: cursor.execute(INSERT INTO str_test (name) VALUES (TooLongName))except mysql.connector.DataError as e: print(f❌ VARCHAR 超长: {e})预期输出nameTom,codeA , 字符数5, 字节数5❌ VARCHAR 超长:1406(22001): Data too longforcolumnnameat row1常见误区VARCHAR(10)和VARCHAR(1000)在存储短字符串时空间开销几乎一样只有额外长度前缀差异但后者可能导致排序、临时表使用更多的内存。切勿为了“保险”随意加大长度要根据业务合理定义。2.4 TEXT 系列用于存储大段文本文章正文、JSON 字符串TINYTEXT (255B)、TEXT (64KB)、MEDIUMTEXT (16MB)、LONGTEXT (4GB)缺点不能有默认值、全量读取影响性能、通常单独存或使用外部对象存储。3. 日期时间类型TIMESTAMP 还是 DATETIME推荐绝大多数场景用DATETIME因为它范围大且与业务时区解耦只有需要自动时区转换的场景如全球化系统记录用户操作才用 TIMESTAMP。Python 验证时区转换cursor.execute(SET time_zone 00:00)# 设为 UTCcursor.execute( CREATE TABLE IF NOT EXISTS dt_test(ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, dt DATETIME DEFAULT CURRENT_TIMESTAMP))cursor.execute(INSERT INTO dt_test VALUES ())cursor.execute(SELECT * FROM dt_test)rowcursor.fetchone()print(fUTC 下: TIMESTAMP{row[0]}, DATETIME{row[1]})# 切回东八区再查cursor.execute(SET time_zone 08:00)cursor.execute(SELECT * FROM dt_test)rowcursor.fetchone()print(f08:00: TIMESTAMP{row[0]}, DATETIME{row[1]})预期输出UTC 下:TIMESTAMP2025-07-21 08:30:00,DATETIME2025-07-21 08:30:00 08:00:TIMESTAMP2025-07-2116:30:00,DATETIME2025-07-21 08:30:00可见 TIMESTAMP 会随着时区自动偏移而 DATETIME 始终不变。4. 半结构化类型ENUM、SET 与 JSON4.1 ENUM单选枚举内部存储为整数1、2、3…可读性好但扩展麻烦。gender ENUM(male,female,other)优点存储紧凑查询时可写字符串。缺点新增枚举值需 ALTER TABLE且顺序调整危险。适合不变的值如星期。4.2 SET多选集合hobbies SET(reading,sports,music)内部用位存储最多 64 个选项。查询可用FIND_IN_SET。4.3 JSON灵活的文档存储MySQL 8.0 对 JSON 的支持已经相当成熟能创建虚拟列并加索引、丰富的 JSON 函数。cursor.execute( CREATE TABLE IF NOT EXISTS user_profile(idINT PRIMARY KEY AUTO_INCREMENT, profile JSON))# 插入 JSONcursor.execute( INSERT INTO user_profile(profile)VALUES({name:Alice,tags:[tech,ai],age:28}))conn.commit()# 查询 JSON 字段cursor.execute(SELECT profile-$.name AS name FROM user_profile)print(姓名:, cursor.fetchone()[0])# 用 JSON_CONTAINS 检查数组cursor.execute(SELECT * FROM user_profile WHERE JSON_CONTAINS(profile-$.tags, \ai\))print(包含 ai 标签的用户:, cursor.fetchone())预期输出姓名: Alice 包含 ai 标签的用户:(1,{name: Alice, tags: [tech, ai], age: 28})最佳实践JSON 列不适合频繁按内部字段查询的场景性能远不如普通列加索引。一般用于存储动态扩展的元数据核心业务字段仍应独立建列。5. 其他常用类型速览BINARY / VARBINARY二进制字符串存哈希、UUID 字节等。BLOB存图片、文件但通常只存路径文件本身放对象存储。BOOLEANTINYINT(1) 的别名0 为 false非 0 为 true。6. 动手试试设计一张完整的员工表现在请你为一家公司的 HR 系统设计employees表并录入几条数据。要求自增主键 id使用 INT。姓名 name (VARCHAR(50)不为空)工号 code (CHAR(6)唯一)性别 gender (ENUM(‘男’,‘女’))。薪资 salary 使用 DECIMAL(10,2)保证精度。入职日期 hire_date 使用 DATE。扩展信息 extra 使用 JSON可存紧急联系人、证书等动态字段。创建时间 created_at 使用 DATETIME DEFAULT CURRENT_TIMESTAMP。用 Python 建表并插入一条你自己的模拟数据最后用SELECT打印整行。参考代码connmysql.connector.connect(host127.0.0.1,port3306,userroot,passwordMyNewPass123!,databaseshop)cursorconn.cursor()cursor.execute( CREATE TABLE IF NOT EXISTS employees(idINT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50)NOT NULL, code CHAR(6)NOT NULL UNIQUE, gender ENUM(男,女), salary DECIMAL(10,2), hire_date DATE, extra JSON, created_at DATETIME DEFAULT CURRENT_TIMESTAMP)ENGINEInnoDB)print(✅ 表 employees 创建成功)cursor.execute( INSERT INTO employees(name, code, gender, salary, hire_date, extra)VALUES(张三,EMP001,男,15000.00,2025-07-01,{contact:13900001111,cert:[PMP]}))conn.commit()cursor.execute(SELECT * FROM employees)forcolincursor.description: print(col[0],end\t)print()forrowincursor.fetchall(): print(row)cursor.close()conn.close()预期输出✅ 表 employees 创建成功idname code gender salary hire_date extra created_at1张三 EMP001 男15000.002025-07-01{contact:13900001111,cert:[PMP]}2025-07-2116:45:007. 总结数据类型的选择直接影响数据的正确性、存储效率和查询性能。记住三条铁律金额永远用 DECIMAL定长文本用 CHAR变长用 VARCHAR大文本用 TEXT日期不涉及时区用 DATETIME否则用 TIMESTAMP注意 2038 问题每当我们设计新表时花 5 分钟仔细斟酌每列的类型往往能避免未来数倍的迁移痛苦。下篇我们将进入DML 实战用 Python 实现高效的 INSERT/UPDATE/DELETE同时揭秘批量插入的性能秘籍。下次见想了解更多还可以去各个平台搜索「IT策士」一起升级 IT 思维