MySQL拆分逗号|极客笔记

MySQL拆分逗号

在MySQL中，有时候我们需要对存储在数据库中的逗号分隔的多个值进行拆分，以便对每个值进行操作或查询。例如，我们可能需要从一个存储了学生ID的字段中拆分出每个学生的ID，以便单独查询每个学生的信息。在本文中，我们将探讨如何在MySQL中拆分逗号分隔的值。

方法一：使用SUBSTRING_INDEX函数

MySQL提供了一个函数SUBSTRING_INDEX(str, delim, count)，它可以在一个字符串中查找分隔符，并返回从字符串开头到第count个分隔符或从最后一个分隔符到字符串末尾的子字符串。我们可以利用这个函数来拆分逗号分隔的值。

示例

假设我们有一个表students，其中有一个字段ids存储着多个学生的ID，如下所示：

+----+-------------+
| id | ids         |
+----+-------------+
| 1  | 101,102,103 |
| 2  | 104,105     |
| 3  | 106,107,108 |
+----+-------------+

现在我们想将每个学生的ID分开，可以使用以下SQL语句：

SELECT 
  id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(s.ids, ',', n.n), ',', -1) as student_id
FROM 
  students s
JOIN 
  (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3) n
ON 
  LENGTH(s.ids) - LENGTH(REPLACE(s.ids, ',', '')) >= n - 1

上述SQL语句中的n是一个虚拟表，包含了我们要拆分的逗号分隔的值的个数。通过这个查询，我们可以得到如下结果：

+----+-----------+
| id | student_id|
+----+-----------+
| 1  | 101       |
| 1  | 102       |
| 1  | 103       |
| 2  | 104       |
| 2  | 105       |
| 3  | 106       |
| 3  | 107       |
| 3  | 108       |
+----+-----------+

方法二：自定义函数

除了使用内置函数SUBSTRING_INDEX外，我们还可以使用自定义函数来拆分逗号分隔的值。以下是一个示例自定义函数SPLIT_STRING，它接受一个字符串和分隔符作为参数，返回一个包含拆分后字符串的表：

DELIMITER //
CREATE FUNCTION SPLIT_STRING(str VARCHAR(255), delim VARCHAR(1))
RETURNS VARCHAR(255)
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE result TEXT DEFAULT '';
  DECLARE str_length INT;

  SET str_length = LENGTH(str);

  WHILE i < str_length DO
    SET i = i + 1;
    IF SUBSTRING(str, i, 1) != delim THEN
      SET result = CONCAT(result, SUBSTRING(str, i, 1));
    ELSE
      RETURN result;
    END IF;
  END WHILE;

  RETURN result;
END //
DELIMITER ;

示例

使用上述自定义函数，我们可以将逗号分隔的值拆分开来，并返回结果：

SELECT
  id,
  SPLIT_STRING(ids, ',') as student_id
FROM 
  students

通过这个查询，我们可以得到和上述方法一相同的结果：

+----+-----------+
| id | student_id|
+----+-----------+
| 1  | 101       |
| 1  | 102       |
| 1  | 103       |
| 2  | 104       |
| 2  | 105       |
| 3  | 106       |
| 3  | 107       |
| 3  | 108       |
+----+-----------+