第十九章字符串与数值函数

本章目标

通过本章学习，你将能够：

掌握 MySQL 中所有核心字符串函数的用法和适用场景
理解字节长度与字符长度的区别，规避多字节字符集下的陷阱
熟练使用字符串截取、替换、填充、查找等操作进行数据清洗
掌握数值运算和数学函数，能够进行精确的数值处理
理解 CONCAT 与 CONCAT_WS 的 NULL 处理差异
能够利用 FIELD 和 FIND_IN_SET 实现自定义排序和集合匹配
在实际业务中灵活组合字符串与数值函数完成复杂数据转换

前置知识

在学习本章之前，你需要：

了解基本的 SQL SELECT 查询语法
理解什么是字符集（charset）和校对规则（collation），特别是 utf8mb4 字符集
知道 NULL 在 SQL 中的含义以及 NULL 参与运算的特殊规则
了解 MySQL 的基本数据类型（VARCHAR、INT、DECIMAL 等）
掌握基本的数值运算概念（四舍五入、取模、幂运算等）

19.1 字符串连接函数

19.1.1 CONCAT(str1, str2, ...)

CONCAT 用于将多个字符串参数按顺序拼接成一个字符串。它是数据清洗和报表生成中最常用的函数之一。

sql

-- 基本用法：拼接姓名
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

-- 拼接地址
SELECT CONCAT(province, city, district, street) AS full_address
FROM addresses;

-- 拼接带分隔符的编号
SELECT CONCAT('ORD-', LPAD(id, 8, '0')) AS order_number
FROM orders;

重要特性 —— NULL 传播：如果任意一个参数为 NULL，CONCAT 的返回值将是 NULL。这是最常见的坑。

sql

-- 演示 NULL 传播问题
SELECT CONCAT('Hello', NULL, 'World') AS result;  -- 结果: NULL

-- 实际场景：用户表中 middle_name 可能为 NULL
SELECT CONCAT(first_name, ' ', middle_name, ' ', last_name) AS full_name
FROM users;
-- 如果 middle_name 为 NULL，整个 full_name 变为 NULL！

解决方案：

使用 COALESCE 将 NULL 替换为空字符串
使用 CONCAT_WS（推荐）

sql

-- 方案一：使用 COALESCE
SELECT CONCAT(first_name, ' ', COALESCE(middle_name, ''), ' ', last_name) AS full_name
FROM users;

-- 方案二：使用 CONCAT_WS（推荐，见下节）

19.1.2 CONCAT_WS(separator, str1, str2, ...)

CONCAT_WS（Concatenate With Separator）的第一个参数是分隔符，之后的参数为要拼接的字符串。它会自动跳过 NULL 值，不会导致整个结果为 NULL。

sql

-- 基本用法
SELECT CONCAT_WS('-', '2024', '01', '15') AS date_str;  -- 结果: 2024-01-15

-- 跳过 NULL 值
SELECT CONCAT_WS(' ', 'Hello', NULL, 'World') AS result;  -- 结果: Hello World

-- 实际场景：姓名拼接（自动处理 middle_name 为 NULL 的情况）
SELECT CONCAT_WS(' ', first_name, middle_name, last_name) AS full_name
FROM users;
-- middle_name 为 NULL 时，first_name 和 last_name 之间只有一个空格

-- 拼接地址（多个字段可能为 NULL）
SELECT CONCAT_WS(', ', address_line1, address_line2, city, state, zip_code) AS full_address
FROM addresses;

-- 生成 CSV 行
SELECT CONCAT_WS(',', id, name, email, phone) AS csv_row
FROM customers;

19.1.3 CONCAT 与 CONCAT_WS 对比

特性	CONCAT	CONCAT_WS
NULL 处理	任一参数为 NULL → 结果为 NULL	跳过 NULL 值
分隔符	需手动插入	自动在非 NULL 值之间插入
参数数量	至少 1 个	至少 2 个（分隔符 + 1 个值）
性能	略快（无 NULL 检查逻辑）	略慢（需要跳过 NULL）

19.2 字符串长度函数

19.2.1 LENGTH(str) —— 字节长度

LENGTH 返回字符串的字节数，取决于字符集编码。在 utf8mb4 中，一个中文字符占 3-4 个字节。

sql

-- 英文字符：每个字符 1 字节
SELECT LENGTH('Hello');       -- 结果: 5

-- 中文字符：utf8mb4 下每个汉字 3 字节
SELECT LENGTH('你好');         -- 结果: 6

-- 混合字符
SELECT LENGTH('hello你好');    -- 结果: 11 (5 + 6)

-- Emoji：utf8mb4 下占 4 字节
SELECT LENGTH('😀');           -- 结果: 4

19.2.2 CHAR_LENGTH(str) / CHARACTER_LENGTH(str) —— 字符长度

这两个函数是同义词，返回字符串的字符数（即人们通常理解的"几个字"）。

sql

-- 英文字符
SELECT CHAR_LENGTH('Hello');       -- 结果: 5

-- 中文字符：与人的感知一致
SELECT CHAR_LENGTH('你好');         -- 结果: 2

-- 混合字符
SELECT CHAR_LENGTH('hello你好');    -- 结果: 7

-- Emoji 算 1 个字符
SELECT CHAR_LENGTH('😀🚀');         -- 结果: 2

19.2.3 核心对比演示

sql

-- 在一个查询中对比两种长度
SELECT
    '你好' AS input_str,
    LENGTH('你好') AS byte_length,
    CHAR_LENGTH('你好') AS char_length,
    CHARACTER_LENGTH('你好') AS char_length2;  -- 同义词

-- 结果:
-- input_str | byte_length | char_length | char_length2
-- 你好      | 6           | 2           | 2

-- 验证：utf8mb4 中不同字符的字节占用
SELECT
    'a' AS ch, LENGTH('a') AS bytes, CHAR_LENGTH('a') AS chars
UNION ALL
SELECT
    '你' AS ch, LENGTH('你') AS bytes, CHAR_LENGTH('你') AS chars
UNION ALL
SELECT
    '😀' AS ch, LENGTH('😀') AS bytes, CHAR_LENGTH('😀') AS chars;

-- 实际应用场景：验证手机号长度
SELECT phone, LENGTH(phone), CHAR_LENGTH(phone)
FROM users
WHERE CHAR_LENGTH(phone) != 11;  -- 找出手机号位数不正确的记录

-- 验证用户名长度限制（按字符数而非字节数）
SELECT username
FROM users
WHERE CHAR_LENGTH(username) > 20;  -- 用户名超过 20 个字符

19.3 字符串截取函数

19.3.1 LEFT(str, len) 和 RIGHT(str, len)

从字符串左端或右端截取指定长度的字符（按字符数，而非字节数）。

sql

-- LEFT：从左边取
SELECT LEFT('Hello World', 5);       -- 结果: Hello
SELECT LEFT('你好世界', 2);           -- 结果: 你好

-- RIGHT：从右边取
SELECT RIGHT('Hello World', 5);      -- 结果: World
SELECT RIGHT('13812345678', 4);      -- 结果: 5678（取手机号后四位）

-- 实际应用：脱敏显示
SELECT
    CONCAT(LEFT(phone, 3), '****', RIGHT(phone, 4)) AS masked_phone
FROM users;
-- 结果示例: 138****5678

-- 取文件扩展名
SELECT
    filename,
    RIGHT(filename, LOCATE('.', REVERSE(filename)) - 1) AS extension
FROM files;

19.3.2 SUBSTRING(str, pos, len) / SUBSTR(str, pos, len)

SUBSTRING 和 SUBSTR 完全等价。从指定位置开始截取，位置从 1 开始计数。如果 pos 为负数，则从字符串末尾开始倒计数。

sql

-- 基本用法：从第 2 个字符开始取 3 个字符
SELECT SUBSTRING('Hello World', 2, 3);   -- 结果: ell

-- 省略 len 参数：取到字符串末尾
SELECT SUBSTRING('Hello World', 7);      -- 结果: World

-- 负数 pos：从末尾倒数
SELECT SUBSTRING('Hello World', -5, 3);  -- 结果: Wor
-- -5 表示从倒数第 5 个字符开始（即 'W'），取 3 个

-- 实际应用：截取身份证中的出生日期（中国 18 位身份证）
SELECT
    id_card,
    SUBSTRING(id_card, 7, 8) AS birth_date
FROM citizens;
-- 结果: id_card=110101199001011234 → birth_date=19900101

-- 从 URL 中提取域名部分
SELECT
    url,
    SUBSTRING(url, LOCATE('://', url) + 3,
              LOCATE('/', url, LOCATE('://', url) + 3) - LOCATE('://', url) - 3) AS domain
FROM web_logs;

19.3.3 SUBSTRING_INDEX(str, delim, count)

按分隔符 delim 拆分字符串，并返回第 count 次出现之前（或之后）的部分。count 为正数时从左数，为负数时从右数。

sql

-- count 为正：从左数，返回前 count 个分隔符之前的部分
SELECT SUBSTRING_INDEX('www.example.com', '.', 1);   -- 结果: www
SELECT SUBSTRING_INDEX('www.example.com', '.', 2);   -- 结果: www.example
SELECT SUBSTRING_INDEX('www.example.com', '.', 3);   -- 结果: www.example.com

-- count 为负：从右数，返回后 |count| 个分隔符之后的部分
SELECT SUBSTRING_INDEX('www.example.com', '.', -1);  -- 结果: com
SELECT SUBSTRING_INDEX('www.example.com', '.', -2);  -- 结果: example.com

-- 核心技巧：提取域名（取中间部分）
-- 第一步：去掉顶级域名
SELECT SUBSTRING_INDEX('www.example.com', '.', 2);   -- www.example
-- 第二步：从结果中取最后一部分
SELECT SUBSTRING_INDEX(
    SUBSTRING_INDEX('www.example.com', '.', 2),
    '.', -1
) AS domain;  -- 结果: example

-- 实际应用：从 email 提取域名
SELECT
    email,
    SUBSTRING_INDEX(email, '@', -1) AS domain
FROM users;

-- 从路径中提取文件名
SELECT
    file_path,
    SUBSTRING_INDEX(file_path, '/', -1) AS file_name
FROM attachments;

19.4 字符串修剪函数

19.4.1 TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)

TRIM 是最灵活的修剪函数，可以指定修剪方向和要修剪的字符。

sql

-- 默认行为：去除两端空格（等价于 BOTH ' ' FROM）
SELECT TRIM('   Hello   ');                -- 结果: Hello

-- 指定方向
SELECT TRIM(LEADING ' ' FROM '   Hello');  -- 结果: Hello（去掉左边空格）
SELECT TRIM(TRAILING ' ' FROM 'Hello   ');  -- 结果: Hello（去掉右边空格）
SELECT TRIM(BOTH ' ' FROM '   Hello   ');  -- 结果: Hello（去掉两边空格）

-- 去除非空格字符
SELECT TRIM(BOTH '0' FROM '000123000');     -- 结果: 123
SELECT TRIM(LEADING '0' FROM '000123');     -- 结果: 123
SELECT TRIM(TRAILING '0' FROM '123.4500');  -- 结果: 123.45

-- 去除多个不同字符（只能指定一个 remstr，逐个字符匹配）
SELECT TRIM(BOTH 'xyz' FROM 'xyzabcxyz');   -- 结果: abc

-- 实际应用：清理导入数据中的前后空格
UPDATE customers
SET name = TRIM(name),
    email = TRIM(email);

19.4.2 LTRIM(str) 和 RTRIM(str)

分别去除左边和右边的空格。这两个是 TRIM 的简化版本。

sql

-- LTRIM：去除左边空格
SELECT LTRIM('   Hello');              -- 结果: Hello
SELECT CONCAT('[', LTRIM('   Hello'), ']');  -- 结果: [Hello]

-- RTRIM：去除右边空格
SELECT RTRIM('Hello   ');              -- 结果: Hello
SELECT CONCAT('[', RTRIM('Hello   '), ']');  -- 结果: [Hello]

-- 实际应用：修正因数据录入不规范导致的查询失败
SELECT * FROM users WHERE LTRIM(RTRIM(username)) = 'admin';
-- 注意：WHERE username = 'admin' 会漏掉前后带空格的记录

19.5 大小写转换函数

sql

-- UPPER(str) / UCASE(str)：转换为大写
SELECT UPPER('hello');        -- 结果: HELLO
SELECT UCASE('hello');        -- 结果: HELLO（同义词）

-- LOWER(str) / LCASE(str)：转换为小写
SELECT LOWER('HELLO');        -- 结果: hello
SELECT LCASE('HELLO');        -- 结果: hello（同义词）

-- 实际应用：不区分大小写的搜索
SELECT * FROM users
WHERE UPPER(username) = UPPER('Admin');
-- 等价于使用不区分大小写的校对规则

-- 规范化数据存储
UPDATE countries SET code = UPPER(code);    -- 国家代码统一大写
UPDATE emails SET email = LOWER(email);     -- 邮箱统一小写

-- 首字母大写（MySQL 没有内置函数，需要组合使用）
SELECT CONCAT(
    UPPER(LEFT('hello world', 1)),
    LOWER(SUBSTRING('hello world', 2))
) AS title_case;  -- 结果: Hello world

19.6 字符串查找与替换函数

19.6.1 REPLACE(str, from_str, to_str)

将字符串中所有出现的 from_str 替换为 to_str。区分大小写。

sql

-- 基本用法
SELECT REPLACE('Hello World', 'World', 'MySQL');  -- 结果: Hello MySQL

-- 大小写敏感
SELECT REPLACE('Hello hello', 'hello', 'hi');     -- 结果: Hello hi（只替换了小写的）

-- 实际应用：数据清洗
-- 清理电话号码中的分隔符
SELECT REPLACE(REPLACE(REPLACE(phone, '-', ''), ' ', ''), '(', '') AS clean_phone
FROM contacts;

-- 替换敏感词
SELECT REPLACE(comment, '敏感词', '***') AS filtered_comment
FROM posts;

-- 去除 HTML 标签（简单场景）
SELECT REPLACE(REPLACE(content, '<p>', ''), '</p>', '') AS plain_text
FROM articles;

-- 多值替换：可用嵌套 REPLACE 实现
SELECT REPLACE(REPLACE(REPLACE(status, '0', '待审核'), '1', '已通过'), '2', '已拒绝') AS status_text
FROM orders;

19.6.2 INSERT(str, pos, len, newstr)

在指定位置 pos 处，删除 len 个字符，然后插入 newstr。注意：这里的 INSERT 不是数据库的 INSERT 语句，而是字符串操作函数。

sql

-- 基本用法：从第 7 个字符开始，删除 5 个字符，插入 'MySQL'
SELECT INSERT('Hello World', 7, 5, 'MySQL');   -- 结果: Hello MySQL

-- 只删除不插入（newstr 为空字符串）
SELECT INSERT('Hello World', 6, 6, '');         -- 结果: Hello

-- 只插入不删除（len 为 0）
SELECT INSERT('Hello', 6, 0, ' World');         -- 结果: Hello World

-- 实际应用：格式化手机号
SELECT INSERT(INSERT('13812345678', 4, 0, '-'), 8, 0, '-') AS formatted;
-- 结果: 138-1234-5678

19.6.3 查找位置函数

MySQL 提供了多个查找子串位置的函数。

sql

-- LOCATE(substr, str, [pos])：返回子串首次出现的位置（从 1 开始）
SELECT LOCATE('World', 'Hello World');       -- 结果: 7
SELECT LOCATE('o', 'Hello World');           -- 结果: 5（第一个 'o' 的位置）
SELECT LOCATE('o', 'Hello World', 6);        -- 结果: 8（从第 6 位开始查找）

-- POSITION(substr IN str)：标准 SQL 语法，等价于 LOCATE
SELECT POSITION('World' IN 'Hello World');   -- 结果: 7

-- INSTR(str, substr)：参数顺序与 LOCATE 相反！
SELECT INSTR('Hello World', 'World');        -- 结果: 7
-- 注意：INSTR('Hello World', 'World') 等价于 LOCATE('World', 'Hello World')

-- 不存在时返回 0
SELECT LOCATE('xyz', 'Hello World');         -- 结果: 0

-- 实际应用：判断字符串是否包含某子串
SELECT
    email,
    CASE WHEN LOCATE('@gmail.com', email) > 0 THEN 'Gmail' ELSE 'Other' END AS provider
FROM users;

-- 从 URL 中提取协议
SELECT
    url,
    CASE WHEN LOCATE('https://', url) = 1 THEN 'HTTPS' ELSE 'HTTP' END AS protocol
FROM web_logs;

19.6.4 FIELD(str, str1, str2, ...) 和 FIND_IN_SET(str, strlist)

sql

-- FIELD：返回 str 在后续参数列表中的索引位置（从 1 开始）
SELECT FIELD('b', 'a', 'b', 'c', 'd');       -- 结果: 2
SELECT FIELD('z', 'a', 'b', 'c');            -- 结果: 0（不存在）

-- 强大用途：自定义排序
SELECT * FROM products
ORDER BY FIELD(status, 'active', 'pending', 'inactive', 'deleted');
-- 结果按 active → pending → inactive → deleted 的顺序排列

-- 多级自定义排序
SELECT * FROM orders
ORDER BY FIELD(priority, 'urgent', 'high', 'medium', 'low'),
         FIELD(status, 'pending', 'processing', 'shipped', 'delivered');

-- FIND_IN_SET：在逗号分隔的字符串列表中查找
SELECT FIND_IN_SET('b', 'a,b,c,d');          -- 结果: 2
SELECT FIND_IN_SET('z', 'a,b,c,d');          -- 结果: 0（不存在）

-- 注意：FIND_IN_SET 不会去除空格！
SELECT FIND_IN_SET('b', 'a, b, c');          -- 结果: 0（因为 ' b' 不等于 'b'）

-- 实际应用：查询用户是否拥有某项权限（权限以逗号分隔存储）
SELECT * FROM users WHERE FIND_IN_SET('admin', roles) > 0;

-- FIELD vs FIND_IN_SET
-- FIELD: 参数是独立的值
-- FIND_IN_SET: 参数是一个逗号分隔的字符串

-- 对比演示
SELECT FIELD('c', 'a', 'b', 'c', 'd');       -- 结果: 3
SELECT FIND_IN_SET('c', 'a,b,c,d');          -- 结果: 3

19.7 字符串填充与重复函数

19.7.1 LPAD(str, len, padstr) 和 RPAD(str, len, padstr)

左填充和右填充：将 str 填充到指定长度 len。

sql

-- LPAD：左填充
SELECT LPAD('123', 6, '0');          -- 结果: 000123
SELECT LPAD('hello', 10, '*');       -- 结果: *****hello

-- RPAD：右填充
SELECT RPAD('123', 6, '0');          -- 结果: 123000
SELECT RPAD('hello', 10, '-');       -- 结果: hello-----

-- 如果 str 长度超过 len，则截断
SELECT LPAD('hello world', 5, '*');  -- 结果: hello（从右边截断）
SELECT RPAD('hello world', 5, '*');  -- 结果: hello（从右边截断）

-- 实际应用：生成固定长度的订单号
SELECT CONCAT('ORD', LPAD(id, 8, '0')) AS order_no
FROM orders;

-- 生成进度条（控制台应用）
SELECT CONCAT('[', RPAD('=', progress * 20 / 100, '='),
              RPAD('', 20 - progress * 20 / 100, ' '), ']') AS progress_bar
FROM tasks;

19.7.2 REPEAT(str, count) 和 REVERSE(str)

sql

-- REPEAT：重复字符串 count 次
SELECT REPEAT('ab', 3);              -- 结果: ababab
SELECT REPEAT('*', 10);              -- 结果: **********

-- 如果 count <= 0，返回空字符串
SELECT REPEAT('ab', 0);              -- 结果: ''
SELECT REPEAT('ab', -1);             -- 结果: ''

-- 实际应用：生成分隔线
SELECT REPEAT('-', 50) AS divider;

-- REVERSE：反转字符串
SELECT REVERSE('Hello');             -- 结果: olleH
SELECT REVERSE('上海自来水来自海上');  -- 结果: 上海自来水来自海上（中文回文）

-- 实际应用：检查回文
SELECT word, word = REVERSE(word) AS is_palindrome
FROM words;

-- 从文件路径中提取扩展名（配合其他函数）
SELECT
    filepath,
    REVERSE(LEFT(REVERSE(filepath), LOCATE('.', REVERSE(filepath)) - 1)) AS ext
FROM files;

19.8 数值函数

19.8.1 四舍五入与截断

sql

-- ROUND(num, [decimals])：四舍五入到指定小数位
SELECT ROUND(3.14159, 2);          -- 结果: 3.14
SELECT ROUND(3.14159, 0);          -- 结果: 3
SELECT ROUND(3.14159);             -- 结果: 3（默认为 0 位小数）
SELECT ROUND(3.5);                 -- 结果: 4（.5 向上舍入）
SELECT ROUND(3.14159, 3);          -- 结果: 3.142
SELECT ROUND(12345.6789, -2);      -- 结果: 12300（负数表示整数位的舍入）

-- CEIL(num) / CEILING(num)：向上取整（天花板）
SELECT CEIL(3.1);                  -- 结果: 4
SELECT CEIL(3.9);                  -- 结果: 4
SELECT CEIL(-3.1);                 -- 结果: -3（注意：-3 > -3.1）
SELECT CEILING(3.5);               -- 结果: 4（同义词）

-- FLOOR(num)：向下取整（地板）
SELECT FLOOR(3.1);                 -- 结果: 3
SELECT FLOOR(3.9);                 -- 结果: 3
SELECT FLOOR(-3.1);                -- 结果: -4（注意：-4 < -3.1）

-- TRUNCATE(num, decimals)：截断到指定小数位（不进行四舍五入）
SELECT TRUNCATE(3.14159, 2);       -- 结果: 3.14
SELECT TRUNCATE(3.14159, 0);       -- 结果: 3
SELECT TRUNCATE(3.999, 0);         -- 结果: 3（与 ROUND 的区别！）
SELECT TRUNCATE(3.14159, 3);       -- 结果: 3.141

19.8.2 基本数学运算

sql

-- ABS(num)：绝对值
SELECT ABS(-10);                   -- 结果: 10
SELECT ABS(3.14 - 5.28);           -- 结果: 2.14

-- MOD(N, M) / N % M：取模（取余数）
SELECT MOD(10, 3);                 -- 结果: 1
SELECT 10 % 3;                     -- 结果: 1
SELECT MOD(10.5, 3);               -- 结果: 1.5（支持小数）
SELECT MOD(-10, 3);                -- 结果: -1

-- POWER(num, exp) / POW(num, exp)：幂运算
SELECT POWER(2, 10);               -- 结果: 1024
SELECT POW(2, 3);                  -- 结果: 8
SELECT POWER(2, -1);               -- 结果: 0.5

-- SQRT(num)：平方根
SELECT SQRT(16);                   -- 结果: 4
SELECT SQRT(2);                    -- 结果: 1.4142135623730951
SELECT SQRT(-1);                   -- 结果: NULL（负数平方根返回 NULL）

-- SIGN(num)：符号函数
SELECT SIGN(-10);                  -- 结果: -1
SELECT SIGN(0);                    -- 结果: 0
SELECT SIGN(10);                   -- 结果: 1

-- RAND([seed])：随机数 [0, 1)
SELECT RAND();                     -- 结果: 0.1234567890...（每次不同）
SELECT RAND(42);                   -- 结果: 固定的伪随机数（seed 相同时结果相同）

-- 生成指定范围的随机整数
SELECT FLOOR(RAND() * 100);        -- 0 ~ 99
SELECT FLOOR(RAND() * 100) + 1;    -- 1 ~ 100
SELECT FLOOR(RAND() * (max - min + 1)) + min;  -- min ~ max

-- 随机抽取数据
SELECT * FROM products ORDER BY RAND() LIMIT 10;  -- 随机抽取 10 条

-- 可重复的随机（使用 seed）
SELECT RAND(1), RAND(1), RAND(2);
-- 使用相同 seed 的 RAND 返回相同值

19.8.3 极值函数

sql

-- GREATEST(val1, val2, ...)：返回参数列表中的最大值
SELECT GREATEST(10, 20, 5, 30);    -- 结果: 30
SELECT GREATEST('a', 'b', 'c');    -- 结果: c（字符串按字典序比较）
SELECT GREATEST('2024-01-01', '2024-06-15', '2023-12-31');  -- 结果: 2024-06-15

-- LEAST(val1, val2, ...)：返回参数列表中的最小值
SELECT LEAST(10, 20, 5, 30);       -- 结果: 5
SELECT LEAST('a', 'b', 'c');       -- 结果: a

-- **重要警告**：如果任一参数为 NULL，GREATEST 和 LEAST 返回 NULL！
SELECT GREATEST(10, NULL, 30);     -- 结果: NULL
SELECT LEAST(10, NULL, 30);        -- 结果: NULL

-- 解决方案：使用 COALESCE 处理 NULL
SELECT GREATEST(COALESCE(a, 0), COALESCE(b, 0), COALESCE(c, 0))
FROM scores;

-- 实际应用：价格比较
SELECT product_name, GREATEST(price, discount_price) AS higher_price
FROM products;

-- 取多列中的最小非 NULL 日期
SELECT LEAST(COALESCE(date1, '9999-12-31'),
             COALESCE(date2, '9999-12-31'),
             COALESCE(date3, '9999-12-31')) AS earliest_date
FROM events;

19.9 FORMAT 函数

FORMAT 将数字格式化为带千位分隔符和指定位小数的字符串。

sql

-- 基本用法
SELECT FORMAT(1234567.89, 2);       -- 结果: 1,234,567.89
SELECT FORMAT(1234567.89, 0);       -- 结果: 1,234,568（会四舍五入！）
SELECT FORMAT(1000000, 2);          -- 结果: 1,000,000.00

-- 注意：返回值是字符串，不是数字！
SELECT FORMAT(1234.56, 2) + 0;     -- 隐式转换后可以做运算

-- 实际应用：财务报表格式化
SELECT
    account_name,
    CONCAT('$', FORMAT(balance, 2)) AS formatted_balance
FROM accounts;

19.10 综合实战示例

sql

-- 场景：清洗和格式化用户导入数据
SELECT
    id,
    -- 去除空格并统一大小写
    TRIM(LOWER(email)) AS clean_email,

    -- 拼接全名（处理可能的 NULL 中间名）
    CONCAT_WS(' ', first_name, middle_name, last_name) AS full_name,

    -- 手机号脱敏
    CONCAT(LEFT(phone, 3), '****', RIGHT(phone, 4)) AS masked_phone,

    -- 提取身份证中的出生日期
    CASE WHEN CHAR_LENGTH(id_card) = 18
         THEN SUBSTRING(id_card, 7, 8)
         ELSE NULL END AS birth_date,

    -- 格式化金额
    CONCAT('¥', FORMAT(balance, 2)) AS balance_formatted,

    -- 判断邮箱域名
    CASE WHEN LOCATE('@gmail.com', LOWER(email)) > 0 THEN 'Gmail'
         WHEN LOCATE('@qq.com', LOWER(email)) > 0 THEN 'QQ'
         ELSE 'Other' END AS email_provider,

    -- 提取域名
    SUBSTRING_INDEX(email, '@', -1) AS domain

FROM user_import
WHERE LENGTH(TRIM(email)) > 0;  -- 排除空邮箱

常见错误

CONCAT 遇到 NULL 返回 NULL
- 错误：SELECT CONCAT(first_name, ' ', middle_name, ' ', last_name) FROM users; 当 middle_name 为 NULL 时返回 NULL。
- 原因：CONCAT 中任一参数为 NULL 则结果为 NULL。
- 解决：使用 CONCAT_WS(' ', first_name, middle_name, last_name) 或 CONCAT(first_name, ' ', COALESCE(middle_name, ''), ' ', last_name)。
混淆 LENGTH 和 CHAR_LENGTH
- 错误：用 LENGTH('你好') 判断中文字符串长度，得到 6 而非预期的 2。
- 原因：LENGTH 返回字节数，utf8mb4 中每个汉字占 3 字节。
- 解决：验证字符串的"字数"时应使用 CHAR_LENGTH()。
TRUNCATE 与 ROUND 的混淆
- 错误：TRUNCATE(3.999, 0) 返回 3，而 ROUND(3.999, 0) 返回 4。
- 原因：TRUNCATE 只截断不四舍五入。
- 解决：根据业务需求选择正确的函数。财务计算通常用 ROUND，取整通常用 FLOOR/CEIL。
GREATEST/LEAST 在遇到 NULL 时返回 NULL
- 错误：SELECT GREATEST(score1, score2, score3) FROM exams; 可能返回 NULL。
- 原因：任一参数为 NULL 则结果为 NULL。
- 解决：使用 SELECT GREATEST(COALESCE(score1, 0), COALESCE(score2, 0), COALESCE(score3, 0)) FROM exams;。
FIND_IN_SET 中的空格问题
- 错误：FIND_IN_SET('admin', 'user, admin, manager') 返回 0。
- 原因：FIND_IN_SET 不会自动去除空格，' admin' 不等于 'admin'。
- 解决：使用 FIND_IN_SET('admin', REPLACE('user, admin, manager', ' ', '')) 或确保分隔列表无空格。

本章练习

数据清洗练习：创建一个用户数据导入表，包含带有前后空格、大小写不一致、电话号码格式混乱的数据。编写 SQL 语句一次性完成所有数据清洗：去除空格、统一大小写、格式化电话号码、提取邮箱域名。
字符串长度验证：编写一个查询，验证 utf8mb4 字符集中英文、中文、Emoji 的 LENGTH 和 CHAR_LENGTH 差异。解释为什么在设置 VARCHAR(255) 时，存中文最多能存多少个汉字（假设全部 3 字节汉字）。
自定义排序：有一个 products 表，status 列值包括 'discontinued', 'out_of_stock', 'active', 'pre_order'。请使用 FIELD 函数实现按 'active' → 'pre_order' → 'out_of_stock' → 'discontinued' 的顺序排序。
电话号码格式化：给定未格式化的手机号 '13812345678'，使用字符串函数（INSERT、SUBSTRING、CONCAT 等）将其格式化为 '138-1234-5678'。尝试至少两种不同的实现方式。
综合报表生成：编写一个查询，从 orders 和 customers 表生成一份客户对账单，包含：客户全名（CONCAT_WS）、脱敏手机号、格式化金额（FORMAT）、订单日期（使用字符串函数格式化为 YYYY/MM/DD）、按订单金额从大到小排序、并过滤掉已取消的订单。
数值函数综合应用：有一张产品表包含 price 和 discount_rate 列。编写查询计算：折后价（保留 2 位小数并四舍五入）、折后价向上取整、折后价截断取整、价格等级（使用 SIGN 和 CASE 判断正负）、以及使用 GREATEST 确保折后价不低于成本价。

第十九章 字符串与数值函数 ​

本章目标 ​

前置知识 ​

19.1 字符串连接函数 ​

19.1.1 CONCAT(str1, str2, ...) ​

19.1.2 CONCAT_WS(separator, str1, str2, ...) ​

19.1.3 CONCAT 与 CONCAT_WS 对比 ​

19.2 字符串长度函数 ​

19.2.1 LENGTH(str) —— 字节长度 ​

19.2.2 CHAR_LENGTH(str) / CHARACTER_LENGTH(str) —— 字符长度 ​

19.2.3 核心对比演示 ​

19.3 字符串截取函数 ​

19.3.1 LEFT(str, len) 和 RIGHT(str, len) ​

19.3.2 SUBSTRING(str, pos, len) / SUBSTR(str, pos, len) ​

19.3.3 SUBSTRING_INDEX(str, delim, count) ​

19.4 字符串修剪函数 ​

19.4.1 TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) ​

19.4.2 LTRIM(str) 和 RTRIM(str) ​

19.5 大小写转换函数 ​

19.6 字符串查找与替换函数 ​

19.6.1 REPLACE(str, from_str, to_str) ​

19.6.2 INSERT(str, pos, len, newstr) ​

19.6.3 查找位置函数 ​

19.6.4 FIELD(str, str1, str2, ...) 和 FIND_IN_SET(str, strlist) ​

19.7 字符串填充与重复函数 ​

19.7.1 LPAD(str, len, padstr) 和 RPAD(str, len, padstr) ​

19.7.2 REPEAT(str, count) 和 REVERSE(str) ​

19.8 数值函数 ​

19.8.1 四舍五入与截断 ​

19.8.2 基本数学运算 ​

19.8.3 极值函数 ​

19.9 FORMAT 函数 ​

19.10 综合实战示例 ​

常见错误 ​

本章练习 ​