I have a MySQL table with about 17mn records with an auto_increment field. The structure is something like this:
我有一个MySQL表,大约有1700条记录,带有auto_increment字段。结构是这样的:
+--------+-------------------+-----------+
| ID | Year1 | Year2 |
+--------+-------------------+-----------+
| 1 | 2001 | 2 |
| 2 | 2001 | 1 |
| 3 | 1999 | 3 |
| 4 | 2004 | 2 |
+--------+-------------------+-----------+
I'd like to reorder the physical structure of the whole table by changing the auto_increment ID to match the Year1 and Year2 columns when ordered in ascending order. Something like this..
我想通过更改auto_increment ID来重新排序整个表的物理结构,以便按升序排序时匹配Year1和Year2列。像这样的东西......
+--------+-------------------+-----------+
| ID | Year1 | Year2 |
+--------+-------------------+-----------+
| 1 | 1999 | 3 |
| 2 | 2001 | 1 |
| 3 | 2001 | 2 |
| 4 | 2004 | 2 |
+--------+-------------------+-----------+
Is this possible with a query in MySQL or should I use PHP and loops, et al.?
这可能是在MySQL中的查询,还是我应该使用PHP和循环,等等?
Update The use case is this: The actual data I have is stored in a specific pattern. I wouldn't want the end user to identify the pattern (2001, then 2001, then 1999, etc).. Hence I'd like to rearrange the data in a standard, sorted fashion..
更新用例是这样的:我拥有的实际数据以特定模式存储。我不希望最终用户识别模式(2001年,然后是2001年,然后是1999年等)。因此,我想以标准的,有序的方式重新排列数据。
There are no foreign keys for this table.
此表没有外键。
1 个解决方案
#1
2
First of all take a backup of your database
首先要备份你的数据库
Instead of re arranging your actual table create a new table and insert values from the old one. like this:
而不是重新安排您的实际表创建一个新表并插入旧表的值。喜欢这个:
Create table with auto increment, primary key
使用自动增量,主键创建表
CREATE TABLE Table2
(`ID` int auto_increment primary key, `Year1` int, `Year2` int)
;
Insert with SELECT statement
插入SELECT语句
INSERT INTO Table2
(`Year1`, `Year2`)
SELECT Year1, Year2
FROM Table1
ORDER BY Year1, Year2;
See this SQLFiddle
#1
2
First of all take a backup of your database
首先要备份你的数据库
Instead of re arranging your actual table create a new table and insert values from the old one. like this:
而不是重新安排您的实际表创建一个新表并插入旧表的值。喜欢这个:
Create table with auto increment, primary key
使用自动增量,主键创建表
CREATE TABLE Table2
(`ID` int auto_increment primary key, `Year1` int, `Year2` int)
;
Insert with SELECT statement
插入SELECT语句
INSERT INTO Table2
(`Year1`, `Year2`)
SELECT Year1, Year2
FROM Table1
ORDER BY Year1, Year2;