SQL 字符串函数 CONCAT、SUBSTRING 高级应用

mysql中concat遇NULL即返回null，应改用concat_ws或coalesce预处理；substring起始位置为1而非0；拼接时需显式类型转换避免精度丢失；分隔符拆分应使用各库原生函数而非嵌套substring。

CONCAT 处理 NULL 时结果意外为空？MySQL 的 CONCAT 遇到任意参数为 NULL，整个结果直接变 NULL，不是跳过也不是转空字符串——这是最常踩的坑。

• 实际场景：拼接用户姓名字段（first_name、last_name），但部分记录 last_name 为 NULL，结果整条拼接值消失
• 解决办法不是硬加 IFNULL 套三层，而是用 CONCAT_WS（带分隔符版本）：它自动跳过 NULL 参数
• 或统一用 COALESCE 预处理：CONCAT(COALESCE(first_name, ''), ' ', COALESCE(last_name, ''))
• 注意：postgresql 的 || 拼接符行为不同——NULL || 'a' 结果是 NULL，但 CONCAT 函数在 PG 中反而会忽略 NULL

SUBSTRING 截取位置从 1 开始，不是 0所有主流 SQL 方言（MySQL、PostgreSQL、SQL Server、oracle）中，SUBSTRING（或 SUBSTR）的第一个位置参数都是 1-based，写成 0 就截不出东西，还可能不报错只返回空。

• 常见错误现象：SUBSTRING('abc', 0, 2) 在 MySQL 返回空字符串，不是 'ab'；在 PostgreSQL 直接报错 Error: substring() start position cannot be zero
• 正确写法：SUBSTRING('abc', 1, 2) → 'ab'；SUBSTRING('abc', 2)（省略长度）→ 'bc'
• 性能注意：在 WHERE 条件里用 SUBSTRING(col, 1, 3) = 'foo' 无法走索引；改用前缀匹配 col LIKE 'foo%' 更高效
• 兼容性提示：SQL Server 支持 SUBSTRING(col, start, Length)，也支持 LEFT(col, n) 这种更直白的写法

用 CONCAT 拼接字段+常量时，别漏掉类型隐式转换把数字字段和字符串拼一起，看似简单，实则容易因隐式转换失败或精度丢失翻车。

• 错误示例：CONCAT('ID:', id)，当 id 是 DECIMAL(10,2) 且值为 1.50，MySQL 可能输出 'ID:1.5'（末尾零被丢）
• 安全做法：显式格式化，MySQL 用 CONCAT('ID:', CAST(id AS char)) 或 CONCAT('ID:', format(id, 2))；PostgreSQL 用 'ID:' || id::TEXT
• 特别注意：SQL Server 的 + 拼接符对类型敏感，'ID:' + id（id 是 int）会报错，必须先 CAST 或用 CONCAT 函数
• 另一个坑：CONCAT 在 MySQL 5.7+ 才支持，老版本只能靠 CONCAT_WS('', ...) 或多层 IFNULL 模拟

想按分隔符拆字符串？SUBSTRING 不是万能解SUBSTRING 本身不支持按分隔符切分，强行用嵌套 LOCATE + SUBSTRING 写法难读、易错、不可复用。

• 真实需求比如：从 'a,b,c' 提取第二个元素，不是写三层 SUBSTRING，而是看数据库是否原生支持
• MySQL 8.0+ 可用 json_TABLE 配合 REPLACE 转 JSON 数组，或用 REGEXP_SUBSTR（需开启正则支持）
• PostgreSQL 推荐 STRING_TO_ARRAY + UNNEST 或 (STRING_TO_ARRAY('a,b,c', ','))[2]
• 兼容方案（通用但稍重）：写个简单存储函数封装拆分逻辑，避免每次都在 SQL 里堆 LOCATE 和 SUBSTRING

字符串拼接和截取看着基础，但跨库行为差异大，NULL 处理、索引友好性、类型转换这三块最容易在上线后突然冒泡。写完记得在目标数据库版本里实测 NULL 输入和边界位置。