使用PHP循环或MySQL重构错误的数据库

Question

I have a special case with a PHP project where I am working with a database without a 3rd normal form structure. The database consists of only 1 table with loads of columns. Some data that is supposed to be in a separate table, is clamped in 1 column, separated by a certain sign (in this case, semicolon ";").

There is also other columns where clamped data should be moved to the same separated table as mentioned. This must be confusing, so let me elaborate:

**HugeTable**
id |  Column1      |  Column2     |  Column3

123 | Data1;Data2 Data3;Data4 Data5;Data6

I need to put the data above in a separate table that looks like this:

**NewTable**
id   |  idHugeTable  |  Column1  |  Column2  |  Column3
 1   |  123          |  Data1    |  Data3    |  Data5
 2   |  123          |  Data2    |  Data4    |  Data6

So for each clamped data in the huge table, I need to make a new row in the new table. This process would help me to normalize the database so that it is at least workable. Right now it's a nightmare. This needs to be done either through PHP or MySQL, preferably PHP since looping is easier for one-shot queries per loop through the scripting language.

Edit: Example code of what I have tried in PHP:

$delimiter = ";";
$query = "SELECT * FROM HugeTable";

$result = mysqli_query($connection_var, $query);

while ($row = mysqli_fetch_assoc()){
    $column1_data = explode($delimiter, $row['Column1']);
    $column2_data = explode($delimiter, $row['Column2']);
    $column3_data = explode($delimiter, $row['Column3']);

    foreach ($column1_data as $key => $value){
        //skip if empty value
        if ($value == ""){
            continue;
        }
        else{
            $query_ins = "INSERT INTO NewTable (idHugeTable, Column1, Column2, Column3) VALUES (".$row['id'].", ".$column1_data[$key].", ".$column2_data[$key].", ".$column3_data[$key].");";
            mysqli_query($connection_var, $query_ins);
        }//end if
    }//end foreach
}//end while

mysqli_close($connection_var);

Answer 1

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

Create table/insert table

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))
;

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.

Query

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 

Result

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

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

Query

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

Result

data    
--------
Data1   

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

Query

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

Result

data    
--------
Data2  

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

Query

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 

Result

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

Query NewTable

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 

Query

SELECT * FROM NewTable

Result

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