CAST函数用于显式类型转换,语法为CAST(expression AS target_type),要求源值在目标类型语义上可表达,不处理空格、时区及格式歧义,且可能影响索引性能。

CAST函数的基本用法和语法结构
SQL里CAST不是万能转换器,它只做显式类型转换,且必须满足源值在目标类型语义上可表达。比如'2023-10-05'能转DATE,但'invalid-date'会直接报错(不同数据库错误信息略有差异,常见如ERROR: invalid input syntax for type date)。
标准写法是CAST(expression AS target_type),注意AS不能省略,也不能写成TO或INTO——那是PostgreSQL的::或某些方言的扩展写法,非SQL标准。
-
expression可以是列名、字面量、计算结果,甚至嵌套函数返回值(只要最终类型可推导) -
target_type需写成类型关键字,如INTEGER、VARCHAR(10)、NUMERIC(8,2),括号和精度必须符合目标数据库支持格式 - MySQL中
CAST('123' AS SIGNED)合法,但CAST('123' AS INTEGER)会失败;而PostgreSQL两者都接受
字符串转数字时容易被忽略的空格和隐式截断问题
从VARCHAR转NUMERIC或INTEGER最常踩坑:前后空格不自动清理,超长数字被静默截断,或小数点位置非法直接报错。
例如CAST(' 42.50 ' AS DECIMAL(5,2))在多数数据库中成功,但CAST('42.567' AS DECIMAL(5,2))会四舍五入成42.57(PostgreSQL默认行为),而SQL Server可能直接报错Arithmetic overflow error converting varchar to data type numeric.
- 先用
TRIM()处理空格:CAST(TRIM(col_name) AS INTEGER) - 对不确定格式的字符串,优先用
TRY_CAST(SQL Server)或SAFE_CAST(BigQuery),避免查询中断 - MySQL没有
TRY_CAST,得靠CONVERT配合IF或正则预判:IF(col_name REGEXP '^[0-9]+(\.[0-9]+)?$', CAST(col_name AS DECIMAL(10,2)), NULL)
日期时间转换中时区和格式依赖的陷阱
CAST本身不解析格式,它依赖数据库当前locale和默认日期格式。把'05/10/2023'转DATE,在US locale下是10月5日,在DE locale下可能是5月10日——这不取决于CAST,而取决于底层datestyle(PostgreSQL)或DATEFORMAT(SQL Server)设置。
更危险的是带时间的字符串:CAST('2023-10-05 14:30' AS TIMESTAMP)在PostgreSQL中默认按ISO格式解析,但在Oracle里可能因NLS_DATE_FORMAT不同而失败。
- 跨库移植时,强制用ISO标准格式:
'2023-10-05T14:30:00'比'10/05/2023 2:30 PM'可靠得多 - 涉及时区,
CAST通常不处理;要用AT TIME ZONE(PostgreSQL/SQL Server)或CONVERT_TZ(MySQL)配合 - 如果源字段是
TIMESTAMP WITHOUT TIME ZONE但业务逻辑需要UTC,别只靠CAST,得明确转换:(col AT TIME ZONE 'UTC')
性能影响和替代方案选择
在WHERE或JOIN条件里用CAST包裹列(如WHERE CAST(created_at AS DATE) = '2023-10-05')会阻止索引使用——因为数据库无法对转换后的结果建立索引映射。
同样,SELECT列表里的CAST虽不影响执行计划,但大量行做类型转换仍增加CPU开销,尤其VARCHAR → JSON或TEXT → XML这类复杂类型。
- 索引友好写法:用范围查询代替日期截断,
created_at >= '2023-10-05' AND created_at - 如果必须转换后比较,考虑建函数索引(PostgreSQL支持
CREATE INDEX ON tbl ((CAST(col AS DATE)))) - 简单类型转换(如
INT → BIGINT)几乎无开销;但TEXT → NUMERIC需逐字符解析,数据量大时明显拖慢
真正难的不是语法,是搞清你的数据到底“长什么样”、目标库“认什么格式”、以及这个转换是否真的该发生在SQL层——有时候清洗逻辑放在应用层反而更可控。










