mysql中数据类型转换与CAST函数的使用

CAST()是mysql显式类型转换标准函数，语法为CAST(expression AS type)，支持char、SIGNED、DECIMAL、date等类型，不支持裸VARCHAR和Boolean；需注意静默截断、日期格式限制及索引失效风险。

CAST 函数的基本用法和语法结构

MySQL 中 CAST() 是显式类型转换的标准方式，比隐式转换更可控、更可读。它的核心语法是 CAST(expression AS type)，其中 type 必须是 MySQL 支持的明确目标类型，比如 CHAR、SIGNED、UNSIGNED、DECIMAL、DATE、DATETIME 等。

注意：不能写成 CAST(col AS VARCHAR) —— MySQL 不接受裸 VARCHAR，必须带长度，如 CHAR(10)；也不支持 BOOLEAN 作为目标类型（它会被当作 TINYINT(1) 处理）。

常见可用类型缩写：

• CHAR[(N)]：转为定长字符串，N 可选，不指定时默认长度由上下文决定
• SIGNED / UNSIGNED：转为有符号/无符号整数（等价于 SIGNED Integer）
• DECIMAL[(M,D)]：转为定点数，M 总位数，D 小数位数
• DATE / DATETIME / TIME：仅对能解析的字符串或数字有效（如 '2023-01-01' 或 20230101）

字符串转数字失败时的静默截断问题

当用 CAST('abc123' AS SIGNED) 时，MySQL 不报错，而是返回 0；而 CAST('123abc' AS SIGNED) 会返回 123 —— 它从左开始取连续数字，遇到非数字字符就停。这种行为容易掩盖数据质量问题。

避免误判的实操建议：

• 先用 regexp '^[0-9]+$' 检查纯数字字符串，再 CAST
• 对可能含空格或符号的字段，先 TRIM() 再判断，例如 CAST(TRIM(col) AS SIGNED)
• 在严格模式下（STRICT_TRANS_TABLES），部分非法转换会报错，但并非全部 —— 不要依赖模式切换来兜底

SELECT    col,   CAST(col AS SIGNED) AS cast_result,   col REGEXP '^[[:space:]]*[+-]?[0-9]+[[:space:]]*$' AS looks_like_int FROM (SELECT '  -456  ' AS col UNION SELECT '78x' UNION SELECT '') t;

日期字符串转 DATETIME 的兼容性陷阱

CAST('2023/05/20' AS DATETIME) 在 MySQL 8.0+ 中可以成功，但 CAST('2023-05-20 14:30' AS DATETIME) 才是稳妥写法。MySQL 对分隔符和精度容忍度有限：不支持 '2023-05-20T14:30:00Z'（ISO 8601 带时区），也不识别 '05/20/2023'（MDY 格式）。

关键限制：

• 只接受标准格式：yyYY-MM-DD、YYYY-MM-DD HH:MM:SS、YYYYMMDD、YYYYMMDDHHMMSS
• 毫秒部分必须是小数点后 1–6 位，如 '2023-05-20 14:30:00.123'
• 若源字段是 VARCHAR 且格式混乱，优先用 STR_TO_DATE() 替代 CAST()，它支持自定义格式串

SELECT    CAST('2023-05-20 14:30:00' AS DATETIME) AS ok,   CAST('2023-05-20T14:30:00' AS DATETIME) AS fails,  -- 返回 NULL   STR_TO_DATE('2023/05/20', '%Y/%m/%d') AS works_with_format;

CAST 与索引失效的性能隐患

在 WHERE 条件中对列使用 CAST(col AS CHAR) 或 CAST(col AS SIGNED)，会导致该列无法走索引（即使它原本是索引列）。本质是函数依赖：优化器无法将索引 B+ 树直接映射到转换后的值域。

典型反模式：

• WHERE CAST(phone AS CHAR) = '13800138000' → phone 是 BIGINT 类型，但加 CAST 后索引失效
• WHERE CAST(created_at AS DATE) = '2023-01-01' → 应改用范围查询：created_at >= '2023-01-01' AND created_at
• 如果必须做类型对齐，优先转换常量而非列，例如 WHERE status = CAST('1' AS UNSIGNED)（status 是 TINYINT）

真正需要列转换又想保索引的场景极少，多数时候应调整数据模型或应用层预处理 —— CAST 是“最后手段”，不是类型适配首选。