类型转换

导学

当运算符两边的操作数类型不一致时，MySQL 会自动进行类型转换。理解转换规则，才能避免隐式转换导致的索引失效和意外结果。

定义

类型转换：将一种数据类型的值转换为另一种类型的过程。MySQL 支持显式转换（用 CAST 或 CONVERT）和隐式转换（自动进行）。

隐式转换规则

MySQL 的隐式转换遵循以下原则：

  1. 两个参数至少有一个是 NULL，比较结果是 NULL
  2. 两个参数都是字符串，按字符串比较
  3. 两个参数都是整数，按整数比较
  4. 一个字符串一个数字，MySQL 将字符串转为数字后比较
  5. 日期/时间类型与数字比较，转为数字

隐式转换对比演示

当前数据状态：

CREATE TABLE type_test (
    id INT PRIMARY KEY,
    str_val VARCHAR(20),
    int_val INT
);

INSERT INTO type_test VALUES
(1, '123', 123),
(2, '123abc', 456);

当前表数据：

idstr_valint_val
1123123
2123abc456

场景一：字符串与数字的隐式转换

操作语句：

SELECT
    id,
    str_val,
    int_val,
    str_val = int_val AS str_eq_int,
    str_val + 0 AS str_to_num
FROM type_test;

操作后的数据状态：

idstr_valint_valstr_eq_intstr_to_num
11231231123
2123abc4560123

结果解读：

  • id=1：str_val = '123' 转为数字 123，与 int_val = 123 相等，返回 1（TRUE）
  • id=2：str_val = '123abc' 转为数字时只取前导数字部分 123，所以 str_to_num = 123

危险：'123abc' = 123 返回 TRUE，因为 MySQL 将字符串转为数字时只取前导数字部分。这种隐式转换极易导致逻辑错误。

字符串比较的陷阱

SELECT
    id,
    str_val,
    str_val = '123' AS exact_str_compare,
    str_val = 123 AS implicit_num_compare
FROM type_test WHERE id = 1;

结果：

idstr_valexact_str_compareimplicit_num_compare
112311

结果解读：

  • id=1：str_val = '123' 按字符串精确比较，'123' 等于 '123'，返回 1
  • str_val = 123 按数字比较，'123' 转为 123，与 123 相等，返回 1
  • 这说明隐式转换可能改变比较逻辑，导致意外结果

场景二：隐式转换导致索引失效

当前数据状态：

CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20),
    dept VARCHAR(20),
    score DECIMAL(5,2),
    phone VARCHAR(20),
    INDEX idx_phone (phone)
);

INSERT INTO employees (emp_name, dept, score, phone) VALUES
('大翔', '技术部', 100, '13800138001'),
('白歌', '技术部', NULL, '13912345678');

当前表数据：

emp_idphone
113800138001
213912345678

错误写法（索引失效）：

-- phone 列是 VARCHAR，与数字常量比较
SELECT * FROM employees WHERE phone = 13800138001;

分析：phone 是字符串，13800138001 是数字。MySQL 会将表中所有 phone 值转为数字再比较，导致：

  1. 无法使用 phone 列的索引（全表扫描）
  2. 额外的 CPU 开销（逐行转换）

正确写法（可利用索引）：

SELECT * FROM employees WHERE phone = '13800138001';

结果解读：给常量加引号，保持类型一致，phone = '13800138001' 可以正常使用 idx_phone 索引。

显式转换

CAST

当前数据状态：

CREATE TABLE raw_data (
    id INT PRIMARY KEY,
    date_str VARCHAR(20),
    amount_str VARCHAR(20)
);

INSERT INTO raw_data VALUES
(1, '2024-01-15', '123.456'),
(2, '2024-06-10', '999.999');

当前表数据：

iddate_stramount_str
12024-01-15123.456
22024-06-10999.999

操作语句：

SELECT
    id,
    date_str,
    amount_str,
    CAST(date_str AS DATE) AS converted_date,
    CAST(amount_str AS DECIMAL(10, 2)) AS converted_amount,
    CAST('456' AS UNSIGNED) AS str_to_unsigned
FROM raw_data;

操作后的数据状态：

iddate_stramount_strconverted_dateconverted_amountstr_to_unsigned
12024-01-15123.4562024-01-15123.46456
22024-06-10999.9992024-06-101000.00456

结果解读：

  • CAST(date_str AS DATE) 将字符串 '2024-01-15' 转为日期类型
  • CAST(amount_str AS DECIMAL(10, 2)) 将字符串转为定点数，并四舍五入到 2 位小数
  • CAST('456' AS UNSIGNED) 将字符串转为无符号整数

CONVERT

SELECT
    id,
    date_str,
    CONVERT(date_str, DATE) AS convert_date,
    CONVERT(amount_str, DECIMAL(10, 2)) AS convert_amount
FROM raw_data;

结果：

iddate_strconvert_dateconvert_amount
12024-01-152024-01-15123.46
22024-06-102024-06-101000.00

结果解读：CAST(expr AS type) 和 CONVERT(expr, type) 在类型转换上等价，只是语法不同。

CONVERT 还可用于字符集转换：

SELECT CONVERT('中文' USING utf8mb4) AS charset_convert;

日期与字符串的转换

字符串转日期

当前数据状态：

CREATE TABLE import_logs (
    log_id INT PRIMARY KEY,
    raw_date VARCHAR(20)
);

INSERT INTO import_logs VALUES
(1, '20240115'),
(2, '15-01-2024');

当前表数据：

log_idraw_date
120240115
215-01-2024

操作语句：

SELECT
    log_id,
    raw_date,
    CAST(raw_date AS DATE) AS cast_date,
    STR_TO_DATE(raw_date, '%d-%m-%Y') AS str_to_date_custom
FROM import_logs;

操作后的数据状态：

log_idraw_datecast_datestr_to_date_custom
1202401152024-01-15NULL
215-01-2024NULL2024-01-15

结果解读：

  • CAST('20240115' AS DATE) 可以识别标准紧凑格式，成功转为 2024-01-15
  • CAST('15-01-2024' AS DATE) 失败，返回 NULL，因为这种格式不符合默认解析规则
  • STR_TO_DATE('15-01-2024', '%d-%m-%Y') 使用指定格式成功解析

日期转字符串

SELECT
    log_id,
    CAST('2024-01-15' AS DATE) AS date_val,
    DATE_FORMAT(CAST('2024-01-15' AS DATE), '%Y年%m月%d日') AS cn_format,
    DATE_FORMAT(CAST('2024-01-15' AS DATE), '%W, %M %d, %Y') AS en_format
FROM import_logs WHERE log_id = 1;

结果：

log_iddate_valcn_formaten_format
12024-01-152024年01月15日Monday, January 15, 2024

结果解读：DATE_FORMAT 将日期类型按指定格式转为字符串，支持中英文等多种格式。

综合场景：数据清洗中的类型转换

当前数据状态：

CREATE TABLE raw_orders (
    order_id INT PRIMARY KEY,
    customer_id VARCHAR(20),
    order_amount VARCHAR(20),
    order_date VARCHAR(20)
);

INSERT INTO raw_orders VALUES
(1, 'C001', '199.99', '2024-01-15'),
(2, 'C002', 'invalid', '2024-02-20');

当前表数据：

order_idcustomer_idorder_amountorder_date
1C001199.992024-01-15
2C002invalid2024-02-20

操作语句：

SELECT
    order_id,
    customer_id,
    order_amount,
    order_date,
    CAST(order_amount AS DECIMAL(10, 2)) AS amount_num,
    CAST(order_date AS DATE) AS date_val,
    CASE
        WHEN order_amount REGEXP '^[0-9]+\\.?[0-9]*$' THEN 'valid'
        ELSE 'invalid'
    END AS amount_check
FROM raw_orders;

操作后的数据状态：

order_idcustomer_idorder_amountorder_dateamount_numdate_valamount_check
1C001199.992024-01-15199.992024-01-15valid
2C002invalid2024-02-200.002024-02-20invalid

结果解读：

  • CAST(order_amount AS DECIMAL(10, 2)) 将字符串金额转为数字，'invalid' 转为 0.00
  • CAST(order_date AS DATE) 将字符串日期转为日期类型
  • 使用正则表达式预先检查金额格式，标识出无效数据，避免转换异常

常见误区

误区正解
隐式转换总是安全的隐式转换会导致索引失效和意外结果，应尽量显式转换。
CAST 和 CONVERT 功能不同类型转换时功能相同，CONVERT 额外支持字符集转换。
任何字符串都能转成日期格式不匹配时返回 NULL，如 CAST('abc' AS DATE) → NULL。

面试考点

Q：为什么 WHERE phone = 123 比 WHERE phone = '123' 慢？

如果 phone 是字符串类型，与数字比较时 MySQL 会将表中所有行的 phone 值隐式转为数字，导致索引失效（全表扫描）。正确做法是给常量加引号，保持类型一致。

Q：SELECT '10' > 9 的结果是什么？

1（TRUE）。字符串 '10' 被转为数字 10，然后 10 > 9。

Q：SELECT '10' > '9' 的结果是什么？

0（FALSE）。两边都是字符串，按字典序比较：'1' < '9'，所以 '10' < '9'。

小结

  • 隐式转换可能导致索引失效和意外结果，应尽量避免
  • 查询时确保常量类型与列类型一致（字符串加引号）
  • 显式转换用 CAST(expr AS type) 或 CONVERT(expr, type)
  • 日期字符串转换用 STR_TO_DATE 和 DATE_FORMAT

下一章引子：了解了数据类型，接下来学习 MySQL 提供的丰富内置函数，让数据处理更加灵活。