网通社科技快报
业务SQL常用函数实战解析 覆盖空值日期字符串处理与跨库兼容要点
在业务系统开发中,SQL查询需频繁处理空值、日期格式与字符串拼接等场景。本文以KingbaseES V009R001C010为测试环境,构建t_expr_demo表模拟真实数据边界(如无昵称、空备注、全空行但含'NoName'昵称等),系统解析常用函数实践。
COALESCE作为多层兜底核心工具,用于显示名生成(优先昵称→用户名→'unknown')及金额计算(coalesce(amount,0)-coalesce(discount,0)),避免NULL参与运算导致结果中断。NULLIF则用于识别语义无效的非空值(如空字符串''),配合TRIM可统一清洗remark字段;需注意其不处理空格,须前置trim。
CASE WHEN支持状态映射(如'paid'→'已支付')与金额分层判断,可嵌套条件并明确处理NULL路径。迁移至KingbaseES时需警惕MySQL习惯写法的兼容风险:三参数CONCAT不支持,应改用||或嵌套concat; DATE_FORMAT需替换为to_char(create_time,'YYYY-MM-DD'); SUBSTRING语法对应substring(str from pos for len)或substr。
类型转换方面,KingbaseES支持::语法(如id::text),而MySQL用CAST/CONVERT;隐式转换易因引擎差异引发不可预测结果,建议显式声明类型(如DATE '2024-01-01')。最后提出建立业务表达式规范库的实践建议,涵盖显示名、净额、备注清洗与状态映射模板,提升接口稳定性与跨库迁移效率。
COALESCE作为多层兜底核心工具,用于显示名生成(优先昵称→用户名→'unknown')及金额计算(coalesce(amount,0)-coalesce(discount,0)),避免NULL参与运算导致结果中断。NULLIF则用于识别语义无效的非空值(如空字符串''),配合TRIM可统一清洗remark字段;需注意其不处理空格,须前置trim。
CASE WHEN支持状态映射(如'paid'→'已支付')与金额分层判断,可嵌套条件并明确处理NULL路径。迁移至KingbaseES时需警惕MySQL习惯写法的兼容风险:三参数CONCAT不支持,应改用||或嵌套concat; DATE_FORMAT需替换为to_char(create_time,'YYYY-MM-DD'); SUBSTRING语法对应substring(str from pos for len)或substr。
类型转换方面,KingbaseES支持::语法(如id::text),而MySQL用CAST/CONVERT;隐式转换易因引擎差异引发不可预测结果,建议显式声明类型(如DATE '2024-01-01')。最后提出建立业务表达式规范库的实践建议,涵盖显示名、净额、备注清洗与状态映射模板,提升接口稳定性与跨库迁移效率。