MySQL常用函数详解之字符串函数

字符串是数据处理中最常见的数据类型之一，从存储用户姓名、地址到处理文本内容、日志信息，字符串数据贯穿于各种业务场景，MySQL提供了丰富的字符串函数帮助我们高效地对字符串进行处理、转换和分析。本文我将全面且深入地介绍MySQL常用的字符串函数，并结合具体示例和场景，帮你熟练掌握这些实用工具。

一、字符串函数概述

1.1 字符串函数的作用

MySQL字符串函数能够实现对字符串的多种操作，包括但不限于：

• 字符串的拼接、截取、替换
• 大小写转换、去空格
• 字符串查找、匹配与统计
• 字符串格式化与加密
  这些函数极大地增强了SQL语句对字符串数据的处理能力，使开发者可以在数据库层面完成复杂的字符串操作，减少应用层的处理压力。

1.2 字符串函数分类

根据功能特性，MySQL字符串函数可大致分为以下几类：

• 字符串长度与统计函数
• 字符串截取与提取函数
• 字符串连接与拆分函数
• 字符串查找与匹配函数
• 字符串转换与格式化函数
• 字符串加密与解密函数

二、字符串长度与统计函数

2.1 CHAR_LENGTH(str)

CHAR_LENGTH(str)函数用于返回字符串str的字符数，一个汉字、字母、数字等都算作一个字符。
示例：查询employees表中last_name字段的字符长度

SELECT last_name, CHAR_LENGTH(last_name) AS length
FROM employees;

2.2 LENGTH(str)

LENGTH(str)函数返回字符串str的字节长度。在UTF8编码下，一个英文字母占1个字节，一个汉字占3个字节。
示例：查询products表中product_name字段的字节长度

SELECT product_name, LENGTH(product_name) AS byte_length
FROM products;

2.3 OCTET_LENGTH(str)

OCTET_LENGTH(str)与LENGTH(str)功能相同，都是返回字符串的字节长度。

SELECT OCTET_LENGTH('Hello, 世界') AS octet_length;

三、字符串截取与提取函数

3.1 SUBSTRING(str, pos)、SUBSTRING(str, pos, len)

SUBSTRING(str, pos)从字符串str的第pos个位置开始截取，直到字符串结尾；SUBSTRING(str, pos, len)从第pos个位置开始截取长度为len的子字符串。
示例：从customers表的phone_number字段中提取后4位号码

SELECT phone_number, SUBSTRING(phone_number, -4) AS last_four_digits
FROM customers;

3.2 LEFT(str, len)、RIGHT(str, len)

LEFT(str, len)返回字符串str最左边len个字符，RIGHT(str, len)返回最右边len个字符。
示例：获取orders表中order_number字段的前3位和后3位

SELECT order_number,LEFT(order_number, 3) AS first_three_digits,RIGHT(order_number, 3) AS last_three_digits
FROM orders;

3.3 MID(str, pos, len)

MID(str, pos, len)与SUBSTRING(str, pos, len)功能一致，从字符串str的第pos个位置开始截取长度为len的子字符串。

SELECT MID('MySQL字符串函数', 4, 6) AS extracted_substring;

四、字符串连接与拆分函数

4.1 CONCAT(str1, str2, …)

CONCAT(str1, str2, ...)用于将多个字符串连接成一个字符串。
示例：将employees表中的first_name和last_name连接成完整姓名

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

4.2 CONCAT_WS(separator, str1, str2, …)

CONCAT_WS(separator, str1, str2, ...)使用指定的分隔符separator连接多个字符串，遇到NULL值时会跳过，不会返回NULL。
示例：用逗号连接products表中的product_name和category字段

SELECT CONCAT_WS(', ', product_name, category) AS product_info
FROM products;

4.3 GROUP_CONCAT(expr)

GROUP_CONCAT(expr)将分组后的结果集中的expr列值连接成一个字符串，默认用逗号分隔。
示例：查询每个department的所有employee_name

SELECT department, GROUP_CONCAT(employee_name) AS employees
FROM employees
GROUP BY department;

五、字符串查找与匹配函数

5.1 INSTR(str, substr)

INSTR(str, substr)返回子字符串substr在字符串str中第一次出现的位置，不存在则返回0。
示例：在customers表的email字段中查找@符号的位置

SELECT email, INSTR(email, '@') AS at_position
FROM customers;

5.2 LOCATE(substr, str)、POSITION(substr IN str)

这两个函数与INSTR(str, substr)功能相同，都是返回子字符串在字符串中首次出现的位置。

SELECT LOCATE('world', 'Hello, world!') AS locate_result,POSITION('world' IN 'Hello, world!') AS position_result;

5.3 LIKE、NOT LIKE

LIKE用于模糊匹配，配合通配符%（匹配任意字符序列）和_（匹配单个字符）使用；NOT LIKE则是取反。
示例：查询customers表中姓名以“张”开头的客户

SELECT *
FROM customers
WHERE customer_name LIKE '张%';

5.4 REGEXP、NOT REGEXP

REGEXP支持正则表达式匹配，NOT REGEXP为取反。
示例：查询products表中product_name包含数字的产品

SELECT product_name
FROM products
WHERE product_name REGEXP '[0-9]';

六、字符串转换与格式化函数

6.1 UPPER(str)、LOWER(str)

UPPER(str)将字符串转换为大写，LOWER(str)转换为小写。
示例：将employees表中的email字段统一转换为小写

SELECT LOWER(email) AS lower_email
FROM employees;

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

TRIM()用于去除字符串两端的空格或指定字符，LEADING去除开头，TRAILING去除结尾，BOTH（默认）去除两端。
示例：去除customers表中address字段两端的空格

SELECT TRIM(address) AS trimmed_address
FROM customers;

6.3 LPAD(str, len, padstr)、RPAD(str, len, padstr)

LPAD(str, len, padstr)在字符串str左侧填充padstr，使其长度达到len；RPAD(str, len, padstr)在右侧填充。
示例：将orders表中的order_id填充为8位，左侧补0

SELECT LPAD(order_id, 8, '0') AS padded_order_id
FROM orders;

七、字符串加密与解密函数

7.1 MD5(str)

MD5(str)计算字符串str的MD5哈希值，常用于密码加密（但安全性较低，不建议用于敏感数据）。

SELECT MD5('password') AS md5_hash;

7.2 SHA(str)、SHA2(str, hash_length)

SHA(str)计算字符串的SHA-1哈希值，SHA2(str, hash_length)计算SHA-2系列哈希值，hash_length可选224、256、384、512。

SELECT SHA('data'), SHA2('data', 256) AS sha2_256_hash;

八、实战案例

8.1 日志分析

在日志表logs中，字段log_message记录了操作信息，需求是提取其中的操作类型（以[开头，]结尾的部分）。

SELECT SUBSTRING(log_message, INSTR(log_message, '[') + 1, INSTR(log_message, ']') - INSTR(log_message, '[') - 1) AS operation_type
FROM logs;

8.2 用户数据清洗

对users表中的phone_number字段进行处理，去除非数字字符，并统一格式为XXX-XXXX-XXXX。

SELECT CONCAT(LEFT(TRIM(REGEXP_REPLACE(phone_number, '[^0-9]', '')), 3),'-',MID(TRIM(REGEXP_REPLACE(phone_number, '[^0-9]', '')), 4, 4),'-',RIGHT(TRIM(REGEXP_REPLACE(phone_number, '[^0-9]', '')), 4)) AS formatted_phone_number
FROM users;

若这篇内容帮到你，动动手指支持下！关注不迷路，干货持续输出！
ヾ(´∀ ˋ)ﾉヾ(´∀ ˋ)ﾉヾ(´∀ ˋ)ﾉヾ(´∀ ˋ)ﾉヾ(´∀ ˋ)ﾉ