dpwbc42604 2017-10-23 13:30
浏览 49
已采纳

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

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);
  • 写回答

1条回答 默认 最新

  • doushengyou2617 2017-10-23 14:41
    关注

    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   
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥500 亚马逊 COOKIE我如何才能实现 登录一个亚马逊账户 下发新 COOKIE ..我使用下发新COOKIE 导入ADS 指纹浏览器登录,我把账户密码 修改过后,原来下发新COOKIE 不会失效的方式
  • ¥20 玩游戏gpu和cpu利用率特别低,玩游戏卡顿
  • ¥25 oracle中的正则匹配
  • ¥15 关于#vscode#的问题:把软件卸载不会再出现蓝屏
  • ¥15 vimplus出现的错误
  • ¥15 usb无线网卡转typec口
  • ¥30 怎么使用AVL fire ESE软件自带的优化模式来优化设计Soot和NOx?
  • ¥15 Ubuntu20.04.4.LTS系统如何下载安装VirtualBox虚拟机?
  • ¥15 如何用QDomDocument读取内容为空格的xml数据
  • ¥15 请阅读下面代码,帮我修改下代码