使用 PHP 循环或 MySQL 重构坏数据库 [英] Restructuring a bad database with PHP loops or MySQL
问题描述
我有一个 PHP 项目的特例，我正在使用一个没有第三范式结构的数据库.该数据库仅包含 1 个带有大量列的表.一些应该在单独表中的数据被夹在 1 列中，用某个符号(在本例中为分号;")分隔.
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
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
所以对于大表中的每一个钳位数据，我都需要在新表中新建一行.这个过程将帮助我规范化数据库，使其至少是可行的.现在简直是噩梦.这需要通过 PHP 或 MySQL 来完成，最好是 PHP，因为循环更容易通过脚本语言进行每次循环的一次性查询.
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.
我在 PHP 中尝试过的示例代码:
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);
推荐答案
不需要 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
这篇关于使用 PHP 循环或 MySQL 重构坏数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
