【MySQL基础-18】MySQL字符函数详解：高效处理文本数据的利器

本文链接：https://blog.youkuaiyun.com/weixin_39033358/article/details/146806229

在数据库操作中，文本数据处理占据了很大比重。MySQL提供了一系列强大的字符函数，能够帮助我们高效地进行字符串操作、转换和分析。本文将全面介绍MySQL中最实用、最常用的字符函数，并通过实际示例展示它们的应用场景。

1. 基础字符串操作函数

1.1 CONCAT() - 字符串连接

SELECT CONCAT('Hello', ' ', 'World'); -- 输出: Hello World
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

CONCAT()函数可以连接两个或多个字符串，在处理全名、地址拼接等场景非常有用。

1.2 CONCAT_WS() - 带分隔符的连接

SELECT CONCAT_WS('-', '2023', '05', '15'); -- 输出: 2023-05-15
SELECT CONCAT_WS(', ', last_name, first_name) FROM customers;

CONCAT_WS()（With Separator）在连接字符串时自动添加指定的分隔符，避免了重复输入分隔符的麻烦。

1.3 LENGTH() / CHAR_LENGTH()

SELECT LENGTH('MySQL'); -- 返回字节数: 5
SELECT CHAR_LENGTH('MySQL'); -- 返回字符数: 5
SELECT CHAR_LENGTH('数据库'); -- 返回字符数: 3 (UTF-8中一个中文占3个字节)

注意：LENGTH()返回字节数，而CHAR_LENGTH()返回字符数，在处理多字节字符时差异明显。

2. 字符串截取与定位函数

2.1 SUBSTRING() / SUBSTR()

SELECT SUBSTRING('MySQL Database', 7); -- 输出: Database
SELECT SUBSTRING('MySQL Database', 1, 5); -- 输出: MySQL

2.2 LEFT() 和 RIGHT()

SELECT LEFT('MySQL', 2); -- 输出: My
SELECT RIGHT('MySQL', 3); -- 输出: SQL

2.3 LOCATE() / INSTR() / POSITION()

SELECT LOCATE('SQL', 'MySQL'); -- 输出: 3
SELECT INSTR('MySQL', 'SQL'); -- 输出: 3
SELECT POSITION('SQL' IN 'MySQL'); -- 输出: 3

这些函数用于查找子串位置，在数据清洗和提取特定内容时非常有用。

3. 字符串修改函数

3.1 UPPER() / LOWER()

SELECT UPPER('MySQL'); -- 输出: MYSQL
SELECT LOWER('MySQL'); -- 输出: mysql

3.2 TRIM() / LTRIM() / RTRIM()

SELECT TRIM('  MySQL  '); -- 输出: MySQL
SELECT LTRIM('  MySQL'); -- 输出: MySQL
SELECT RTRIM('MySQL  '); -- 输出: MySQL

3.3 REPLACE()

SELECT REPLACE('MySQL Database', 'Database', 'DB'); -- 输出: MySQL DB
UPDATE products SET description = REPLACE(description, 'old', 'new');

REPLACE()函数在批量更新数据时特别有用。

3.4 INSERT()

SELECT INSERT('MySQL', 3, 0, 'New'); -- 输出: MyNewSQL

4. 格式化与填充函数

4.1 LPAD() / RPAD()

SELECT LPAD('7', 3, '0'); -- 输出: 007
SELECT RPAD('MySQL', 10, '*'); -- 输出: MySQL*****

4.2 FORMAT()

SELECT FORMAT(1234567.89, 2); -- 输出: 1,234,567.89

5. 高级字符串处理函数

5.1 REPEAT()

SELECT REPEAT('MySQL ', 3); -- 输出: MySQL MySQL MySQL

5.2 REVERSE()

SELECT REVERSE('MySQL'); -- 输出: LQSyM

5.3 ELT() 和 FIELD()

SELECT ELT(2, 'Apple', 'Banana', 'Cherry'); -- 输出: Banana
SELECT FIELD('Banana', 'Apple', 'Banana', 'Cherry'); -- 输出: 2

6. 实用案例集锦

6.1 案例1：提取电子邮件域名

SELECT 
    email,
    SUBSTRING(email, LOCATE('@', email) + 1) AS domain
FROM users;

6.2 案例2：电话号码格式化

UPDATE contacts 
SET phone = CONCAT_WS('-', 
    SUBSTRING(phone, 1, 3),
    SUBSTRING(phone, 4, 3),
    SUBSTRING(phone, 7)
)
WHERE LENGTH(phone) = 10;

6.3 案例3：生成随机密码

SELECT CONCAT(
    CHAR(FLOOR(65 + RAND() * 26)),
    CHAR(FLOOR(97 + RAND() * 26)),
    FLOOR(RAND() * 10),
    CHAR(FLOOR(33 + RAND() * 15)),
    SUBSTRING(MD5(RAND()), 1, 6)
) AS random_password;

7. 性能优化建议

避免在WHERE子句中使用函数：这会导致索引失效

-- 不推荐 (索引失效)
SELECT * FROM users WHERE UPPER(name) = 'JOHN';

-- 推荐 (使用函数索引)
SELECT * FROM users WHERE name = 'JOHN' COLLATE utf8mb4_general_ci;

考虑使用存储过程处理复杂的字符串操作，减少网络传输
对于大量数据，考虑在应用层处理字符串操作

8. 结语

MySQL的字符函数为数据处理提供了强大的工具集。掌握这些函数能够显著提高数据操作的效率和灵活性。在实际应用中，应根据具体需求选择合适的函数组合，并注意性能影响。希望本文能帮助您在数据库工作中更加得心应手地处理各种字符串操作需求。

附录：MySQL字符函数速查表

函数	描述	示例
CONCAT()	连接字符串	CONCAT(‘My’, ‘SQL’) → ‘MySQL’
SUBSTRING()	截取子串	SUBSTRING(‘MySQL’,3,2) → ‘SQ’
REPLACE()	替换字符串	REPLACE(‘MySQL’,‘SQL’,‘DB’) → ‘MyDB’
TRIM()	去除空格	TRIM(’ MySQL ') → ‘MySQL’
UPPER()/LOWER()	大小写转换	UPPER(‘MySQL’) → ‘MYSQL’
LENGTH()	字节长度	LENGTH(‘数据库’) → 9 (UTF-8)
CHAR_LENGTH()	字符长度	CHAR_LENGTH(‘数据库’) → 3
LOCATE()	查找子串位置	LOCATE(‘SQL’,‘MySQL’) → 3
FORMAT()	数字格式化	FORMAT(1234567.89,2) → ‘1,234,567.89’