日常写 SQL 的时候“类型对不上”是开发最容易忽略、却又最经常出事的问题。接口表吐出来的字段是字符串业务表要存数字日志表的时间列是 VARCHAR报表 SQL 却要按日期统计这些场景全靠 MySQL 的 convert 函数、cast 这类类型转换函数来兜底。字符串转数字、字符串转日期这两类操作几乎每个项目都会遇到但真正用对的人并不多。这篇笔记来自我实际维护两个线上库的经验把 convert 的用法、隐式转换的坑、索引失效的排查过程完整走一遍适合正在写报表 SQL、做数据迁移或者被“类型不匹配”折腾过的开发者。这里插一句我平时写 SQL 的第一原则是“能不改类型就不改类型”。这个函数不是给你拿来绕开表结构设计问题的而是用来处理边界数据的。理解了这个前提再看下面的内容才不会跑偏。1. 先搞清楚MySQL 里哪些场景逼你手动做类型转换1.1 数据入库与查询条件的“类型错位”最常见的场景之一是上游接口给你传了一个全是字符串的临时表CREATE TEMPORARY TABLE tmp_import ( order_no VARCHAR(50), amount VARCHAR(20), pay_time VARCHAR(30) );下游业务表的定义里amount是 DECIMAL(10,2)pay_time是 DATETIME。你要把临时表的数据灌进业务表就必须在 INSERT ... SELECT 的过程里做转换INSERT INTO t_order(amount, pay_time) SELECT CONVERT(tmp.amount, DECIMAL(10,2)), CONVERT(tmp.pay_time, DATETIME) FROM tmp_import tmp;这种场景在数据迁移、接口对接、ETL 清洗里非常常见。你不需要在程序里写一堆 Java/Python 转换逻辑SQL 端一次搞定性能还比逐条循环好得多。另一种场景是查询条件类型不匹配。比如用户从前端传过来一个keyword你拿它去匹配订单号。这个字段在表里是 VARCHAR代码里却是数字类型直接拼进 SQL 就出问题。这种时候有人习惯在 SQL 里对列做转换但实际上绝大多数情况下更优的做法是转换传入的值而不是转换列本身。这个区别在后面会有专门一节讲因为它直接决定索引能不能用上。1.2 隐式转换MySQL 替你默默“好心办坏事”很多人不知道即使你不写任何转换函数MySQL 在发现两边类型不一致时也会自己决定把谁转成谁。这个行为叫隐式转换。比如下面这条 SQLSELECT * FROM t_user WHERE user_id 12345;user_id是 INT右边是字符串。MySQL 会尝试把右侧的字符串转成数字再和左侧比较。因为右侧是常量字符串转成数字后仍可能命中索引隐患还不明显。但反过来就很危险SELECT * FROM t_order WHERE order_no 12345;这里order_no是 VARCHAR 列右侧是数字常量。MySQL 会把每一行的order_no列转成数字再比较于是这一列上的索引基本就废了。我在生产环境见过多次全表扫描的慢查询EXPLAIN 里 type 是 ALL一查就是这种写法。修复方式也很简单把右侧常量写成字符串SELECT * FROM t_order WHERE order_no 12345;这条经验值得刻在工位上字符串列和数字常量比较优先把常量包上引号而不是指望 MySQL 自己优化。1.3 从 SQL Server 迁移过来的朋友最容易踩的语法坑标题热搜里同时有“sqlserver 字符串转数字”和“mysql convert”说明很多人是在做数据库迁移的时候搜到这的。我提醒一句SQL Server 的 CONVERT 和 MySQL 的 CONVERT参数顺序是反的而且能力也不一样。SQL Server 的写法是-- SQL Server SELECT CONVERT(INT, order_no) FROM t_order; SELECT CONVERT(VARCHAR(10), GETDATE(), 120);MySQL 的写法是-- MySQL SELECT CONVERT(order_no, SIGNED) FROM t_order;第一个参数是表达式第二个参数才是目标类型。而且 MySQL 的 CONVERT 没有第三个“样式”参数你想把日期格式化成2023-06-15 10:30:00这种风格得用 DATE_FORMAT不能指望 CONVERT 带 style。从迁移项目里看到的报错基本都是CONVERT(INT, xxx)这种把 SQL Server 习惯带进来的MySQL 会直接报语法错。遇到这种问题先检查是不是两边的函数用法混了。2. CONVERT() 语法速览与和 CAST() 的选型差异2.1 CONVERT 的两种形态MySQL 的 CONVERT 函数实际上有两套用法。第一套是类型转换CONVERT(expr, type)expr可以是列、常量、表达式type是目标类型。第二套是字符集转换CONVERT(expr USING charset_name)这两套用法共用同一个函数名但语义完全不同。如果你在 Charset 转换里写成CONVERT(expr, charset_name)这种带逗号的写法会直接报参数错误。很多新手在这里栽跟头因为只看了一部分文档就上手写。与 CONVERT 功能高度重叠的是 CASTCAST(expr AS type)在绝大多数类型转换场景里CAST 和 CONVERT 可以互换。区别主要在语法形式和字符集支持上。CAST 的标准性更好如果你是搞跨数据库开发的建议优先用 CAST如果只写 MySQL看团队习惯两种都行。2.2 CAST 与 CONVERT 可转换的类型对照MySQL 里可以转换的主要类型如下表目标类型示例说明BINARY[(N)]CONVERT(abc, BINARY)转二进制常用于二进制比较CHAR[(N)]CONVERT(123, CHAR)转字符串DATECONVERT(2023-06-15, DATE)转日期丢弃时间部分DATETIMECONVERT(2023-06-15 10:30:00, DATETIME)转日期时间DECIMAL[(M[,D])]CONVERT(123.456, DECIMAL(10,2))转定点数注意四舍五入JSONCONVERT({a:1}, JSON)转 JSONMySQL 5.7NCHAR[(N)]CONVERT(abc, NCHAR(10))按国家字符集转字符串SIGNED [INTEGER]CONVERT(-12, SIGNED)转有符号整数TIMECONVERT(10:30:00, TIME)转时间UNSIGNED [INTEGER]CONVERT(12, UNSIGNED)转无符号整数注意两点SIGNED和SIGNED INTEGER等价UNSIGNED同理DECIMAL 的第二个参数D是小数位省略时默认是 0容易把小数部分吃掉所以一定要显式写明。MySQL 8.0.17 之后CAST 还新增了对 FLOAT 和 DOUBLE 的支持SELECT CAST(3.14 AS DOUBLE);不过这种用法一般不如直接用 DECIMAL 稳妥。浮点数在计算和比较时会有精度问题业务数据建议优先 DECIMAL。2.3 CONVERT 独享的字符集转换能力CAST 做不了字符集转换这是 CONVERT 不可替代的一个点。SELECT CONVERT(article_title USING gbk) FROM t_article LIMIT 1;这句话把article_title从表里的默认字符集比如 utf8mb4转成 gbk 输出。在对接老系统、生成 GBK 编码的导出文件时这一句能省掉大量程序层转码工作。但这里有个坑是返回结果的显示效果还取决于客户端连接字符集。你转成了 gbk如果客户端连接用的是 utf8mb4展示出来可能就是乱码。我一般只在 INSERT 到另一张 GBK 表之前用这招让 MySQL 内部直接完成转码不把乱码问题带到应用层。3. 字符串转数字实战三种写法和一堆坑3.1 基础写法转整数、转 DECIMAL字符串转数字最常用的是这两种-- 转为有符号整数 SELECT CONVERT(-128, SIGNED); -- 结果-128 -- 转为无符号整数 SELECT CONVERT(128, UNSIGNED); -- 结果128 -- 转为定点数 SELECT CONVERT(123.456, DECIMAL(10,2)); -- 结果123.46DECIMAL 的转换默认走四舍五入不是截断。123.456转成DECIMAL(10,2)会得到123.46。如果你需要截断效果要自己配合其他函数处理比如先乘后取整或者用 FLOOR/CEILING 辅助。实际业务里接口给的金额字符串往往带货币符号或者空格比如100.50。这种字符串直接 CONVERT 会得到 0因为 MySQL 从开头解析时就碰到非数字字符了。你需要先清洗SELECT CONVERT(REPLACE(REPLACE(100.50, , ), ,, ), DECIMAL(10,2));这个经验在处理第三方支付对账文件时非常常见。总之CONVERT 只负责类型转换不负责数据清洗脏数据在传进来之前就要先处理干净。3.2 非数字字符串的行为差异5.7 的截断和 8.0 的严格模式字符串转数字有一个非常经典的坑就是“数字开头带尾巴”。在 MySQL 5.7 及早期版本里SELECT CONVERT(123abc, UNSIGNED); -- 结果123 SELECT CONVERT(abc123, UNSIGNED); -- 结果0也就是说5.7 会从头开始解析数字直到遇到非数字字符就停下后面的一概忽略。如果第一个字符就不是数字结果就是 0。这个行为让很多人在不知不觉中吞掉了数据异常。从 MySQL 8.0.17 开始官方对这类转换做了收紧。123abc这种字符串再转数字在某些服务器配置下会直接报错或者返回 0 并产生 warning。如果你在升级版本后突然发现批量导入脚本报错先检查是不是有这个原因。我的建议是永远不要依赖“截断前段数字”这个行为。所有转数字的字符串最好在程序里或 SQL 里先用正则等手段确认是合法数字。5.7 的时代还有侥幸心理8.0 之后就得彻底改掉这个习惯。3.3 隐式转换仍然常见的场景为什么还是建议显式转换日常排查慢查询时我见过太多 SELECT 里没有写任何转换函数却因为类型不匹配而慢的例子。最常见的就是 JOINSELECT * FROM t_order a JOIN t_user b ON a.user_id b.user_id;如果a.user_id是 BIGINTb.user_id是 VARCHARMySQL 在决定连接方式时会把被驱动表的字符串列隐式转成数字。b.user_id上的索引在这种情况下很容易失效执行计划变成全表扫描。这种场景不需要你写 CONVERT因为你根本没在 SQL 里写它问题反而更难发现。排查方法是在 EXPLAIN 的 Extra 列里看到Using where; Using join buffer或者 type 为 ALL 时逐字段核对参与连接列的数据类型。修复办法不是用 CONVERT 强行转换而是建议统一表结构把关联字段类型做成完全一致。如果暂时改不了表结构也要在 JOIN 时显式转换常量侧尽量保持被驱动表的列不被函数包裹。3.4 有空字符串、NULL 时的处理字符串转数字遇到NULL结果还是NULL这个没有争议。但有争议的是空字符串SELECT CONVERT(, SIGNED); -- 结果0在严格模式下可能直接报错在非严格模式下返回 0。这种“0”会污染统计结果。很多报表的金额汇总里莫名多出几个 0源头往往就是空字符串转数字。我在做清洗 SQL 时一般会先兜一层SELECT CASE WHEN trim(col) THEN 0 WHEN col IS NULL THEN 0 ELSE CONVERT(col, DECIMAL(10,2)) END AS amount FROM tmp_table;先把空值语义明确下来再去做金额转换。这种事看起来是小事真到月底对账差几分钱的时候排查成本能让人崩溃。4. 字符串转日期实战格式地狱的解法4.1 标准格式白名单CONVERT 能直接识别的格式MySQL 对日期字符串的识别比你想的要宽松一些。以下这些写法 CONVERT 都能直接识别SELECT CONVERT(2023-06-15, DATE); SELECT CONVERT(2023/06/15, DATE); SELECT CONVERT(2023.06.15, DATE); SELECT CONVERT(20230615, DATE); -- 以上几行结果都是 2023-06-15连接符用-、/、.都可以年份在前、月份在中间的基本格式MySQL 都能解析。带时间部分也支持SELECT CONVERT(2023-06-15 14:30:00, DATETIME); -- 结果2023-06-15 14:30:00 SELECT CONVERT(2023-06-15 14:30:00, DATE); -- 结果2023-06-15第二个查询值得注意转成 DATE 会自动丢弃时间部分只保留日期。如果你需要的是“这一天”用这个写法比先截断字符串再转换干净得多。但有一种格式 MySQL 不认就是日放在最前面的写法SELECT CONVERT(15/06/2023, DATE); -- 结果不是 2023-06-15可能是 NULL或报错取决于版本和模式很多从欧洲系统导出的数据都是dd/mm/yyyy格式这是字符串转日期最容易翻车的地方。4.2 非标准格式STR_TO_DATE 打头阵CONVERT 收尾碰到 CONVERT 直接搞不定的日期格式正确姿势是先用 STR_TO_DATE 做格式化解析再赋值给日期列。比如SELECT STR_TO_DATE(15/06/2023, %d/%m/%Y); -- 结果2023-06-15 SELECT STR_TO_DATE(2023-6-5, %Y-%c-%e); -- 结果2023-06-05STR_TO_DATE返回的结果本身就已经是 DATE 或 DATETIME 类型不需要再套一层 CONVERT。很多新人会写CONVERT(STR_TO_DATE(...), DATE)多此一举但问题不大、也不会报错只是冗余。这里的关键是搞清楚目标字符串到底长什么样再去匹配格式符。常用的几个格式符含义例子%Y四位年份2023%y两位年份23%m两位月份06%c月份可是一位或两位6%d两位日05%e日可是一位或两位5%H24 小时制小时14%i分钟30%s秒00我通常这样处理一批格式混乱的日期字符串先用 STR_TO_DATE 统一转成合法日期如果返回 NULL再看一眼是不是有额外空格或不可见字符用 REPLACE 清理后再试一次。批量数据里十几种日期格式混杂的情况我遇到过好几次老老实实靠 STR_TO_DATE 一列一列清洗比在程序里拆字符串可靠得多。4.3 日期范围查询中 CONVERT 与索引的博弈这是日期转换里最疼的问题。很多人统计某一天的订单时这样写SELECT COUNT(*) FROM t_order WHERE DATE(created_at) 2023-06-15;结果慢得离谱。原因是你对created_at列套了DATE()函数MySQL 没法直接走这个列上的索引只能把每行都提取出来算一遍。正确写法是用区间查询让索引有机会命中SELECT COUNT(*) FROM t_order WHERE created_at 2023-06-15 00:00:00 AND created_at 2023-06-16 00:00:00;如果一定要写转换函数也应该是转换常量侧SELECT COUNT(*) FROM t_order WHERE created_at CONVERT(2023-06-15, DATE);这里等号右边的 CONVERT 是常量表达式MySQL 会先算出结果再拿这个常量去索引列上比较不会损伤created_at的索引。这条规则同样适用于 CHAR、SIGNED 等所有类型转换。记住一句话转换常量不要转换列。5. 二进制、字符集和其他类型转换的高级玩法5.1 BINARY 转换与二进制比较字符串比较在 MySQL 默认排序规则下通常不区分大小写。如果业务需要区分常见办法是把两边都转成 BINARYSELECT CONVERT(abc ABC, UNSIGNED); -- 结果1默认排序规则下两者相等 SELECT CONVERT(abc, BINARY) CONVERT(ABC, BINARY); -- 结果0二进制比较下两者不等这种做法的原理是二进制比较直接按字节值判断字符的大小写映射就不起作用了。在实际项目里我更多用它来做用户名校验里的精确匹配或者判断编码是否完全一致。注意别把整列都转成 BINARY 然后建索引那种方案会让很大一部分范围查询失效得不偿失。BINARY 转换还有一个细节它按字节存储中文等多字节字符转完后长度与字符数不一致很容易在截断时切出半个汉字。真要处理字节多用 VARBINARY 而不是在查询里临时 CONVERT。5.2 USING charset 字符集转换的实际意义字符集转换最大的实际用途是处理历史库和异构系统对接。我之前做过一个老系统数据迁移源库把中文内容存成了 gbk目标表要按 utf8mb4 入库不能直接 INSERT否则全是问号。这时候可以这样把读取和写入分开搞-- 读出时转成 utf8mb4 SELECT CONVERT(name USING utf8mb4) FROM t_old; -- 或者写入时按目标字符集转 SET NAMES utf8mb4; INSERT INTO t_old_copy(name) SELECT CONVERT(name USING utf8mb4) FROM t_old;另一个常见场景是把多个来源的数据统一成一致的排序规则再参与 JOIN。不同字符集的列做等值比较MySQL 可能会走转换流程转换时用的默认排序规则不一定合理。你可以先把其中一边用 CONVERT 转成和目标库一致的字符集避免隐式转换带来的额外开销。这里要提醒一句字符集转换只解决“字节表示”问题不解决“乱码”问题。如果数据源本身就是乱码转来转去还是乱码得先把源头数据修好。5.3 SIGNED 与 UNSIGNED 转换的陷阱SIGNED 和 UNSIGNED 的转换最容易踩的坑是把负数转成无符号整数SELECT CONVERT(-1, UNSIGNED); -- 结果18446744073709551615没错-1转成 UNSIGNED 后会变成一个巨大的正数。这在做数据对比和排序时非常致命。比如某张表里有一个status列某些脏数据为-1你写条件SELECT * FROM t_status WHERE CONVERT(status, UNSIGNED) 18446744073709551615;你以为在查一个异常值实际上查不到你想要的那行数据因为真正的-1已经被类型转换吞掉了。反过来把一个很大的 UNSIGNED 值转成 SIGNED也可能变成负数。这类转换在涉及底层位运算或协议数据的场景里建议少用业务层面基本用不到。真要取绝对值或判断正负先用SIGN()或比较运算符处理比依赖无符号转换靠谱。6. 真·踩坑实录CONVERT 在复杂查询里的三个教训6.1 教训一WHERE 条件里滥用 CONVERT 导致索引失效有一次业务方反馈某个订单查询接口越跑越慢。我打开慢查询日志锁定了一条 SQLSELECT * FROM t_order WHERE CONVERT(order_no, CHAR) ?写这条 SQL 的同事的本意是“把可能传进来的数字转成字符串再比较”但他把转换放在了列上。结果就是order_no上的索引完全失效每次请求都全表扫描。最讽刺的是如果他不转换MySQL 自己也能处理数字和字符串的等值比较而且大多数时候能命中索引。正确的改法是先判断入参类型再决定怎么写。如果入参可能是数字也可能是字符串那就把入参统一转成字符串再和字符串列比较SELECT * FROM t_order WHERE order_no CAST(? AS CHAR);这里转换的是参数order_no列保持原样索引就能用上。这个坑的根子在于很多人写“防御性代码”时不加思考地把函数套到了列上结果防御变成了破坏。6.2 教训二字符串日期比较的区间误判另一个记忆犹新的坑来自报表统计。有一个日志表log_date字段是 VARCHAR存的是2023-06-15 14:30:00这种字符串。写统计 SQL 的人想查 6 月 15 日全天数据写成了SELECT COUNT(*) FROM t_log WHERE log_date 2023-06-15 AND log_date 2023-06-16;乍一看没问题但字符串比较是按字典序来的。2023-06-15 23:59:59大于2023-06-16吗按字典序比较字符串2023-06-15 23:59:59和2023-06-16比较时逐位比较到2023-06-1之后一个是5一个是6所以2023-06-15 ...小于2023-06-16侥幸没错。但如果把范围写成between 2023-06-15 and 2023-06-16某些边缘值就会按字典序落进区间产生误判。正确的做法是先确定这个字段该不该是字符串。如果业务上确实是日志型查询我建议在中间层用 STR_TO_DATE 统一转换后再比较SELECT COUNT(*) FROM t_log WHERE STR_TO_DATE(log_date, %Y-%m-%d %H:%i:%s) 2023-06-15 00:00:00 AND STR_TO_DATE(log_date, %Y-%m-%d %H:%i:%s) 2023-06-16 00:00:00;当然这样写会牺牲log_date上的索引。最根本的解法还是把字段类型改成 DATETIME让比较走真正的日期逻辑。字符串字段存时间迟早是要还的。6.3 教训三返回类型与程序语言类型的摩擦最后一个教训不是 SQL 层报错而是 SQL 和程序之间“类型空欢喜”的摩擦。我早年写过一个报表接口SQL 里对金额做了汇总SELECT CONVERT(SUM(amount) / COUNT(*), DECIMAL(10,2)) AS avg_amount FROM t_order;MySQL 返回的类型是 DECIMAL结果到了 Java 端被 JDBC 驱动映射成了BigDecimal。前端要求最多两位小数看起来也没问题。但后续有人把这段 SQL 用于导出功能程序里直接调doubleValue()精度丢失导出文件里的平均数偶尔差了几分钱。这类问题的关键不是 CONVERT 本身而是你要提前想清楚这个值要被哪种语言消费。返回给 Java 的金额类字段最好保持一致用 DECIMAL不要为了省事转成 DOUBLE 或 FLOAT。如果确定要浮点数就在 SQL 端用ROUND处理好精度SELECT ROUND(AVG(amount), 2) AS avg_amount FROM t_order;在迁移和维护历史系统时这类隐蔽的精度问题比语法报错难查得多。我现在的习惯是每个报表接口除了看 SQL 是否能跑通还会看一眼驱动映射后的类型把“SQL 类型”和“程序类型”这条链路彻底确认一遍才算结束。关于 MySQL 的 convert 函数能讲的远不止上面的例子但最核心的一条经验是类型转换是把双刃剑它能解决脏数据问题也能制造新的脏数据问题。我自己的习惯是优先保证表结构设计合理让字段类型一开始就对了实在要转换时优先转换常量、转换参数而不是转换列能用 CAST 的场景就先用 CAST尽量少用依赖特定方言的写法。最后再分享一个小技巧写完任何带 CONVERT 的 SQL先跑一遍 EXPLAIN看到索引正常命中心里这笔账才算真正结清。