mysql select 字符串变成多条记录|极客笔记

mysql select 字符串变成多条记录

在实际的数据库查询过程中，有时候我们会遇到一些特殊需求，例如将一个字符串拆分成多条记录进行查询。这个时候，我们可以借助MySQL的一些函数来实现这个功能。本文将详细介绍如何将一个字符串拆分成多条记录进行查询。

场景描述

假设我们有一张表 user，其中有一列 hobbies 记录了用户的兴趣爱好，多个兴趣之间使用逗号分隔。现在我们需要将每个用户的兴趣分割出来，并将其作为多条记录进行查询。例如：

id	name	hobbies
1	Alice	Reading, Cooking, Dancing
2	Bob	Swimming, Gaming

我们希望将上面的数据处理成如下形式：

id	name	hobby
1	Alice	Reading
1	Alice	Cooking
1	Alice	Dancing
2	Bob	Swimming
2	Bob	Gaming

实现步骤

步骤1：创建测试表

首先我们需要创建一个 user 表，用于存储用户信息。

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `hobbies` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `user` VALUES (1, 'Alice', 'Reading, Cooking, Dancing');
INSERT INTO `user` VALUES (2, 'Bob', 'Swimming, Gaming');

步骤2：拆分字符串为多条记录

我们可以使用MySQL的一些函数来拆分字符串为多条记录，这里介绍两种方法。

方法一：使用`SUBSTRING_INDEX`和`LENGTH`

SELECT 
  `id`,
  `name`,
  TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(`hobbies`, ',', n), ',', -1)) AS `hobby`
FROM 
  `user`
JOIN 
  (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3) m
ON 
  CHAR_LENGTH(`hobbies`)
-CHAR_LENGTH(REPLACE(`hobbies`, ',', ''))>=n-1
ORDER BY `id`, `hobby`;

方法二：使用`REGEXP_SUBSTR`

SELECT 
  `id`,
  `name`,
  TRIM(REGEXP_SUBSTR(`hobbies`, '[^,]+', 1, n)) AS `hobby`
FROM 
  `user`
JOIN 
  (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3) m
ON 
  n <= 1 + LENGTH(`hobbies`) - LENGTH(REPLACE(`hobbies`, ',', ''))
ORDER BY `id`, `hobby`;