我的个人技术笔记

数据类型与转换

7 min read

数据类型与转换

MySQL 中并没有直接返回“数据类型”的函数，但可以通过一些函数判断值的特征（是否为空、是否为数字等），同时提供了强大的显式类型转换机制，以及在运算和比较中会自动发生的隐式转换。

1. 数据类型判断

虽然没有 TYPEOF() 这样的函数，但以下方法可以帮助判断值的数据特征：

函数/表达式说明
ISNULL(expr)判断是否为 NULL，是返回 1，否则 0
IFNULL(expr1, expr2)若 expr1 为 NULL 则返回 expr2
COALESCE(expr1, ...)返回第一个非 NULL 值
NULLIF(expr1, expr2)若两值相等返回 NULL，否则返回 expr1
JSON_TYPE(json_val)返回 JSON 值的类型（OBJECT、ARRAY、STRING 等）
expr REGEXP '正则'可用于判断字符串是否全为数字、日期格式等
expr BETWEEN ... 或 CASE 结合算术比较间接判断字符串能否转为数字（MySQL 会将无法转的字符串当作 0）

示例

SELECT ISNULL(NULL);              -- 1
SELECT COALESCE(NULL, 'a', 'b');  -- a
SELECT NULLIF(10, 10);            -- NULL
 
-- 判断是否全为数字（纯整数）
SELECT '12345' REGEXP '^[0-9]+$';   -- 1
SELECT '12a'   REGEXP '^[0-9]+$';   -- 0
 
-- 判断是否可转为日期
SELECT STR_TO_DATE('2026-05-26', '%Y-%m-%d') IS NOT NULL;  -- 1

2. 显式类型转换：CAST 与 CONVERT

这两个函数可以将一个值强制转换为指定类型。

CAST

CAST(expr AS type)

支持类型：

  • BINARY[(N)]
  • CHAR[(N)] / CHARACTER[(N)] / NCHAR[(N)]
  • DATE
  • DATETIME[(fsp)]
  • DECIMAL[(M[,D])]
  • DOUBLE
  • FLOAT[(p)]
  • SIGNED [INTEGER] （有符号整数）
  • UNSIGNED [INTEGER] （无符号整数）
  • TIME[(fsp)]
  • YEAR
  • JSON（MySQL 5.7+）

CONVERT

两种用法：

  1. 类型转换（与 CAST 类似）： 
    CONVERT(expr, type)
    例如 CONVERT('123', SIGNED)
  2. 字符集转换： 
    CONVERT(expr USING charset_name)
SELECT CAST('123' AS UNSIGNED) + 1;          -- 124
SELECT CAST(123.456 AS DECIMAL(5,2));        -- 123.46
SELECT CONVERT('2026-05-26', DATE);          -- 2026-05-26
SELECT CONVERT('Hello' USING utf8mb4);       -- 修改字符集

常见转换组合

源类型目标类型示例
字符串 → 整数SIGNED / UNSIGNEDCAST('007' AS UNSIGNED) → 7
字符串 → 小数DECIMAL(M,D)CAST('12.34' AS DECIMAL(5,2))
数字 → 字符串CHARCAST(123 AS CHAR) → ‘123’
日期/时间 → 字符串DATE / DATETIMECAST(NOW() AS CHAR)
字符串 → 日期DATE / DATETIMECAST('2026-05-26' AS DATE)
二进制 → 字符串CHARCAST(binary_data AS CHAR)

3. 隐式类型转换及注意事项

在以下场景中，MySQL 会自动转换数据类型，可能产生意想不到的结果：

3.1 字符串与数字比较 / 运算

  • 字符串会被转换为数字，如果字符串不以数字开头，则转换为 0。
SELECT 'a' = 0;          -- 1（true！）
SELECT '123abc' = 123;   -- 1，因为 '123abc' 转成数字 123
SELECT '123abc' + 1;     -- 124
SELECT 'abc' + 1;        -- 1

3.2 日期时间比较

  • 字符串与 DATE / DATETIME 比较时，字符串会被当作日期解析。
SELECT '2026-05-26' > '2026-05-25';          -- 1（字符串按字典序比较）
SELECT '2026-05-26' > DATE '2026-05-25';    -- 1（字符串先转成日期再比）

直接用字符串比较日期格式，如果没有分隔符或格式不一致可能出错，建议显式转换。

3.3 与 NULL 比较

  • 任何值与 NULL 比较（除了 IS NULL）都返回 NULL。
  • SELECT 10 = NULL; → NULL，不是 0。

3.4 排序时的隐式转换

  • 如果 ORDER BY 的列是字符串类型（如 VARCHAR）但存的是数字，排序会按字典序而不是数值大小。
-- num_str 列值：'1','10','2','20'
SELECT num_str FROM t ORDER BY num_str;        -- '1','10','2','20' (字典序)
SELECT num_str FROM t ORDER BY CAST(num_str AS UNSIGNED); -- '1','2','10','20'

4. 常用业务场景中的类型转换

4.1 将存储为字符串的数字用于数值排序或计算

-- 正确排序
SELECT * FROM products ORDER BY CAST(price_str AS DECIMAL(10,2));
 
-- 计算总和
SELECT SUM(CAST(amount_str AS UNSIGNED)) FROM payments;

4.2 数字补零 / 格式化

-- 订单编号：'ORD' + 6位流水号
SELECT CONCAT('ORD', LPAD(CAST(id AS CHAR), 6, '0')) AS order_no
FROM orders;
-- 如果 id 是数字，LPAD 会隐式转为字符串

4.3 提取字符串中的数字并转换为数值

-- MySQL 8.0 正则提取并转换
SELECT CAST(REGEXP_SUBSTR('楼层15-16室', '[0-9]+') AS UNSIGNED) AS floor;
-- 结果为 15

4.4 安全处理空值或无效值

-- 如果字段可能为 NULL 或非数字，计算时给默认值
SELECT IFNULL(CAST(score AS SIGNED), 0) FROM exams;
 
-- 或者用 CASE 判断
SELECT CASE WHEN score REGEXP '^[0-9]+$' THEN CAST(score AS SIGNED) ELSE 0 END
FROM exams;

4.5 判断字符串能否转为日期，并转换

-- 尝试转为日期，如果失败则返回 NULL
SELECT IF(STR_TO_DATE(date_str, '%Y-%m-%d') IS NULL, NULL, CAST(date_str AS DATE))
FROM raw_data;
-- 或者直接用 STR_TO_DATE
SELECT STR_TO_DATE(date_str, '%Y-%m-%d') FROM raw_data;  -- 无法转换返回 NULL

4.6 JSON 字段值提取并转换类型

SELECT JSON_UNQUOTE(JSON_EXTRACT(extra, '$.age')) AS age_str,
       CAST(JSON_UNQUOTE(JSON_EXTRACT(extra, '$.age')) AS UNSIGNED) AS age_int
FROM profiles;
-- MySQL 5.7.8+ 也可使用 ->> 操作符
SELECT extra->>'$.age' AS age_str, CAST(extra->>'$.age' AS UNSIGNED) AS age_int
FROM profiles;

4.7 字符串比较忽略大小写（通过字符集转换实现）

SELECT * FROM users WHERE CONVERT(username USING utf8mb4) = 'admin';
-- 或者使用 COLLATE，更规范

4.8 二进制与字符串互转

-- 字符串转二进制（如存储哈希值）
SELECT UNHEX(SHA2('hello', 256));
-- 二进制转可读字符串
SELECT HEX(binary_field) FROM t;

4.9 避免隐式转换带来的”诡异”结果

-- 错误：'a' 被转为 0，导致 WHERE 条件为真
SELECT * FROM t WHERE 0 = 'a';  -- 返回所有行！
-- 正确做法：使用 CAST 或显式类型确保类型一致
SELECT * FROM t WHERE CAST(col AS UNSIGNED) = 0;

总结要点

  • 类型判断多借助 REGEXP、ISNULL、STR_TO_DATE 等函数来间接实现。
  • 显式转换使用 CAST(expr AS type) 或 CONVERT(expr, type)，可使代码清晰、避免隐式陷阱。
  • 注意 字符串转数字时非数字开头会变成 0，这种隐式行为可能导致逻辑错误和性能问题（索引失效）。
  • 处理用户输入或不确定格式的数据时，务必显式转换并配合判空/默认值，提高 SQL 的健壮性。

Graph View