网站导航
> 技术文档 > MySQL中的字符串分割函数_mysql split

MySQL中的字符串分割函数_mysql split

网友: 技术文档 2025-07-30 12:21:32

MySQL中的字符串分割函数

MySQL本身没有内置的SPLIT()函数，但可以通过其他方式实现字符串分割功能。以下是几种常见的方法：

1. SUBSTRING_INDEX函数

SUBSTRING_INDEX()是MySQL中最常用的字符串分割函数，它可以根据指定的分隔符从字符串中提取子串，语法如下：

SUBSTRING_INDEX(str, delim, count)
  • 含义：返回字符串 str 中按分隔符 delim 分割后的第 count 个子串。
  • str: 要分割的字符串
  • delim: 分隔符（可以是单个字符或多个字符）
  • count:
    • 正数：返回从左边开始第count个分隔符之前的所有内容
    • 负数：返回从右边开始第count个分隔符之后的所有内容

1、基本用法

-- 获取第一个逗号前的内容SELECT SUBSTRING_INDEX(\'apple,banana,orange\', \',\', 1); -- 结果: \'apple\'-- 获取最后一个逗号后的内容SELECT SUBSTRING_INDEX(\'apple,banana,orange\', \',\', -1); -- 结果: \'orange\'-- 获取前两个元素SELECT SUBSTRING_INDEX(\'apple,banana,orange\', \',\', 1) AS item1, SUBSTRING_INDEX(SUBSTRING_INDEX(\'apple,banana,orange\', \',\', 2), \',\', -1) AS item2;-- 结果: item1=\'apple\', item2=\'banana\'

2. 处理多字符分隔符

-- 使用多字符作为分隔符SELECT SUBSTRING_INDEX(\'apple||banana||orange\', \'||\', 2);-- 结果: \'apple||banana\'SELECT SUBSTRING_INDEX(\'apple||banana||orange\', \'||\', -1);-- 结果: \'orange\'
  • 找到第一个 || 在 apple||banana||orange 的 apple 之后，此时已找到1次分隔符
  • 找到第二个 || 在 banana 之后，此时已找到2次分隔符（达到count值）
  • 函数返回从开头到第二个 || 之前的所有内容：‘apple||banana’

3. 边界情况处理

-- 分隔符不存在时返回原字符串SELECT SUBSTRING_INDEX(\'apple_banana_orange\', \',\', 1);-- 结果: \'apple_banana_orange\'-- count超过实际分隔数时返回整个字符串SELECT SUBSTRING_INDEX(\'apple,banana\', \',\', 5);-- 结果: \'apple,banana\'-- 空字符串处理SELECT SUBSTRING_INDEX(\'\', \',\', 1);-- 结果: \'\'

2. 使用正则表达式:REGEXP_SUBSTR

MySQL 8.0及以上版本支持正则表达式函数：

-- 使用REGEXP_SUBSTR提取匹配的子串SELECT REGEXP_SUBSTR(\'apple,banana,orange\', \'[^,]+\', 1, 1) AS item1, REGEXP_SUBSTR(\'apple,banana,orange\', \'[^,]+\', 1, 2) AS item2;-- 结果: item1=\'apple\', item2=\'banana\'

3. 使用存储过程实现完整分割

如果需要将字符串完全分割成多行，可以创建存储过程：

DELIMITER //CREATE PROCEDURE split_string(IN input_string VARCHAR(1000), IN delimiter_char VARCHAR(1))BEGIN DECLARE temp_string VARCHAR(1000); DECLARE i INT DEFAULT 1; DECLARE item VARCHAR(1000); SET temp_string = input_string; WHILE LENGTH(temp_string) > 0 DO SET item = SUBSTRING_INDEX(temp_string, delimiter_char, 1); SELECT item AS split_result; SET temp_string = SUBSTRING(temp_string, LENGTH(item) + 2); IF LENGTH(temp_string) = 0 THEN LEAVE; END IF; SET i = i + 1; END WHILE;END //DELIMITER ;-- 调用存储过程CALL split_string(\'apple,banana,orange\', \',\');

4. 使用JSON函数(MySQL 5.7+)

MySQL 5.7及以上版本可以使用JSON函数处理字符串分割：

-- 将逗号分隔的字符串转为JSON数组SELECT JSON_UNQUOTE(JSON_EXTRACT(CONCAT(\'[\"\', REPLACE(\'apple,banana,orange\', \',\', \'\",\"\'), \'\"]\'), \'$[0]\')) AS item1, JSON_UNQUOTE(JSON_EXTRACT(CONCAT(\'[\"\', REPLACE(\'apple,banana,orange\', \',\', \'\",\"\'), \'\"]\'), \'$[1]\')) AS item2;-- 结果: item1=\'apple\', item2=\'banana\'

实际应用示例

假设有一个表格包含逗号分隔的标签字段：

CREATE TABLE products ( id INT, name VARCHAR(100), tags VARCHAR(255));INSERT INTO products VALUES (1, \'Laptop\', \'electronics,computer,premium\'),(2, \'Phone\', \'electronics,mobile\'),(3, \'Desk\', \'furniture,office\');-- 查询所有电子类产品SELECT * FROM products WHERE FIND_IN_SET(\'electronics\', tags) > 0;

注意：对于复杂的字符串分割需求，建议在应用层处理(如Python、Java等)，或者在数据库设计时就避免使用分隔符存储多个值(遵循第一范式)。

练习题

统计每种性别的人数

select SUBSTRING_INDEX (profile, \',\', -1) as gender, count(*) as numberfrom user_submit group by gender

提取博客URL中的用户名

select device_id, substring_index (blog_url, \"/\", -1) as user_namefrom user_submit

截取出年龄

select substring_index (substring_index (profile, \",\", 3), \",\", -1) as age, count(*) as numberfrom user_submitgroup by age

SQL83 商品id数据清洗统计

select # SUBSTRING_INDEX (order_id, \'_\', -1) as product_id, regexp_substr(order_id,\'p[0-9]+$\') as product_id, count(*) as cntfrom order_loggroup by product_idorder by cnt desc,product_id asc;
  • ‘p[0-9]+$’: 正则表达式模式
  • p: 匹配字母 “p”
  • [0-9]+: 匹配一个或多个数字(0-9)
  • $: 匹配字符串的结尾
网络标签：

相关问题