mysql - 拆分具有多个值的单个 VARCHAR 字段，然后分别插入它们

Question

我正在将旧数据库数据迁移到新数据库，它们过去以以下格式存储电话号码:

示例1:

41.9044-9082;41.9044-9661;41.9851-9862;41.9984-0393;41.3399-9169;41.3997-7999;

示例 2:

41.3369-0102;41.8928-5992;

没有电话(空):

;

如何将这些单个 VARCHAR 字段拆分为多个值，然后分别插入它们？

表格示例:

|#id_tel#|### number ####|#|client_id|#|
|#  1   #|111163;3554353;|#|   2     |#|
|#  2   #|222222;        |#|   3     |#|
|#  3   #|;              |#|   4     |#|

我希望的表格示例:

|#id_tel#|### number ####|#|client_id|#|
|#  1   #|111163         |#|   2     |#|
|#  2   #|3554353        |#|   2     |#|
|#  3   #|222222         |#|   3     |#|

Answer 1

您可以通过嵌套调用 SUBSTRING_INDEX() 和数字表来做到这一点。在我的示例中，我动态创建最多包含 100 个号码的号码表。

假设表 old_tel 具有以下 CREATE TABLE 语句:

CREATE TABLE old_tel (
    id_tel INT,
    `number` VARCHAR(200),
    client_id INT
);

您可以使用以下查询获取带有 client_id 的拆分号码:

  SELECT
      SUBSTRING_INDEX(SUBSTRING_INDEX(`number`, ';', n.n), ';', -1) value,
      client_id
  FROM 
      old_tel
  CROSS JOIN (
      SELECT 
          1 + a.N + b.N * 10 AS n
      FROM
          (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
          ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
      ORDER BY n      
  ) n
  WHERE
      n.n <= LENGTH(`number`) - LENGTH(REPLACE(`number`, ';', ''))
  AND
      SUBSTRING_INDEX(SUBSTRING_INDEX(`number`, ';', n.n), ';', -1) <> ''
  ORDER BY
     client_id, SUBSTRING_INDEX(SUBSTRING_INDEX(`number`, ';', n.n), ';', -1);

假设您的新电话号码表看起来几乎相同:

CREATE TABLE new_tel (
    id_tel INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `number` VARCHAR(200),
    client_id INT
);

您可以使用第一个查询用这个简单的 INSERT 语句填充此表:

  INSERT INTO new_tel (`number`, client_id)
  SELECT
      SUBSTRING_INDEX(SUBSTRING_INDEX(`number`, ';', n.n), ';', -1) value,
      client_id
  FROM 
      old_tel
  CROSS JOIN (
    SELECT 
        1 + a.N + b.N * 10 AS n
    FROM
        (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
        ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
    ORDER BY n    
  ) n
  WHERE
    n.n <= LENGTH(`number`) - LENGTH(REPLACE(`number`, ';', ''))
  AND
    SUBSTRING_INDEX(SUBSTRING_INDEX(`number`, ';', n.n), ';', -1) <> ''
  ORDER BY
     client_id, SUBSTRING_INDEX(SUBSTRING_INDEX(`number`, ';', n.n), ';', -1);

说明

带有 UNION ALL 的内部子选择动态创建一个数字表。我们限制 number 列中子字符串的数量并过滤掉空值。

SUBSTRING_INDEX(SUBSTRING_INDEX(`number`, ';', n.n), ';', -1) value

删除以分号分隔的第 n 个数字。

查看它在这个中的工作Demo

**注意:** 通过避免痛苦的逐行插入，速度非常快。