mysql中的内置函数分类与常见函数使用

mysql字符串函数对NULL返回null而非空字符串，Length返回字节数、char_length返回字符数，trim默认仅去空格；日期函数中current_timestamp作默认值会自动初始化而now()不会；count(*)统计所有行，count(col)仅统计非null值；json路径区分大小写且不存在时静默返回null。

字符串函数：处理文本时最常踩坑的是空值和编码

MySQL 字符串函数在 NULL 输入时多数直接返回 NULL，不是空字符串。比如 CONCAT('a', NULL, 'b') 结果是 NULL，而非 'ab'；要用 CONCAT_WS() 或提前用 IFNULL() 处理。

常见误用：LENGTH() 返回字节数，CHAR_LENGTH() 才是字符数——在 utf8mb4 下一个 emoji 占 4 字节但算 1 个字符。

• TRIM() 默认只去空格，要去其他字符得写 TRIM(BOTH 'x' FROM col)
• SUBSTRING(col, 1, 3) 和 SUBSTR(col, 1, 3) 等价，但起始位置从 1 开始（不是 0）
• REPLACE(col, 'old', 'new') 区分大小写，且不支持正则；要正则替换需升级到 MySQL 8.0+ 并用 REGEXP_REPLACE()

SELECT    name,   CHAR_LENGTH(name) AS char_len,   LENGTH(name) AS byte_len,   IFNULL(TRIM(name), '(empty)') AS cleaned FROM users WHERE name IS NOT NULL;

日期时间函数：NOW() vs CURRENT_TIMESTAMP 有隐式行为差异

NOW() 和 CURRENT_TIMESTAMP 在大多数场景下等价，但作为列默认值时行为不同：定义 created_at TIMESTAMP default CURRENT_TIMESTAMP 会自动启用“自动初始化”，而 DEFAULT NOW() 不会——这会影响后续的 INSERT 是否触发默认填充。

另外，DATE_ADD(NOW(), intERVAL 1 DAY) 比 ADDDATE(NOW(), 1) 更明确、更常用；避免用 SYSDATE() 除非真需要语句执行时刻（它不受事务回滚影响，而 NOW() 在同一事务中多次调用返回相同值）。

• UNIX_TIMESTAMP() 输入为 DATETIME 时返回秒级时间戳；输入 INT 则反向转换，但注意时区：它始终按系统时区解释
• STR_TO_DATE('2023-10-05', '%Y-%m-%d') 格式必须严格匹配，多一个空格就返回 NULL
• WEEKDAY() 返回 0（周一）到 6（周日），而 DAYOFWEEK() 是 1（周日）到 7（周六）——别混用

聚合与数值函数：GROUP BY 场景下 COUNT(*) 和 COUNT(col) 差异明显

COUNT(*) 统计行数（包括含 NULL 的行），COUNT(col) 只统计该列非 NULL 的行。这是最常见的数据偏差来源——比如统计“有手机号的用户数”必须用 COUNT(phone)，而不是 COUNT(*)。

SUM() 和 AVG() 都会忽略 NULL 值，但若整组全为 NULL，SUM() 返回 NULL，AVG() 也返回 NULL（不是 0）。需要兜底时用 COALESCE(SUM(col), 0)。

• ROUND(123.456, 2) 返回 123.46，但注意浮点精度：对 DECIMAL 类型更安全
• ABS(-5) 没问题，但 ABS(NULL) → NULL，别假设它会转成 0
• GROUP_CONCAT(col SEPARATOR ';') 默认长度上限是 1024 字符，超长会被截断；可通过 SET session group_concat_max_len = 1000000 临时调整

JSON 函数（MySQL 5.7+）：路径表达式大小写敏感且不报错

MySQL 的 JSON 路径语法（如 $.name）对 key 名大小写敏感，且如果路径不存在，JSON_EXTRACT(json_col, '$.name') 返回 NULL，不会报错——容易掩盖字段名拼写错误。

更隐蔽的问题：JSON_CONTAINS() 第三个参数（路径）为空字符串时，行为不符合直觉；JSON_VALID() 是唯一能快速过滤脏数据的函数，建议在入库前校验。

• JSON_EXTRACT(json_col, '$.user.id') 和 json_col->'$.user.id' 等价，但后者更简洁；注意箭头操作符返回带引号的字符串，->> 才去引号
• JSON_SET(json_col, '$.status', 'active') 如果路径不存在会新增字段，但不会递归创建中间对象（'$.user.profile.age' 中若 profile 不存在，则整个操作失败）
• 更新 JSON 字段必须用 JSON_SET() 或 JSON_REPLACE()，直接 = 赋值会覆盖整个 JSON

SELECT    id,   JSON_EXTRACT(data, '$.user.name') AS name_raw,   data->>'$.user.name' AS name_clean,   JSON_CONTAINS(data, '"admin"', '$.roles') AS is_admin FROM config WHERE JSON_VALID(data);

实际用的时候，别只记函数名，重点盯住三件事：输入含不含 NULL、字符串按字节还是字符算、时间函数在定义列默认值时有没有隐式特性。这些地方一疏忽，查半天数据对不上。