不需要 PHP.您只能使用纯 MySQL 代码来完成.

No PHP is needed. You can do it with pure MySQL code only.

创建表格/插入表格

CREATE TABLE HugeTable
    (`Column1` VARCHAR(11), `Column2` VARCHAR(11), `Column3` VARCHAR(11))
;

INSERT INTO HugeTable
    (`Column1`, `Column2`, `Column3`)
VALUES
    ('Data1;Data2', 'Data3;Data4', 'Data5;Data6')
; 

CREATE TABLE NewTable
   (`Column1` VARCHAR(11), `Column2` VARCHAR(11), `Column3` VARCHAR(11))
;

首先我们需要 MySQL 来生成数字.此 MySQL 代码生成 1 到 100.所以最终的查询将支持多达 100 个分隔值.

First we need MySQL to generate numbers. This MySQL code generates 1 to 100. So the final query will support up to 100 separated values.

查询

SELECT 
 @row := @row + 1 AS ROW
FROM (
  SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) row1
CROSS JOIN (
  SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) row2
CROSS JOIN (
  SELECT @row := 0 
) init_user_params 

结果

  row  
--------
       1
       2
       3
       4
       5
       6
       7
       8
       9
      10
     ...
     ...
      90
      91
      92
      93
      94
      95
      96
      97
      98
      99
     100

现在我们可以看看一个方法来分离;分隔符.我们可以为此使用嵌套的 SUBSTRING_INDEX 函数

Now we can look at a method to separate on the ; delimiter. We can use nested SUBSTRING_INDEX functions for that

查询

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('Data1;Data2', ';', 1), ';', -1) AS DATA

结果

data    
--------
Data1   

如果我们想要我们可以使用的第二个单词，您只能看到返回第一个单词

You can see only the first word is returned if we want the second word we can use

查询

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('Data1;Data2', ';', 2), ';', -1) AS DATA

结果

data    
--------
Data2  

现在我们结合数字生成器和SUBSTRING_INDEX来生成数据

Now we combine the number generator and the SUBSTRING_INDEX to generate the data

查询

SELECT 
  DISTINCT
   SUBSTRING_INDEX(SUBSTRING_INDEX(Column1, ';', rows.row), ';', -1) Column1
 , SUBSTRING_INDEX(SUBSTRING_INDEX(Column2, ';', rows.row), ';', -1) Column2
 , SUBSTRING_INDEX(SUBSTRING_INDEX(Column3, ';', rows.row), ';', -1) Column3
FROM (
  SELECT 
   @row := @row + 1 AS ROW
  FROM (
    SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) row1
  CROSS JOIN (
    SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) row2  
  CROSS JOIN (
    SELECT @row := 0 
  ) init_user_params
)
 ROWS
CROSS JOIN 
 HugeTable 

结果

Column1  Column2  Column3  
-------  -------  ---------
Data1    Data3    Data5    
Data2    Data4    Data6    

查询新表

INSERT INTO 
  NewTable
SELECT 
  DISTINCT
   SUBSTRING_INDEX(SUBSTRING_INDEX(Column1, ';', rows.row), ';', -1) Column1
 , SUBSTRING_INDEX(SUBSTRING_INDEX(Column2, ';', rows.row), ';', -1) Column2
 , SUBSTRING_INDEX(SUBSTRING_INDEX(Column3, ';', rows.row), ';', -1) Column3
FROM (
  SELECT 
   @row := @row + 1 AS ROW
  FROM (
    SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) row1
  CROSS JOIN (
    SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) row2  
  CROSS JOIN (
    SELECT @row := 0 
  ) init_user_params
)
 ROWS
CROSS JOIN 
 HugeTable 

查询

SELECT * FROM NewTable

结果

Column1  Column2  Column3  
-------  -------  ---------
Data1    Data3    Data5    
Data2    Data4    Data6