MySQL中CAST转换为integer报错1064，求解决方法

错误原因 #

MySQL的CAST()函数不支持使用integer作为目标转换类型，这是触发1064语法错误的核心原因。MySQL里用于整数转换的合法类型为SIGNED（有符号整数）、UNSIGNED（无符号整数）或者INT。

修正后的查询语句 #

SELECT e.*, 
       CAST(REPLACE(SUBSTR(annual_salary, 2), ',' , '') AS SIGNED) AS salary_cleaned,
       RANK() OVER(PARTITION BY department ORDER BY CAST(REPLACE(SUBSTR(annual_salary, 2), ',' , '') AS SIGNED) DESC) AS rn
FROM employees.employees_sample e

也可以用INT替代SIGNED，效果一致，且可以直接引用清洗后的字段排序：

SELECT e.*, 
       CAST(REPLACE(SUBSTR(annual_salary, 2), ',' , '') AS INT) AS salary_cleaned,
       RANK() OVER(PARTITION BY department ORDER BY salary_cleaned DESC) AS rn
FROM employees.employees_sample e

额外注意事项 #

• 原查询中rank()函数按annual_salary（TEXT类型）排序会触发文本排序逻辑（比如字符串"10000"会比"9999"小），而非数值排序逻辑，建议改为按清洗后的salary_cleaned字段排序，保证排名结果符合预期。
• 如果annual_salary字段存在无法转换为整数的异常值，可使用MySQL 8.0+支持的TRY_CAST()函数，转换失败时返回NULL避免查询中断：

SELECT e.*, 
       TRY_CAST(REPLACE(SUBSTR(annual_salary, 2), ',' , '') AS SIGNED) AS salary_cleaned,
       RANK() OVER(PARTITION BY department ORDER BY TRY_CAST(REPLACE(SUBSTR(annual_salary, 2), ',' , '') AS SIGNED) DESC) AS rn
FROM employees.employees_sample e

内容的提问来源于stack exchange，提问作者Azimovsky