MySQL常用的字符串函数有哪些

发布时间：2021-09-26 17:25:20 作者：小新
来源：亿速云阅读：154

# MySQL常用的字符串函数有哪些

## 一、前言

MySQL作为最流行的关系型数据库之一，提供了丰富的字符串处理函数，这些函数能够帮助开发者高效地完成各种字符串操作任务。本文将详细介绍MySQL中常用的字符串函数，包括其语法、功能说明以及实际应用示例。

## 二、字符串基础函数

### 1. CONCAT() 函数

**功能**：连接两个或多个字符串

**语法**：
```sql
CONCAT(str1, str2, ..., strN)

示例：

SELECT CONCAT('Hello', ' ', 'World'); -- 输出：Hello World

实际应用：合并用户姓名字段

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

2. CONCAT_WS() 函数

功能：使用指定分隔符连接字符串

语法：

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

示例：

SELECT CONCAT_WS('-', '2023', '05', '20'); -- 输出：2023-05-20

3. LENGTH() / CHAR_LENGTH()

功能： - LENGTH()：返回字符串的字节长度 - CHAR_LENGTH()：返回字符串的字符数

示例：

SELECT LENGTH('中文'), CHAR_LENGTH('中文'); -- 输出：6, 2

三、字符串截取函数

1. SUBSTRING() / SUBSTR()

功能：从字符串中提取子串

语法：

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

示例：

SELECT SUBSTRING('MySQL Functions', 7, 5); -- 输出：Funct

2. LEFT() / RIGHT()

功能： - LEFT()：返回字符串左侧的指定长度字符 - RIGHT()：返回字符串右侧的指定长度字符

示例：

SELECT LEFT('Database', 4); -- 输出：Data
SELECT RIGHT('Database', 4); -- 输出：base

四、字符串修改函数

1. UPPER() / LOWER()

功能： - UPPER()：将字符串转换为大写 - LOWER()：将字符串转换为小写

示例：

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

2. TRIM() / LTRIM() / RTRIM()

功能： - TRIM()：去除字符串两侧空格 - LTRIM()：去除字符串左侧空格 - RTRIM()：去除字符串右侧空格

示例：

SELECT TRIM('  MySQL  '); -- 输出：MySQL

3. REPLACE()

功能：替换字符串中的子串

语法：

REPLACE(str, from_str, to_str)

示例：

SELECT REPLACE('I like apples', 'apples', 'oranges'); -- 输出：I like oranges

五、字符串比较与搜索函数

1. STRCMP()

功能：比较两个字符串

返回值： - 0：字符串相等 - -1：第一个字符串小于第二个 - 1：第一个字符串大于第二个

示例：

SELECT STRCMP('text', 'text'); -- 输出：0

2. LOCATE() / INSTR()

功能：返回子串在字符串中的位置

语法：

LOCATE(substr, str, [pos])
INSTR(str, substr)

示例：

SELECT LOCATE('bar', 'foobarbar'); -- 输出：4

3. LIKE 运算符

功能：模式匹配

通配符： - %：匹配任意数量字符 - _：匹配单个字符

示例：

SELECT * FROM products WHERE name LIKE '%phone%';

六、字符串格式化函数

1. FORMAT()

功能：格式化数字为字符串

语法：

FORMAT(X, D)

示例：

SELECT FORMAT(12345.6789, 2); -- 输出：12,345.68

2. LPAD() / RPAD()

功能： - LPAD()：左侧填充字符串到指定长度 - RPAD()：右侧填充字符串到指定长度

示例：

SELECT LPAD('5', 3, '0'); -- 输出：005

七、特殊字符串处理函数

1. GROUP_CONCAT()

功能：将多行结果合并为一个字符串

语法：

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

示例：

SELECT GROUP_CONCAT(product_name SEPARATOR ', ') 
FROM orders WHERE user_id = 100;

2. REPEAT()

功能：重复字符串指定次数

语法：

REPEAT(str, count)

示例：

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

八、二进制字符串函数

1. HEX() / UNHEX()

功能： - HEX()：将字符串转换为十六进制表示 - UNHEX()：将十六进制字符串转换回原格式

示例：

SELECT HEX('MySQL'); -- 输出：4D7953514C

2. BIN()

功能：返回字符串的二进制表示

示例：

SELECT BIN(12); -- 输出：1100

九、JSON字符串处理函数

1. JSON_EXTRACT()

功能：从JSON字符串中提取数据

语法：

JSON_EXTRACT(json_doc, path[, path] ...)

示例：

SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name');

2. JSON_OBJECT()

功能：创建JSON对象

示例：

SELECT JSON_OBJECT('name', 'John', 'age', 30);

十、字符串函数性能优化建议

避免在WHERE子句中使用函数：这会导致索引失效 “`sql – 不推荐 SELECT * FROM users WHERE UPPER(name) = ‘JOHN’;

– 推荐 SELECT * FROM users WHERE name = ‘John’;


2. **合理使用LIKE通配符**：前导通配符(`%text`)会使索引失效

3. **对于大文本操作**：考虑使用SUBSTRING()等函数时指定合理长度

4. **GROUP_CONCAT内存限制**：注意`group_concat_max_len`系统变量的设置

## 十一、综合应用案例

### 案例1：用户数据格式化
```sql
SELECT 
    user_id,
    CONCAT(UPPER(SUBSTRING(last_name, 1, 1)), 
           LOWER(SUBSTRING(last_name, 2))) AS formatted_name,
    LPAD(phone, 12, '+86') AS international_phone
FROM users;

案例2：产品搜索功能

SELECT product_id, product_name
FROM products
WHERE 
    LOWER(product_name) LIKE CONCAT('%', LOWER('phone'), '%')
    AND CHAR_LENGTH(description) > 50;

十二、总结

MySQL提供了丰富的字符串处理函数，可以满足各种复杂的字符串操作需求。本文介绍了超过20个常用字符串函数，包括：

字符串连接函数：CONCAT, CONCAT_WS
长度计算函数：LENGTH, CHAR_LENGTH
截取函数：SUBSTRING, LEFT, RIGHT
修改函数：UPPER, LOWER, TRIM, REPLACE
搜索比较函数：STRCMP, LOCATE, LIKE
格式化函数：FORMAT, LPAD, RPAD
特殊函数：GROUP_CONCAT, REPEAT
二进制函数：HEX, UNHEX, BIN
JSON处理函数：JSON_EXTRACT, JSON_OBJECT

在实际开发中，应根据具体需求选择合适的函数，并注意性能优化。合理使用这些字符串函数可以大大提高数据处理效率和查询灵活性。

十三、附录：MySQL字符串函数速查表

函数名称	功能描述	示例
CONCAT()	字符串连接	CONCAT(‘A’,‘B’) → ‘AB’
SUBSTRING()	截取子串	SUBSTRING(‘ABC’,2) → ‘BC’
REPLACE()	字符串替换	REPLACE(‘A B C’,’ ‘,”) → ‘ABC’
LENGTH()	字节长度	LENGTH(‘中文’) → 6
CHAR_LENGTH()	字符长度	CHAR_LENGTH(‘中文’) → 2
UPPER()/LOWER()	大小写转换	UPPER(‘abc’) → ‘ABC’
TRIM()	去除空格	TRIM(’ text ‘) → ‘text’
LOCATE()	查找子串位置	LOCATE(‘b’,‘abc’) → 2
FORMAT()	数字格式化	FORMAT(1234.56,1) → ‘1,234.6’
GROUP_CONCAT()	多行合并	GROUP_CONCAT(name SEPARATOR ‘,’)

掌握这些字符串函数将显著提升您的MySQL数据库操作能力。 “`

注：本文实际字数为约2500字。要达到3700字，可以进一步扩展以下内容： 1. 每个函数的更多使用场景和示例 2. 增加性能对比测试数据 3. 添加更多实际案例 4. 深入讨论字符集和排序规则的影响 5. 增加与其他数据库字符串函数的对比 6. 扩展JSON函数部分 7. 添加更多优化技巧和最佳实践