1. 为什么说数据类型不是“贴标签”而是数据库的“操作系统内核”刚接触 SQL 的人常把VARCHAR(50)当成给字段起个“昵称”——就像给 Excel 单元格加个备注“这里填名字”。但这种理解离真实场景差了整整一个数据库引擎的距离。我带过几十个刚转行的新人几乎所有人都在入职第三周左右栽在同一类坑里明明逻辑没错SELECT * FROM orders WHERE amount 1000却查不出结果或者导出报表时金额列突然变成999.9999999999999又或者两个系统对接时2024-03-15在 A 库能查在 B 库直接报错。问题根源90% 出在数据类型选错了。数据类型根本不是标签它是数据库执行所有操作的底层契约。它决定了存储层这个值在磁盘上占多少字节、怎么排列比如INT是 4 字节二进制补码DECIMAL(10,2)是按整数小数位分别编码计算层10 5在 MySQL 里会隐式转成15在 PostgreSQL 里直接报错因为BOOLEAN和TEXT的隐式转换规则完全不同索引层VARCHAR(255)和VARCHAR(100)建 B-Tree 索引时前者节点分裂更频繁查询慢 15%20%我们线上订单表实测过网络层TIMESTAMPTZ传到应用端是带时区的2024-03-15T09:30:0008:00而TIMESTAMP只传2024-03-15 09:30:00Java 的LocalDateTime接收后者会丢时区信息导致跨时区订单时间全乱。你写的每一行CREATE TABLE本质是在给数据库下指令“请用这套规则管理我的数据”。它不像编程语言里的变量声明声明完就完了它是持续生效的约束力贯穿插入、查询、索引、备份、主从同步全过程。我见过最痛的教训是某电商把用户手机号存成INT结果遇到13800138000这种以1开头的号——MySQL 的INT最大值是214748364713800138000直接溢出变负数用户登录时密码校验永远失败。排查了三天最后发现建表语句里写着phone INT NOT NULL。所以这本指南不讲“有哪些类型”而是带你钻进数据库的血管里看数据类型怎么真正影响你的每一行代码、每一次查询、每一分性能。我会用真实生产环境的案例、可复现的 SQL 脚本、跨三大主流数据库MySQL 8.0 / PostgreSQL 15 / SQL Server 2019的对比实验告诉你为什么DECIMAL(10,2)存钱比FLOAT少踩 3 类致命坑为什么VARCHAR(100)比VARCHAR(255)在千万级用户表里省下 12GB 存储为什么DATE和DATETIME混用会让 BI 报表凌晨 3 点的数据消失以及最关键的——当你接手一个老系统如何用 5 分钟 SQL 快速诊断出数据类型设计的硬伤。适合谁读如果你写过INSERT INTO users VALUES (...)但没想过users.name为什么是VARCHAR(50)而不是VARCHAR(255)如果你调过WHERE created_at 2024-01-01但不知道2024-01-01是字符串还是日期字面量如果你被Column status cannot be null报错卡住过却只想着加DEFAULT active而不是检查status该不该是ENUM或BOOLEAN——那这篇就是为你写的。接下来的内容没有一句废话全是我在金融、电商、SaaS 项目里亲手验证过的结论。2. 数据类型四大核心分类原理、选型逻辑与跨库差异解剖2.1 数值类型精度即正义存储即成本数值类型绝不是“数字就用 INT带小数点就用 FLOAT”这么简单。它的选择直接决定你的财务系统会不会算错一分钱科学计算会不会累积误差甚至影响服务器采购预算。我拆解三个关键维度精度需求、范围预估、存储开销。先说最典型的陷阱用 FLOAT 存钱。新手看到salary DECIMAL(10,2)觉得麻烦改用salary FLOAT。表面看55000.00插进去显示一样但执行SELECT salary * 0.1 FROM employees WHERE id 1MySQL 可能返回5499.999999999999PostgreSQL 返回5500.0SQL Server 返回5500.000000000000。为什么因为FLOAT遵循 IEEE 754 标准用二进制近似表示十进制小数。0.1在二进制里是无限循环小数就像十进制的1/3 0.333...必须截断误差在计算中会放大。我们曾有个支付对账脚本用FLOAT算手续费百万笔交易累计误差达 237.8 元审计直接叫停。正确方案是DECIMAL(p,s)或NUMERIC(p,s)二者在标准 SQL 中等价。p是总位数s是小数位数。例如DECIMAL(10,2)表示最多 10 位数字其中 2 位小数能存99999999.99。它内部把数字当整数处理存9999999999再除以10^2完全避免二进制误差。但代价是存储更大DECIMAL(10,2)占 5 字节FLOAT只占 4 字节。所以原则很明确只要涉及金钱、比例、精确计数如库存数量必须用 DECIMAL/NUMERIC只有科学计算、传感器原始数据等允许微小误差的场景才考虑 FLOAT/DOUBLE。再看整数类型。很多人无脑用BIGINT8 字节觉得“以后数据多了不怕”。但实际业务中用户 ID 用INT4 字节足够支撑 42 亿用户订单号用BIGINT是因为要支持分库分表后全局唯一。关键在范围预估TINYINT1 字节范围 -128~127适合状态码0待处理,1成功,2失败SMALLINT2 字节-32768~32767适合省份编号、商品分类 IDINT4 字节-2147483648~2147483647覆盖 99% 的主键和计数场景BIGINT8 字节-9223372036854775808~9223372036854775807仅用于超大规模 ID 或时间戳毫秒级。提示MySQL 的TINYINT(1)常被误认为“布尔型”但它本质还是整数。TINYINT(1)只是显示宽度提示存2完全合法。真要布尔PostgreSQL 用原生BOOLEAN存true/falseSQL Server 用BIT存0/1MySQL 5.7 推荐用TINYINT(1)但需应用层强校验或升级到 8.0 用BOOLEAN别名实际仍是TINYINT。跨库差异最明显的是无符号整数。MySQL 支持INT UNSIGNED0~4294967295PostgreSQL 用SERIAL自动INTEGER不支持无符号SQL Server 用INT但可通过CHECK约束模拟。这意味着如果你的 MySQL 表用id INT UNSIGNED AUTO_INCREMENT迁到 PostgreSQL 时要么改id SERIAL自动INTEGER要么手动建id BIGSERIAL并设CHECK (id 0)。我们迁移一个 20 亿记录的用户表时就因忽略这点导致主键冲突重跑三天。2.2 字符串类型长度不是数字游戏而是性能开关字符串类型的选择直接影响查询速度、存储空间、甚至应用层代码复杂度。核心矛盾在于固定长度 vs 可变长度确定长度 vs 不确定长度。CHAR(n)是固定长度。比如country_code CHAR(2)存US时实际占 2 字节存CN也占 2 字节。优势是存储和检索极快数据库直接按偏移量读劣势是浪费空间存A也占 2 字节。适用场景极少全球国家代码ISO 3166-1 alpha-2、货币代码ISO 4217、机场三字码IATA。我们做过测试在 1 亿行的订单表里currency CHAR(3)比currency VARCHAR(3)查询快 8%但存储多占 1.2GB。所以除非是高频查询且长度绝对固定否则别碰CHAR。VARCHAR(n)是可变长度。n是最大长度实际存多少占多少字节外加 1~2 字节记录长度。比如name VARCHAR(100)存Alice占 516 字节存Alexander the Great19 字符占 19120 字节。关键点在于n不是越大越好。MySQL 5.7 对VARCHAR的n有严格限制单行总长度不能超 65535 字节。如果定义VARCHAR(20000)再加几个字段很容易超限报错。更隐蔽的问题是排序和临时表ORDER BY name时MySQL 会把name全字段加载到内存排序VARCHAR(255)比VARCHAR(100)多占 155 字节内存10 万行排序就多用 15MB 内存可能触发磁盘临时表速度暴跌 5 倍。我们优化一个报表查询把product_name VARCHAR(255)改成VARCHAR(120)内存占用从 2.1GB 降到 1.3GB查询从 47 秒降到 8 秒。TEXT和CLOB是大文本专用。TEXT在 MySQL 中最大 64KBPostgreSQL 中无硬限制实际受toast表限制SQL Server 中VARCHAR(MAX)最大 2GB。它们不参与内存排序数据库会用磁盘临时表所以ORDER BY description会极慢。正确做法是大文本只用于存储查询时用SUBSTRING(description, 1, 200)截取摘要或单独建全文索引。我们有个日志表log_content TEXT原本WHERE log_content LIKE %error%全表扫描 2 分钟加FULLTEXT(log_content)后降到 0.3 秒。Unicode 支持是另一道坎。VARCHAR默认是数据库字符集如 MySQL 的utf8mb4但NVARCHARSQL Server和NCHAR显式声明 Unicode。区别在于VARCHAR存中文需要utf8mb4字符集支持 4 字节 UTF-8而NVARCHAR强制用 UCS-22 字节或 UTF-16可能 4 字节兼容性更好但存储翻倍。我们对接一个日本客户系统他们用NVARCHAR存U20BB7需 4 字节 UTF-8MySQLVARCHAR必须设utf8mb4否则存成?。结论如果业务涉及多语言尤其中日韩、emojiMySQL 用utf8mb4 VARCHARSQL Server 用NVARCHARPostgreSQL 用TEXT默认 UTF-8。2.3 日期时间类型时区不是可选项而是必答题日期类型选错轻则报表时间错 8 小时重则订单丢失、风控失效。核心误区是以为DATETIME和TIMESTAMP只是“存得更多”或“存得更少”。DATE只存日期YYYY-MM-DDTIME只存时间HH:MM:SSDATETIME存日期时间YYYY-MM-DD HH:MM:SSTIMESTAMP也存日期时间但本质是时间戳Unix Epoch 毫秒数。关键差异在时区处理DATETIME是“字面量”存什么就是什么。INSERT INTO t VALUES (2024-03-15 10:00:00)无论服务器时区是 UTC8 还是 UTC-5查出来永远是2024-03-15 10:00:00TIMESTAMP是“相对值”存入时会转成 UTC 时间戳查询时再转回当前会话时区。SET time_zone00:00; INSERT INTO t VALUES (2024-03-15 10:00:00)存的是 UTC 时间SET time_zone08:00; SELECT * FROM t查出来是2024-03-15 18:00:00。这导致经典问题一个部署在新加坡UTC8的系统用DATETIME存用户注册时间运维半夜切到美国西海岸UTC-7服务器所有历史时间显示错 15 小时。而用TIMESTAMP只要会话时区设对显示永远正确。但TIMESTAMP有范围限制MySQL 是 1970~2038 年DATETIME是 1000~9999 年。所以原则是记录事件发生时间如订单创建、日志时间用TIMESTAMP或 PostgreSQL 的TIMESTAMPTZ记录固定日期如生日、合同到期日用DATE记录纯时间如营业时间用TIME。TIMESTAMPTZPostgreSQL和DATETIMEOFFSETSQL Server是终极方案它们把时区信息和时间一起存。2024-03-15 10:00:0008存进去查出来还是带08。我们做跨境支付系统时强制所有时间字段用TIMESTAMPTZ应用层 Java 用OffsetDateTime接收彻底规避时区混乱。MySQL 8.0 的TIMESTAMP已支持时区但语法不如 PostgreSQL 直观。2.4 特殊类型JSON、UUID、数组——不是炫技而是解决真实瓶颈特殊类型常被当成“高级功能”其实它们是为了解决传统关系模型的硬伤。关键看是否满足结构不确定、嵌套深、查询频率低但写入频繁。JSON类型MySQL 5.7/PostgreSQL/SQL Server 2016本质是校验过的字符串但提供-、-操作符快速提取字段。比如用户配置表CREATE TABLE user_settings ( id INT PRIMARY KEY, config JSON ); INSERT INTO user_settings VALUES (1, {theme:dark,notifications:{email:true,sms:false}}); SELECT config-$.theme AS theme FROM user_settings; -- 返回 dark优势是不用为每个配置项建列新增push_token字段无需ALTER TABLE劣势是无法为 JSON 内部字段建索引MySQL 5.7 支持生成列索引但复杂。我们曾用JSON存用户设备信息iOS/Android/版本号但后来发现WHERE config-$.os iOS全表扫描太慢最终拆成os VARCHAR(20), os_version VARCHAR(20)两列查询提速 20 倍。所以JSON适用场景是配置项、日志详情、第三方 API 原始响应体——这些字段你基本不按它查只按 ID 查后解析。UUID通用唯一标识符解决自增 ID 的三大痛点安全暴露id100001让爬虫知道这是第 10 万零一个用户分库分表自增 ID 在不同库可能重复离线生成APP 端可生成 UUID 后异步提交不依赖数据库。但代价是UUID占 36 字符字符串或 16 字节二进制比BIGINT8 字节大一倍B-Tree 索引更臃肿。MySQL 8.0 支持UUID_TO_BIN()将35a2b5e8-1f9c-4a2d-8e1a-3c5b7d9e1f2a转成 16 字节二进制索引效率接近BIGINT。我们所有新微服务都用BINARY(16)存 UUIDSELECT * FROM orders WHERE id UUID_TO_BIN(xxx)性能损失不到 5%。ARRAYPostgreSQL 专属是真正的杀手锏。比如权限系统用户有多个角色传统方案是user_roles关联表查一个用户所有角色要JOIN。用ARRAYCREATE TABLE users ( id SERIAL PRIMARY KEY, name VARCHAR(100), roles TEXT[] DEFAULT ARRAY[user]::TEXT[] ); INSERT INTO users (name, roles) VALUES (Alice, ARRAY[admin,editor]); SELECT * FROM users WHERE admin ANY(roles); -- 直接查无需 JOINANY操作符让数组查询像普通字段一样快。我们用它重构了 SaaS 系统的租户权限QPS 从 1200 提升到 3500。但注意ARRAY不是万能的更新单个元素要UPDATE ... SET roles array_replace(roles, old, new)比关联表UPDATE user_roles SET rolenew WHERE user_id1 AND roleold复杂。所以原则是读多写少、元素数量稳定 100、不需对单个元素建索引的场景用 ARRAY。3. 实操指南从建表到调优手把手构建健壮数据模型3.1 建表黄金七步法拒绝拍脑袋定类型我带团队写建表语句严格执行七步法杜绝“先建再改”的返工。每一步都有明确检查点下面用电商订单表orders为例演示第一步明确业务实体与核心属性订单实体包含订单号、用户 ID、商品列表、总金额、状态、创建时间、支付时间。注意“商品列表”是嵌套结构不是原子字段。第二步逐字段分析数据特征order_no全局唯一长度 20 字符如ORD2024031500001非数字不可为空user_id关联用户表整数范围预估 1~5 亿需索引items商品列表JSON 格式含商品 ID、数量、单价不用于查询条件total_amount金额精确到分范围 0.01~999999.99status状态码枚举值pending/paid/shipped/cancelled共 4 种created_at订单创建时间需精确到秒跨时区paid_at支付时间可能为空未支付订单。第三步匹配数据类型并标注理由字段类型理由order_noVARCHAR(20)非数字前缀长度固定CHAR(20)浪费空间user_idBIGINT用户量超 21 亿INT不够BIGINT索引稍大但安全itemsJSON结构动态不按内部字段查JSON省去关联表total_amountDECIMAL(10,2)金钱必须精确10位支持 99999999.99statusVARCHAR(20)MySQL 不支持ENUM在线 DDL锁表VARCHAR更灵活若用 PostgreSQL 可选ENUMcreated_atTIMESTAMP WITH TIME ZONE(PostgreSQL) /DATETIME(MySQL)PostgreSQL 用TIMESTAMPTZ自动时区转换MySQL 用DATETIME配合应用层统一时区paid_atTIMESTAMP WITH TIME ZONE/DATETIME同上允许为空第四步设置约束与默认值-- PostgreSQL 示例 CREATE TABLE orders ( id SERIAL PRIMARY KEY, order_no VARCHAR(20) NOT NULL UNIQUE, user_id BIGINT NOT NULL REFERENCES users(id), items JSON NOT NULL, total_amount DECIMAL(10,2) NOT NULL CHECK (total_amount 0), status VARCHAR(20) NOT NULL DEFAULT pending, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), paid_at TIMESTAMPTZ NULL, -- 状态检查约束 CONSTRAINT valid_status CHECK (status IN (pending,paid,shipped,cancelled)) );第五步评估索引需求必建索引user_id用户查订单、created_at按时间查、status查待发货订单组合索引(status, created_at)加速WHERE statusshipped ORDER BY created_at DESC避免索引itemsJSON 字段不建普通索引。第六步估算存储与性能按 1 亿订单计算order_no VARCHAR(20)平均 18 字符 × 1 亿 1.8GBuser_id BIGINT8 字节 × 1 亿 0.8GBitems JSON平均 500 字符 × 1 亿 5GBtotal_amount DECIMAL(10,2)5 字节 × 1 亿 0.5GB总计约 8.1GB加上索引约 12GB。确认服务器磁盘充足。第七步编写初始化脚本并验证-- 插入测试数据 INSERT INTO orders (order_no, user_id, items, total_amount, status, paid_at) VALUES (ORD2024031500001, 1001, {items:[{id:101,qty:2,price:99.99}]}, 199.98, paid, NOW()), (ORD2024031500002, 1002, {items:[{id:102,qty:1,price:299.99}]}, 299.99, pending, NULL); -- 验证查询 SELECT order_no, total_amount, status FROM orders WHERE user_id 1001; -- 检查 JSON 提取 SELECT order_no, items-$.items[0].id AS item_id FROM orders;注意MySQL 8.0 的JSON_EXTRACT(items, $.items[0].id)语法更标准但 PostgreSQL 用-更简洁。跨库时建议封装 DAO 层屏蔽差异。3.2 跨数据库迁移实战从 MySQL 到 PostgreSQL 的避坑清单我们曾将一个 500GB 的 MySQL 电商库迁到 PostgreSQL耗时 72 小时其中 60 小时花在数据类型适配。以下是血泪总结的迁移 checklist1. 整数类型映射TINYINT→SMALLINTMySQL 的TINYINT是 1 字节PostgreSQLSMALLINT是 2 字节但TINYINT无符号范围 0~255SMALLINT有符号 -32768~32767完全覆盖INT→INTEGERBIGINT→BIGINTAUTO_INCREMENT→SERIALSERIAL是INTEGERSEQUENCE需手动创建序列或用GENERATED ALWAYS AS IDENTITYPG 10。2. 字符串类型映射VARCHAR(n)→VARCHAR(n)完全兼容TEXT→TEXT兼容但 PostgreSQLTEXT无长度限制MySQLTEXT最大 64KBLONGTEXT→TEXTPostgreSQLTEXT支持超大文本ENUM→VARCHAR(n)或自定义ENUMPostgreSQL 支持CREATE TYPE status AS ENUM (pending,paid);但需提前创建。3. 日期时间类型映射DATETIME→TIMESTAMP WITHOUT TIME ZONE若 MySQL 服务器时区固定可忽略时区TIMESTAMP→TIMESTAMP WITH TIME ZONE推荐自动处理时区DATE→DATE完全兼容TIME→TIME WITHOUT TIME ZONE兼容。4. 布尔类型映射TINYINT(1)→BOOLEANPostgreSQL 原生BOOLEAN0→false1→true迁移脚本需处理UPDATE orders SET status CASE WHEN status 0 THEN false ELSE true END先转字符串再转布尔。5. JSON 类型映射JSON→JSONBPostgreSQL 的JSONB是二进制格式查询更快支持索引迁移时用to_jsonb()函数INSERT INTO pg_orders SELECT id, to_jsonb(items) FROM mysql_orders。6. 索引与约束迁移MySQL 的FULLTEXT索引 → PostgreSQL 的GIN索引 to_tsvector()UNIQUE约束直接迁移FOREIGN KEY需确保引用表已存在。7. 验证脚本必须执行-- 检查行数一致性 SELECT (SELECT COUNT(*) FROM mysql.orders) AS mysql_count, (SELECT COUNT(*) FROM pg.orders) AS pg_count; -- 检查金额精度抽样 1000 行 SELECT o1.total_amount AS mysql_amt, o2.total_amount AS pg_amt FROM mysql.orders o1 JOIN pg.orders o2 ON o1.order_no o2.order_no WHERE o1.total_amount ! o2.total_amount LIMIT 10;实操心得迁移前用pt-table-checksumPercona Toolkit校验 MySQL 主从一致性迁移中用pgloader工具自动处理类型转换比手写mysqldumpsed替换可靠 10 倍迁移后重点压测JOIN查询PostgreSQL 的HASH JOIN和MERGE JOIN策略与 MySQL 不同可能需调整work_mem参数。3.3 性能调优实录从慢查询到毫秒响应的四次迭代一个订单搜索接口初始 SQLSELECT * FROM orders WHERE status shipped AND created_at BETWEEN 2024-01-01 AND 2024-03-01 ORDER BY created_at DESC LIMIT 100;线上平均 8.2 秒。我们分四步优化第一次索引诊断耗时 0.5 小时执行EXPLAIN ANALYZESeq Scan on orders (cost0.00..123456.78 rows12345 width200) (actual time0.123..8200.456 rows100 loops1) Filter: ((status shipped::text) AND (created_at 2024-01-01 00:00:00::timestamp without time zone) AND (created_at 2024-03-01 00:00:00::timestamp without time zone)) Rows Removed by Filter: 999900全表扫描原因status和created_at无组合索引。建索引CREATE INDEX idx_orders_status_created ON orders(status, created_at);查询降至 1.3 秒。但EXPLAIN显示Index Scan using idx_orders_status_created仍扫描 12 万行才找到 100 条。第二次覆盖索引耗时 1 小时SELECT *需回表取所有字段而索引只存status和created_at。建覆盖索引CREATE INDEX idx_orders_covering ON orders(status, created_at) INCLUDE (order_no, user_id, total_amount, paid_at);PostgreSQL 11 支持INCLUDEMySQL 用联合索引INDEX(status, created_at, order_no, user_id, ...)查询降至 0.4 秒。EXPLAIN显示Index Only Scan无需回表。第三次分区裁剪耗时 3 小时订单表已达 2 亿行created_at范围查询仍慢。按月分区-- PostgreSQL 分区表 CREATE TABLE orders_part (LIKE orders INCLUDING ALL) PARTITION BY RANGE (created_at); CREATE TABLE orders_202401 PARTITION OF orders_part FOR VALUES FROM (2024-01-01) TO (2024-02-01); CREATE TABLE orders_202402 PARTITION OF orders_part FOR VALUES FROM (2024-02-01) TO (2024-03-01); -- 重命名原表交换数据 ALTER TABLE orders RENAME TO orders_old; ALTER TABLE orders_part RENAME TO orders;查询降至 0.08 秒。EXPLAIN显示只扫描orders_202401和orders_202402两个分区。第四次物化视图预计算耗时 2 小时业务要求实时查“各状态订单数”SELECT status, COUNT(*) FROM orders GROUP BY status每次扫全表。建物化视图CREATE MATERIALIZED VIEW orders_status_count AS SELECT status, COUNT(*) as cnt FROM orders GROUP BY status; REFRESH MATERIALIZED VIEW CONCURRENTLY orders_status_count; -- 并发刷新不锁表查询SELECT * FROM orders_status_count降至 0.002 秒。最终效果接口 P99 从 8200ms 降至 12ms服务器 CPU 使用率下降 35%。关键经验索引不是越多越好。我们曾为orders表建了 12 个索引导致INSERT速度从 5000 QPS 降到 800 QPS。现在坚持“一个查询一个索引”用pg_stat_statements监控慢查询只对 QPS 10 且耗时 100ms 的查询建索引。4. 常见问题与排查技巧实录生产环境踩坑大全4.1 数据截断为什么“Michael”变成了“Micha”这是新手最常犯的
数据库数据类型选型实战：精度、时区与跨库兼容性指南
1. 为什么说数据类型不是“贴标签”而是数据库的“操作系统内核”刚接触 SQL 的人常把VARCHAR(50)当成给字段起个“昵称”——就像给 Excel 单元格加个备注“这里填名字”。但这种理解离真实场景差了整整一个数据库引擎的距离。我带过几十个刚转行的新人几乎所有人都在入职第三周左右栽在同一类坑里明明逻辑没错SELECT * FROM orders WHERE amount 1000却查不出结果或者导出报表时金额列突然变成999.9999999999999又或者两个系统对接时2024-03-15在 A 库能查在 B 库直接报错。问题根源90% 出在数据类型选错了。数据类型根本不是标签它是数据库执行所有操作的底层契约。它决定了存储层这个值在磁盘上占多少字节、怎么排列比如INT是 4 字节二进制补码DECIMAL(10,2)是按整数小数位分别编码计算层10 5在 MySQL 里会隐式转成15在 PostgreSQL 里直接报错因为BOOLEAN和TEXT的隐式转换规则完全不同索引层VARCHAR(255)和VARCHAR(100)建 B-Tree 索引时前者节点分裂更频繁查询慢 15%20%我们线上订单表实测过网络层TIMESTAMPTZ传到应用端是带时区的2024-03-15T09:30:0008:00而TIMESTAMP只传2024-03-15 09:30:00Java 的LocalDateTime接收后者会丢时区信息导致跨时区订单时间全乱。你写的每一行CREATE TABLE本质是在给数据库下指令“请用这套规则管理我的数据”。它不像编程语言里的变量声明声明完就完了它是持续生效的约束力贯穿插入、查询、索引、备份、主从同步全过程。我见过最痛的教训是某电商把用户手机号存成INT结果遇到13800138000这种以1开头的号——MySQL 的INT最大值是214748364713800138000直接溢出变负数用户登录时密码校验永远失败。排查了三天最后发现建表语句里写着phone INT NOT NULL。所以这本指南不讲“有哪些类型”而是带你钻进数据库的血管里看数据类型怎么真正影响你的每一行代码、每一次查询、每一分性能。我会用真实生产环境的案例、可复现的 SQL 脚本、跨三大主流数据库MySQL 8.0 / PostgreSQL 15 / SQL Server 2019的对比实验告诉你为什么DECIMAL(10,2)存钱比FLOAT少踩 3 类致命坑为什么VARCHAR(100)比VARCHAR(255)在千万级用户表里省下 12GB 存储为什么DATE和DATETIME混用会让 BI 报表凌晨 3 点的数据消失以及最关键的——当你接手一个老系统如何用 5 分钟 SQL 快速诊断出数据类型设计的硬伤。适合谁读如果你写过INSERT INTO users VALUES (...)但没想过users.name为什么是VARCHAR(50)而不是VARCHAR(255)如果你调过WHERE created_at 2024-01-01但不知道2024-01-01是字符串还是日期字面量如果你被Column status cannot be null报错卡住过却只想着加DEFAULT active而不是检查status该不该是ENUM或BOOLEAN——那这篇就是为你写的。接下来的内容没有一句废话全是我在金融、电商、SaaS 项目里亲手验证过的结论。2. 数据类型四大核心分类原理、选型逻辑与跨库差异解剖2.1 数值类型精度即正义存储即成本数值类型绝不是“数字就用 INT带小数点就用 FLOAT”这么简单。它的选择直接决定你的财务系统会不会算错一分钱科学计算会不会累积误差甚至影响服务器采购预算。我拆解三个关键维度精度需求、范围预估、存储开销。先说最典型的陷阱用 FLOAT 存钱。新手看到salary DECIMAL(10,2)觉得麻烦改用salary FLOAT。表面看55000.00插进去显示一样但执行SELECT salary * 0.1 FROM employees WHERE id 1MySQL 可能返回5499.999999999999PostgreSQL 返回5500.0SQL Server 返回5500.000000000000。为什么因为FLOAT遵循 IEEE 754 标准用二进制近似表示十进制小数。0.1在二进制里是无限循环小数就像十进制的1/3 0.333...必须截断误差在计算中会放大。我们曾有个支付对账脚本用FLOAT算手续费百万笔交易累计误差达 237.8 元审计直接叫停。正确方案是DECIMAL(p,s)或NUMERIC(p,s)二者在标准 SQL 中等价。p是总位数s是小数位数。例如DECIMAL(10,2)表示最多 10 位数字其中 2 位小数能存99999999.99。它内部把数字当整数处理存9999999999再除以10^2完全避免二进制误差。但代价是存储更大DECIMAL(10,2)占 5 字节FLOAT只占 4 字节。所以原则很明确只要涉及金钱、比例、精确计数如库存数量必须用 DECIMAL/NUMERIC只有科学计算、传感器原始数据等允许微小误差的场景才考虑 FLOAT/DOUBLE。再看整数类型。很多人无脑用BIGINT8 字节觉得“以后数据多了不怕”。但实际业务中用户 ID 用INT4 字节足够支撑 42 亿用户订单号用BIGINT是因为要支持分库分表后全局唯一。关键在范围预估TINYINT1 字节范围 -128~127适合状态码0待处理,1成功,2失败SMALLINT2 字节-32768~32767适合省份编号、商品分类 IDINT4 字节-2147483648~2147483647覆盖 99% 的主键和计数场景BIGINT8 字节-9223372036854775808~9223372036854775807仅用于超大规模 ID 或时间戳毫秒级。提示MySQL 的TINYINT(1)常被误认为“布尔型”但它本质还是整数。TINYINT(1)只是显示宽度提示存2完全合法。真要布尔PostgreSQL 用原生BOOLEAN存true/falseSQL Server 用BIT存0/1MySQL 5.7 推荐用TINYINT(1)但需应用层强校验或升级到 8.0 用BOOLEAN别名实际仍是TINYINT。跨库差异最明显的是无符号整数。MySQL 支持INT UNSIGNED0~4294967295PostgreSQL 用SERIAL自动INTEGER不支持无符号SQL Server 用INT但可通过CHECK约束模拟。这意味着如果你的 MySQL 表用id INT UNSIGNED AUTO_INCREMENT迁到 PostgreSQL 时要么改id SERIAL自动INTEGER要么手动建id BIGSERIAL并设CHECK (id 0)。我们迁移一个 20 亿记录的用户表时就因忽略这点导致主键冲突重跑三天。2.2 字符串类型长度不是数字游戏而是性能开关字符串类型的选择直接影响查询速度、存储空间、甚至应用层代码复杂度。核心矛盾在于固定长度 vs 可变长度确定长度 vs 不确定长度。CHAR(n)是固定长度。比如country_code CHAR(2)存US时实际占 2 字节存CN也占 2 字节。优势是存储和检索极快数据库直接按偏移量读劣势是浪费空间存A也占 2 字节。适用场景极少全球国家代码ISO 3166-1 alpha-2、货币代码ISO 4217、机场三字码IATA。我们做过测试在 1 亿行的订单表里currency CHAR(3)比currency VARCHAR(3)查询快 8%但存储多占 1.2GB。所以除非是高频查询且长度绝对固定否则别碰CHAR。VARCHAR(n)是可变长度。n是最大长度实际存多少占多少字节外加 1~2 字节记录长度。比如name VARCHAR(100)存Alice占 516 字节存Alexander the Great19 字符占 19120 字节。关键点在于n不是越大越好。MySQL 5.7 对VARCHAR的n有严格限制单行总长度不能超 65535 字节。如果定义VARCHAR(20000)再加几个字段很容易超限报错。更隐蔽的问题是排序和临时表ORDER BY name时MySQL 会把name全字段加载到内存排序VARCHAR(255)比VARCHAR(100)多占 155 字节内存10 万行排序就多用 15MB 内存可能触发磁盘临时表速度暴跌 5 倍。我们优化一个报表查询把product_name VARCHAR(255)改成VARCHAR(120)内存占用从 2.1GB 降到 1.3GB查询从 47 秒降到 8 秒。TEXT和CLOB是大文本专用。TEXT在 MySQL 中最大 64KBPostgreSQL 中无硬限制实际受toast表限制SQL Server 中VARCHAR(MAX)最大 2GB。它们不参与内存排序数据库会用磁盘临时表所以ORDER BY description会极慢。正确做法是大文本只用于存储查询时用SUBSTRING(description, 1, 200)截取摘要或单独建全文索引。我们有个日志表log_content TEXT原本WHERE log_content LIKE %error%全表扫描 2 分钟加FULLTEXT(log_content)后降到 0.3 秒。Unicode 支持是另一道坎。VARCHAR默认是数据库字符集如 MySQL 的utf8mb4但NVARCHARSQL Server和NCHAR显式声明 Unicode。区别在于VARCHAR存中文需要utf8mb4字符集支持 4 字节 UTF-8而NVARCHAR强制用 UCS-22 字节或 UTF-16可能 4 字节兼容性更好但存储翻倍。我们对接一个日本客户系统他们用NVARCHAR存U20BB7需 4 字节 UTF-8MySQLVARCHAR必须设utf8mb4否则存成?。结论如果业务涉及多语言尤其中日韩、emojiMySQL 用utf8mb4 VARCHARSQL Server 用NVARCHARPostgreSQL 用TEXT默认 UTF-8。2.3 日期时间类型时区不是可选项而是必答题日期类型选错轻则报表时间错 8 小时重则订单丢失、风控失效。核心误区是以为DATETIME和TIMESTAMP只是“存得更多”或“存得更少”。DATE只存日期YYYY-MM-DDTIME只存时间HH:MM:SSDATETIME存日期时间YYYY-MM-DD HH:MM:SSTIMESTAMP也存日期时间但本质是时间戳Unix Epoch 毫秒数。关键差异在时区处理DATETIME是“字面量”存什么就是什么。INSERT INTO t VALUES (2024-03-15 10:00:00)无论服务器时区是 UTC8 还是 UTC-5查出来永远是2024-03-15 10:00:00TIMESTAMP是“相对值”存入时会转成 UTC 时间戳查询时再转回当前会话时区。SET time_zone00:00; INSERT INTO t VALUES (2024-03-15 10:00:00)存的是 UTC 时间SET time_zone08:00; SELECT * FROM t查出来是2024-03-15 18:00:00。这导致经典问题一个部署在新加坡UTC8的系统用DATETIME存用户注册时间运维半夜切到美国西海岸UTC-7服务器所有历史时间显示错 15 小时。而用TIMESTAMP只要会话时区设对显示永远正确。但TIMESTAMP有范围限制MySQL 是 1970~2038 年DATETIME是 1000~9999 年。所以原则是记录事件发生时间如订单创建、日志时间用TIMESTAMP或 PostgreSQL 的TIMESTAMPTZ记录固定日期如生日、合同到期日用DATE记录纯时间如营业时间用TIME。TIMESTAMPTZPostgreSQL和DATETIMEOFFSETSQL Server是终极方案它们把时区信息和时间一起存。2024-03-15 10:00:0008存进去查出来还是带08。我们做跨境支付系统时强制所有时间字段用TIMESTAMPTZ应用层 Java 用OffsetDateTime接收彻底规避时区混乱。MySQL 8.0 的TIMESTAMP已支持时区但语法不如 PostgreSQL 直观。2.4 特殊类型JSON、UUID、数组——不是炫技而是解决真实瓶颈特殊类型常被当成“高级功能”其实它们是为了解决传统关系模型的硬伤。关键看是否满足结构不确定、嵌套深、查询频率低但写入频繁。JSON类型MySQL 5.7/PostgreSQL/SQL Server 2016本质是校验过的字符串但提供-、-操作符快速提取字段。比如用户配置表CREATE TABLE user_settings ( id INT PRIMARY KEY, config JSON ); INSERT INTO user_settings VALUES (1, {theme:dark,notifications:{email:true,sms:false}}); SELECT config-$.theme AS theme FROM user_settings; -- 返回 dark优势是不用为每个配置项建列新增push_token字段无需ALTER TABLE劣势是无法为 JSON 内部字段建索引MySQL 5.7 支持生成列索引但复杂。我们曾用JSON存用户设备信息iOS/Android/版本号但后来发现WHERE config-$.os iOS全表扫描太慢最终拆成os VARCHAR(20), os_version VARCHAR(20)两列查询提速 20 倍。所以JSON适用场景是配置项、日志详情、第三方 API 原始响应体——这些字段你基本不按它查只按 ID 查后解析。UUID通用唯一标识符解决自增 ID 的三大痛点安全暴露id100001让爬虫知道这是第 10 万零一个用户分库分表自增 ID 在不同库可能重复离线生成APP 端可生成 UUID 后异步提交不依赖数据库。但代价是UUID占 36 字符字符串或 16 字节二进制比BIGINT8 字节大一倍B-Tree 索引更臃肿。MySQL 8.0 支持UUID_TO_BIN()将35a2b5e8-1f9c-4a2d-8e1a-3c5b7d9e1f2a转成 16 字节二进制索引效率接近BIGINT。我们所有新微服务都用BINARY(16)存 UUIDSELECT * FROM orders WHERE id UUID_TO_BIN(xxx)性能损失不到 5%。ARRAYPostgreSQL 专属是真正的杀手锏。比如权限系统用户有多个角色传统方案是user_roles关联表查一个用户所有角色要JOIN。用ARRAYCREATE TABLE users ( id SERIAL PRIMARY KEY, name VARCHAR(100), roles TEXT[] DEFAULT ARRAY[user]::TEXT[] ); INSERT INTO users (name, roles) VALUES (Alice, ARRAY[admin,editor]); SELECT * FROM users WHERE admin ANY(roles); -- 直接查无需 JOINANY操作符让数组查询像普通字段一样快。我们用它重构了 SaaS 系统的租户权限QPS 从 1200 提升到 3500。但注意ARRAY不是万能的更新单个元素要UPDATE ... SET roles array_replace(roles, old, new)比关联表UPDATE user_roles SET rolenew WHERE user_id1 AND roleold复杂。所以原则是读多写少、元素数量稳定 100、不需对单个元素建索引的场景用 ARRAY。3. 实操指南从建表到调优手把手构建健壮数据模型3.1 建表黄金七步法拒绝拍脑袋定类型我带团队写建表语句严格执行七步法杜绝“先建再改”的返工。每一步都有明确检查点下面用电商订单表orders为例演示第一步明确业务实体与核心属性订单实体包含订单号、用户 ID、商品列表、总金额、状态、创建时间、支付时间。注意“商品列表”是嵌套结构不是原子字段。第二步逐字段分析数据特征order_no全局唯一长度 20 字符如ORD2024031500001非数字不可为空user_id关联用户表整数范围预估 1~5 亿需索引items商品列表JSON 格式含商品 ID、数量、单价不用于查询条件total_amount金额精确到分范围 0.01~999999.99status状态码枚举值pending/paid/shipped/cancelled共 4 种created_at订单创建时间需精确到秒跨时区paid_at支付时间可能为空未支付订单。第三步匹配数据类型并标注理由字段类型理由order_noVARCHAR(20)非数字前缀长度固定CHAR(20)浪费空间user_idBIGINT用户量超 21 亿INT不够BIGINT索引稍大但安全itemsJSON结构动态不按内部字段查JSON省去关联表total_amountDECIMAL(10,2)金钱必须精确10位支持 99999999.99statusVARCHAR(20)MySQL 不支持ENUM在线 DDL锁表VARCHAR更灵活若用 PostgreSQL 可选ENUMcreated_atTIMESTAMP WITH TIME ZONE(PostgreSQL) /DATETIME(MySQL)PostgreSQL 用TIMESTAMPTZ自动时区转换MySQL 用DATETIME配合应用层统一时区paid_atTIMESTAMP WITH TIME ZONE/DATETIME同上允许为空第四步设置约束与默认值-- PostgreSQL 示例 CREATE TABLE orders ( id SERIAL PRIMARY KEY, order_no VARCHAR(20) NOT NULL UNIQUE, user_id BIGINT NOT NULL REFERENCES users(id), items JSON NOT NULL, total_amount DECIMAL(10,2) NOT NULL CHECK (total_amount 0), status VARCHAR(20) NOT NULL DEFAULT pending, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), paid_at TIMESTAMPTZ NULL, -- 状态检查约束 CONSTRAINT valid_status CHECK (status IN (pending,paid,shipped,cancelled)) );第五步评估索引需求必建索引user_id用户查订单、created_at按时间查、status查待发货订单组合索引(status, created_at)加速WHERE statusshipped ORDER BY created_at DESC避免索引itemsJSON 字段不建普通索引。第六步估算存储与性能按 1 亿订单计算order_no VARCHAR(20)平均 18 字符 × 1 亿 1.8GBuser_id BIGINT8 字节 × 1 亿 0.8GBitems JSON平均 500 字符 × 1 亿 5GBtotal_amount DECIMAL(10,2)5 字节 × 1 亿 0.5GB总计约 8.1GB加上索引约 12GB。确认服务器磁盘充足。第七步编写初始化脚本并验证-- 插入测试数据 INSERT INTO orders (order_no, user_id, items, total_amount, status, paid_at) VALUES (ORD2024031500001, 1001, {items:[{id:101,qty:2,price:99.99}]}, 199.98, paid, NOW()), (ORD2024031500002, 1002, {items:[{id:102,qty:1,price:299.99}]}, 299.99, pending, NULL); -- 验证查询 SELECT order_no, total_amount, status FROM orders WHERE user_id 1001; -- 检查 JSON 提取 SELECT order_no, items-$.items[0].id AS item_id FROM orders;注意MySQL 8.0 的JSON_EXTRACT(items, $.items[0].id)语法更标准但 PostgreSQL 用-更简洁。跨库时建议封装 DAO 层屏蔽差异。3.2 跨数据库迁移实战从 MySQL 到 PostgreSQL 的避坑清单我们曾将一个 500GB 的 MySQL 电商库迁到 PostgreSQL耗时 72 小时其中 60 小时花在数据类型适配。以下是血泪总结的迁移 checklist1. 整数类型映射TINYINT→SMALLINTMySQL 的TINYINT是 1 字节PostgreSQLSMALLINT是 2 字节但TINYINT无符号范围 0~255SMALLINT有符号 -32768~32767完全覆盖INT→INTEGERBIGINT→BIGINTAUTO_INCREMENT→SERIALSERIAL是INTEGERSEQUENCE需手动创建序列或用GENERATED ALWAYS AS IDENTITYPG 10。2. 字符串类型映射VARCHAR(n)→VARCHAR(n)完全兼容TEXT→TEXT兼容但 PostgreSQLTEXT无长度限制MySQLTEXT最大 64KBLONGTEXT→TEXTPostgreSQLTEXT支持超大文本ENUM→VARCHAR(n)或自定义ENUMPostgreSQL 支持CREATE TYPE status AS ENUM (pending,paid);但需提前创建。3. 日期时间类型映射DATETIME→TIMESTAMP WITHOUT TIME ZONE若 MySQL 服务器时区固定可忽略时区TIMESTAMP→TIMESTAMP WITH TIME ZONE推荐自动处理时区DATE→DATE完全兼容TIME→TIME WITHOUT TIME ZONE兼容。4. 布尔类型映射TINYINT(1)→BOOLEANPostgreSQL 原生BOOLEAN0→false1→true迁移脚本需处理UPDATE orders SET status CASE WHEN status 0 THEN false ELSE true END先转字符串再转布尔。5. JSON 类型映射JSON→JSONBPostgreSQL 的JSONB是二进制格式查询更快支持索引迁移时用to_jsonb()函数INSERT INTO pg_orders SELECT id, to_jsonb(items) FROM mysql_orders。6. 索引与约束迁移MySQL 的FULLTEXT索引 → PostgreSQL 的GIN索引 to_tsvector()UNIQUE约束直接迁移FOREIGN KEY需确保引用表已存在。7. 验证脚本必须执行-- 检查行数一致性 SELECT (SELECT COUNT(*) FROM mysql.orders) AS mysql_count, (SELECT COUNT(*) FROM pg.orders) AS pg_count; -- 检查金额精度抽样 1000 行 SELECT o1.total_amount AS mysql_amt, o2.total_amount AS pg_amt FROM mysql.orders o1 JOIN pg.orders o2 ON o1.order_no o2.order_no WHERE o1.total_amount ! o2.total_amount LIMIT 10;实操心得迁移前用pt-table-checksumPercona Toolkit校验 MySQL 主从一致性迁移中用pgloader工具自动处理类型转换比手写mysqldumpsed替换可靠 10 倍迁移后重点压测JOIN查询PostgreSQL 的HASH JOIN和MERGE JOIN策略与 MySQL 不同可能需调整work_mem参数。3.3 性能调优实录从慢查询到毫秒响应的四次迭代一个订单搜索接口初始 SQLSELECT * FROM orders WHERE status shipped AND created_at BETWEEN 2024-01-01 AND 2024-03-01 ORDER BY created_at DESC LIMIT 100;线上平均 8.2 秒。我们分四步优化第一次索引诊断耗时 0.5 小时执行EXPLAIN ANALYZESeq Scan on orders (cost0.00..123456.78 rows12345 width200) (actual time0.123..8200.456 rows100 loops1) Filter: ((status shipped::text) AND (created_at 2024-01-01 00:00:00::timestamp without time zone) AND (created_at 2024-03-01 00:00:00::timestamp without time zone)) Rows Removed by Filter: 999900全表扫描原因status和created_at无组合索引。建索引CREATE INDEX idx_orders_status_created ON orders(status, created_at);查询降至 1.3 秒。但EXPLAIN显示Index Scan using idx_orders_status_created仍扫描 12 万行才找到 100 条。第二次覆盖索引耗时 1 小时SELECT *需回表取所有字段而索引只存status和created_at。建覆盖索引CREATE INDEX idx_orders_covering ON orders(status, created_at) INCLUDE (order_no, user_id, total_amount, paid_at);PostgreSQL 11 支持INCLUDEMySQL 用联合索引INDEX(status, created_at, order_no, user_id, ...)查询降至 0.4 秒。EXPLAIN显示Index Only Scan无需回表。第三次分区裁剪耗时 3 小时订单表已达 2 亿行created_at范围查询仍慢。按月分区-- PostgreSQL 分区表 CREATE TABLE orders_part (LIKE orders INCLUDING ALL) PARTITION BY RANGE (created_at); CREATE TABLE orders_202401 PARTITION OF orders_part FOR VALUES FROM (2024-01-01) TO (2024-02-01); CREATE TABLE orders_202402 PARTITION OF orders_part FOR VALUES FROM (2024-02-01) TO (2024-03-01); -- 重命名原表交换数据 ALTER TABLE orders RENAME TO orders_old; ALTER TABLE orders_part RENAME TO orders;查询降至 0.08 秒。EXPLAIN显示只扫描orders_202401和orders_202402两个分区。第四次物化视图预计算耗时 2 小时业务要求实时查“各状态订单数”SELECT status, COUNT(*) FROM orders GROUP BY status每次扫全表。建物化视图CREATE MATERIALIZED VIEW orders_status_count AS SELECT status, COUNT(*) as cnt FROM orders GROUP BY status; REFRESH MATERIALIZED VIEW CONCURRENTLY orders_status_count; -- 并发刷新不锁表查询SELECT * FROM orders_status_count降至 0.002 秒。最终效果接口 P99 从 8200ms 降至 12ms服务器 CPU 使用率下降 35%。关键经验索引不是越多越好。我们曾为orders表建了 12 个索引导致INSERT速度从 5000 QPS 降到 800 QPS。现在坚持“一个查询一个索引”用pg_stat_statements监控慢查询只对 QPS 10 且耗时 100ms 的查询建索引。4. 常见问题与排查技巧实录生产环境踩坑大全4.1 数据截断为什么“Michael”变成了“Micha”这是新手最常犯的
