SQL数据类型详解：索引失效与跨库迁移避坑指南

简介这是一份面向数据库初学者与SQL开发人员的SQL数据类型系统梳理资料聚焦SQL Server中各类数据类型的定义、取值范围与适用场景帮助读者在建模与建表时准确选型、避免存储与精度问题。资源包共1个PDF文件约71KB内容按二进制、字符、Unicode、日期时间、数字、货币及特殊数据类型等模块展开并延伸至用户自定义数据类型结构紧凑便于速查。其中对Binary与Varbinary的定长变长差异、Char与Varchar的8KB边界、Nchar与Nvarchar的存储翻倍特性、Datetime与Smalldatetime的日期区间、Int与Smallint与Tinyint的数值范围以及Decimal、Float、Money、Timestamp、Bit、Uniqueidentifier等均有具体说明还涉及Set DateFormat日期格式设置。目前已有614人学习适合作为日常开发与面试复习的参考手册。1. SQL 数据类型详解为什么你写的索引没生效问题可能出在类型上很多人第一次认真看 SQL 数据类型是在一次线上事故之后。我印象最深的一次是订单表按user_id查一条记录明明建了索引EXPLAIN却显示全表扫描。查了半天发现user_id在表里是VARCHAR而应用传进来的参数是整型数据库做了一次隐式类型转换索引直接失效。这类问题不是玄学是数据类型没吃透。SQL 数据类型详解这件事表面看是背一张对照表实际解决的是三类问题存储该选什么类型才不浪费空间、查询时类型不匹配为什么让索引失效、跨库迁移和 ETL 时类型怎么映射才不丢精度。它适合后端开发、数据开发和做数据清洗的工程师——只要你写过CREATE TABLE或者被慢 SQL 优化折磨过这篇就值得往下看。下面按「选型 → 落地 → 踩坑 → 进阶」的顺序讲透。2. 整数、字符串、时间三大类的选型逻辑与建表落地2.1 整数类型从 TINYINT 到 BIGINT 到底怎么选整数类型的选择核心就一句话按业务真实取值范围选别一律INT或BIGINT。常见做法是状态、类型这种枚举值用TINYINT1 字节-128~127 或 0~255用户 ID、订单 ID 这种会持续增长的用BIGINT8 字节中间量级的计数用INT4 字节。这里有个容易被忽略的点INT UNSIGNED和INT的取值范围不同。无符号INT是 0~4294967295有符号是 -2147483648~2147483647。如果你的自增主键永远为正用UNSIGNED能多出一倍空间但要注意跨库迁移时某些数据库对无符号支持不一致可能翻车。-- 建一张用户表演示整数类型的合理选型 CREATE TABLE t_user ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键持续增长用 BIGINT, status TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 状态枚举 0-255, age TINYINT UNSIGNED DEFAULT NULL COMMENT 年龄 0-255 足够, login_count INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 登录次数中等量级, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;逻辑说明id用BIGINT UNSIGNED是因为自增主键迟早会突破INT上限提前留余量比后期改表代价小得多。status和age用TINYINT UNSIGNED一个字节搞定比INT省 3 字节行数上亿时差距明显。login_count用INT UNSIGNED因为登录次数不太可能超过 42 亿。参数说明AUTO_INCREMENT只对整数类型生效UNSIGNED会改变取值范围迁移到不支持无符号的库时要评估DEFAULT给默认值能避免NULL带来的三值逻辑问题。2.2 字符串类型CHAR、VARCHAR、TEXT 的边界在哪字符串选型最常见的错误是无脑用VARCHAR(255)。CHAR是定长适合长度固定的场景比如 MD5 值32 位、国家代码2 位VARCHAR是变长适合长度不固定的名称、地址TEXT适合大段文本但它不能有默认值且排序时可能用到磁盘临时表。一个关键细节VARCHAR(n)里的 n 是字符数不是字节数。在utf8mb4下一个汉字占 3~4 字节所以VARCHAR(255)最大可能占 1020 字节。索引长度限制和这个直接相关——MySQL InnoDB 单列索引前缀默认最多 767 字节老版本或 3072 字节VARCHAR(255)在utf8mb4下建完整索引可能超限。-- 字符串类型选型演示 CREATE TABLE t_product ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, sku_code CHAR(32) NOT NULL COMMENT 固定长度编码用 CHAR, product_name VARCHAR(128) NOT NULL COMMENT 名称变长用 VARCHAR, description TEXT COMMENT 详情大文本用 TEXT, PRIMARY KEY (id), KEY idx_sku (sku_code) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;逻辑说明sku_code长度固定用CHAR(32)比VARCHAR少一个长度字节且定长检索略快。product_name用VARCHAR(128)128 个字符对商品名足够别动不动 255。description用TEXT因为详情可能几千字放VARCHAR会撑大行长度。参数说明CHAR会去掉尾部空格存密码哈希这类不能丢空格的场景要小心VARCHAR要按业务上限设设太大浪费内存临时表TEXT不能建普通索引需要索引时得用前缀索引或额外字段。2.3 时间类型DATETIME、TIMESTAMP、DATE 怎么分工时间类型选错最典型的后果是时区问题。TIMESTAMP存储时转 UTC读取时转当前时区范围只到 2038 年DATETIME存字面值范围到 9999 年但不带时区信息。常见做法是需要跨时区展示的用TIMESTAMP只记录业务本地时间的用DATETIME只关心日期的用DATE。-- 时间类型选型演示 CREATE TABLE t_order ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间业务本地时间, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间自动维护, order_date DATE NOT NULL COMMENT 下单日期只到天, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;逻辑说明created_at用DATETIME记录业务时间不受时区转换影响。updated_at用TIMESTAMP配合ON UPDATE CURRENT_TIMESTAMP让数据库自动维护更新时间省去应用层代码。order_date用DATE按天统计时不用做函数转换能直接走索引。参数说明CURRENT_TIMESTAMP是默认值表达式ON UPDATE只在行真正变化时触发TIMESTAMP的 2038 问题在长期系统里要提前规划必要时全用DATETIME。3. 类型转换、隐式转换与索引失效的排查实战3.1 隐式类型转换为什么让索引失效这是慢 SQL 优化里最高频的坑之一。当查询条件里的类型和列类型不一致时数据库会做隐式转换。规则是字符串列和数字比较时字符串被转成数字。一旦列被函数包裹或发生转换索引就用不上了。-- 假设 user_id 是 VARCHAR 类型 -- 下面这条会全表扫描因为 123 被转成数字列也参与转换 SELECT * FROM t_user WHERE user_id 123; -- 正确写法参数类型和列类型一致 SELECT * FROM t_user WHERE user_id 123;逻辑说明第一条语句里user_id是字符串列123是数字MySQL 会把user_id转成数字再比较相当于对列做了函数操作索引失效。第二条保持类型一致索引正常生效。参数说明排查时用EXPLAIN看type列出现ALL就是全表扫描key列为NULL说明没走索引。应用层传参时统一类型别让框架自动转换。3.2 显式转换函数 CAST 和 CONVERT 的用法有时候确实需要转换比如把字符串日期转成日期比较。这时用显式转换并且尽量把函数用在常量侧而不是列侧。-- 把字符串转日期函数用在常量侧列侧保持原样 SELECT * FROM t_order WHERE created_at CAST(2024-01-01 AS DATETIME); -- CONVERT 写法适合跨库兼容 SELECT * FROM t_order WHERE created_at CONVERT(2024-01-01, DATETIME);逻辑说明CAST和CONVERT都能做类型转换CONVERT还支持字符集转换。关键是把转换放在常量上这样列created_at不被包裹索引依然可用。参数说明CAST(expr AS type)里 type 支持DATETIME、DATE、SIGNED、CHAR等CONVERT(expr, type)或CONVERT(expr USING charset)。跨库迁移时优先用标准CAST。3.3 用 EXPLAIN 定位类型相关性能问题排查类型导致的性能问题EXPLAIN是第一工具。重点看三列type访问类型、key实际用的索引、rows预估扫描行数。EXPLAIN SELECT * FROM t_user WHERE user_id 123; EXPLAIN SELECT * FROM t_user WHERE user_id 123;逻辑说明对比两条EXPLAIN结果第一条type大概率是ALLkey为NULL第二条type是ref或constkey显示索引名。这就是类型一致性的价值。参数说明type从好到差是system const eq_ref ref range index ALLrows越小越好如果Extra出现Using filesort或Using temporary也要结合类型一起看。4. 跨库迁移与数据清洗中的类型映射避坑4.1 不同数据库类型映射对照跨库迁移时类型映射是最容易丢精度的地方。下面这张表是常见映射关系迁移前务必逐列核对。语义MySQLPostgreSQLOracleSQL Server小整数TINYINTSMALLINTNUMBER(3)TINYINT整数INTINTEGERNUMBER(10)INT长整数BIGINTBIGINTNUMBER(19)BIGINT变长字符串VARCHARVARCHARVARCHAR2VARCHAR大文本TEXTTEXTCLOBNVARCHAR(MAX)时间戳DATETIMETIMESTAMPTIMESTAMPDATETIME2布尔TINYINT(1)BOOLEANNUMBER(1)BIT逻辑说明MySQL 没有原生布尔常用TINYINT(1)模拟Oracle 的NUMBER要指定精度否则可能按浮点处理SQL Server 的DATETIME精度只有 3.33 毫秒要更高精度用DATETIME2。参数说明迁移前用information_schema.columns导出源库类型清单和目标库逐列比对字符串长度要按目标库字符集重新计算utf8mb4下长度可能翻倍。4.2 迁移时精度丢失的三种典型场景第一种是浮点转定点。源库用FLOAT存金额目标库用DECIMAL转换时可能出现0.1 0.2 ! 0.3的精度问题。金额一律用DECIMAL(m,2)别用浮点。第二种是时间精度截断。源库TIMESTAMP(6)带微秒目标库DATETIME只到秒微秒直接丢。迁移前确认业务是否依赖微秒。第三种是字符集不兼容。源库latin1存了中文迁到utf8mb4时乱码。迁移前统一字符集用CONVERT ... USING utf8mb4做转换。-- 迁移前检查源库列类型和字符集 SELECT column_name, data_type, character_maximum_length, character_set_name FROM information_schema.columns WHERE table_schema source_db AND table_name t_order; -- 金额字段统一用 DECIMAL ALTER TABLE t_order MODIFY amount DECIMAL(12,2) NOT NULL DEFAULT 0.00;逻辑说明第一条查源库列定义确认字符集和长度。第二条把金额改成DECIMAL(12,2)12 位总长度、2 位小数能存到百亿级精度不丢。参数说明DECIMAL(m,d)里 m 是总位数d 是小数位character_set_name为NULL表示非字符类型迁移脚本里对每个字符列都要显式指定目标字符集。4.3 数据清洗中的类型统一策略数据清洗时源数据往往类型混乱比如日期列里混了2024/01/01、2024-01-01、20240101三种格式。策略是先统一成标准格式再转目标类型。-- 清洗日期列先替换分隔符再转 DATE UPDATE t_raw SET order_date STR_TO_DATE( REPLACE(REPLACE(order_date_str, /, -), ., -), %Y-%m-%d ) WHERE order_date_str IS NOT NULL;逻辑说明REPLACE把/和.统一成-再用STR_TO_DATE按%Y-%m-%d解析。清洗后列类型改成DATE后续查询才能走索引。参数说明STR_TO_DATE的格式串要和数据实际格式匹配不匹配返回NULL清洗前先备份原列用WHERE限定非空避免把NULL转成0000-00-00。5. 类型相关的常见问题与排查清单5.1 插入报错「Data too long for column」怎么定位现象插入或更新时报Data too long for column xxx业务中断。原因目标列长度不够常见于VARCHAR设太短或者字符集从utf8换成utf8mb4后字节数变大。解决先查列定义SHOW COLUMNS FROM t_table LIKE xxx确认Type里的长度再查实际数据长度SELECT MAX(CHAR_LENGTH(col)) FROM t_table按业务上限扩列别直接改TEXT扩到合理长度即可。5.2 金额字段用 FLOAT 导致对账差几分钱现象订单金额和对账系统差 0.01 元反复核对找不到原因。原因FLOAT和DOUBLE是近似值存储累加时误差累积。解决金额一律用DECIMAL(m,2)Java 侧用BigDecimalPython 侧用decimal.Decimal别用float。已存在的FLOAT列用ALTER TABLE ... MODIFY amount DECIMAL(12,2)迁移迁移前备份。5.3 时间字段存了 0000-00-00 导致查询异常现象查询时间范围时结果不对或者应用解析时间报错。原因老版本 MySQL 允许0000-00-00作为默认值或者STR_TO_DATE解析失败返回零值。解决先查SELECT COUNT(*) FROM t WHERE created_at 0000-00-00把零值更新为NULL或合理默认值建表时设NO_ZERO_DATE模式从源头禁止。5.4 隐式转换让唯一索引失效导致重复数据现象明明建了唯一索引还是插入了重复数据。原因唯一索引列是VARCHAR插入时传了数字隐式转换后123和123被当成不同值或者转换规则导致判断异常。解决应用层统一传字符串检查已有数据SELECT col, COUNT(*) FROM t GROUP BY col HAVING COUNT(*) 1必要时用CAST统一后再建唯一索引。5.5 跨库迁移后字符串尾部空格丢失现象迁移后密码哈希校验失败或者编码比对不一致。原因源库用CHARCHAR会去掉尾部空格目标库用VARCHAR空格被保留导致值不一致。解决迁移前确认源列是CHAR还是VARCHAR对不能丢空格的字段改用VARCHAR或BINARY迁移后用SELECT LENGTH(col) - CHAR_LENGTH(col)检查尾部空格差异。6. 用类型信息反推表结构设计一个可复用的检查脚本前面讲了选型和踩坑最后落到一个我平时常用的技巧写一个脚本把库里所有列的类型、长度、是否可空、索引情况导出来做一次体检。这个习惯帮我提前发现过好几次隐患比如某张表金额列是FLOAT、某列VARCHAR(255)在utf8mb4下索引超限。-- 表结构体检导出列类型、长度、可空、索引情况 SELECT t.table_name, c.column_name, c.data_type, c.character_maximum_length, c.numeric_precision, c.numeric_scale, c.is_nullable, c.column_default, CASE WHEN s.index_name IS NOT NULL THEN YES ELSE NO END AS has_index FROM information_schema.tables t JOIN information_schema.columns c ON t.table_name c.table_name AND t.table_schema c.table_schema LEFT JOIN information_schema.statistics s ON s.table_name c.table_name AND s.column_name c.column_name AND s.table_schema c.table_schema WHERE t.table_schema your_db ORDER BY t.table_name, c.ordinal_position;逻辑说明这条查询把列定义和索引信息拼在一起一眼能看出哪些列类型可疑、哪些列没索引。numeric_precision和numeric_scale对DECIMAL列特别有用能确认金额精度。has_index帮你快速定位该建索引却没建的列。参数说明table_schema换成你的库名statistics表里同一列可能有多个索引结果会重复需要去重时加DISTINCTcharacter_maximum_length对非字符类型为NULL判断时注意。拿到这份清单后我一般按三条规则过一遍金额列不是DECIMAL的标红VARCHAR长度超过 255 且建了索引的标黄时间列是TIMESTAMP且业务要跨 2038 年的标黄。这套检查不复杂但比出事后再改表省心得多。血泪经验是类型问题从来不是「建表时随便选选」的小事它会在数据量上来、查询变复杂、跨库迁移时集中爆发。我现在建表前会先想清楚三件事这列的取值范围、会不会参与索引、要不要跨库。想清楚再动手比事后加后悔药强。希望帮到你。本文还有配套的精品资源点击获取