MySQL 如何重新索引mysql表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2089492/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to reindex mysql table
提问by mrpatg
I have a table with many rows but they are out of order. Im using the field "id" as the primary key. I also have a "date" field which is a datetime field.
我有一张有很多行的表，但它们乱序了。我使用字段“id”作为主键。我还有一个“日期”字段，它是一个日期时间字段。
How could i reindex the table so that the entries are id'd in chronological order according to the date field
我如何重新索引表，以便根据日期字段按时间顺序对条目进行标识
回答by Josh
the way i would do it is to create a new table with auto increment index and just select all your old table into it ordering by date. you can then remove your old table.
我这样做的方法是创建一个带有自动增量索引的新表，然后按日期顺序选择所有旧表。然后你可以删除你的旧桌子。
回答by Ryun
How about something like a simple query using a variable:
使用变量的简单查询怎么样：
set @ROW = 0;
UPDATE `tbl_example` SET `id` = @ROW := @ROW+1 ORDER BY `fld_date` ASC;
This will order your rows like: 0,1,2,4,5...etc by your date.
这将按您的日期对您的行进行排序：0,1,2,4,5...等。
回答by Wyzard
Why do you want the sequence of IDs to correlate with the dates? It sounds like you want to do ORDER BY idand have the rows come back in date order. If you want rows in date order, just use ORDER BY dateinstead.
为什么您希望 ID 序列与日期相关联？听起来您想做ORDER BY id并让行按日期顺序返回。如果您想要按日期顺序排列的行，请ORDER BY date改用。
Values in an autoincrement ID column should be treated as arbitrary. Relying on your IDs being in date order is a bad idea.
自动增量 ID 列中的值应被视为任意值。依赖按日期顺序排列的 ID 是一个坏主意。
回答by zombat
The following SQL snippet should do what you want.
以下 SQL 代码段应该可以满足您的需求。
ALTER TABLE test_table ADD COLUMN id2 int unsigned not null;
SET @a:=0;
UPDATE test_table SET id2=@a:=@a+1 ORDER BY `date`;
ALTER TABLE test_table DROP id;
ALTER TABLE test_table CHANGE id2 id int UNSIGNED NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (id);
Keep in mind that you can never guarantee the order of an auto-incremented column once you start inserting and removing data, so you shouldn't be relying on any order except that which you specify using ORDER BYin your queries. This is an expensive operation that you are doing, as it requires indexes to be completely re-created, so I wouldn't suggest doing it often.
请记住，一旦开始插入和删除数据，您就永远无法保证自动递增列的顺序，因此除了您ORDER BY在查询中指定使用的顺序之外，您不应依赖任何顺序。这是您正在执行的一项昂贵的操作，因为它需要完全重新创建索引，所以我不建议经常这样做。
回答by sibidiba
You can use ALTER TABLEt ORDER BY col; The allowed syntax of ORDER BY is as in SELECT statements.
您可以使用ALTER TABLEt ORDER BY col; ORDER BY 允许的语法与 SELECT 语句中的一样。
回答by Стилян Горанов
I had to do something similar. The best way to do it was the following (you can run it in one SQL Query if you want, but bare in mind that this is a slow and very resource consuming operation):
我不得不做类似的事情。最好的方法如下（如果需要，您可以在一个 SQL 查询中运行它，但请记住，这是一个缓慢且非常消耗资源的操作）：
BE SURE TO MAKE A BACKUP OF YOUR TABLE, INCLUDING STRUCTURE AND DATA BEFORE STARTING THIS QUERY!
在开始此查询之前，请务必备份您的表，包括结构和数据！
ALTER TABLE your_table ADD COLUMN temp_id INT UNSIGNED NOT NULL;
SET @a:=0;
UPDATE your_table SET temp_id=@a:=@a+1 ORDER BY `date` ASC;
ALTER TABLE your_table DROP id;
ALTER TABLE your_table CHANGE temp_id id INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (id);
ALTER TABLE your_table CHANGE COLUMN id id INT(10) FIRST;
Just don't forget to change "your_table" with the name of your table, and the ORDER BY columns. Here I explain you what you're doing this way step by step:
只是不要忘记使用表名和 ORDER BY 列更改“your_table”。在这里，我一步一步地向您解释您正在做什么：
- First you add a new column named "temp_id" (make sure it's not a name you're using already);
- Next you add a temp variable equal to 0 (or to whatever you want for your ID to start from);
- Then you update your table, row by row by the set ORDER logic, setting a value for your new column "temp_id" equal to the variable you've set, then increment this variable by 1 (you can do something funky here, for example if you want your ID's to be always even, the you can set @a+2);
- Next step you drop (remove) your old column ID;
- Then you change the name of your temp_id column back to ID and it as a positive integer with auto increment which is the primary key of your table.
- Because ID now is the newly added temp_id column, it's located at the end of your table structure. To move it again as first column, you run the last query, to make sure it's the first column.
- 首先，您添加一个名为“temp_id”的新列（确保它不是您已经在使用的名称）；
- 接下来，您添加一个等于 0 的临时变量（或您希望 ID 开始的任何值）；
- 然后你按照设置的 ORDER 逻辑逐行更新你的表格，为你的新列“temp_id”设置一个等于你设置的变量的值，然后将此变量增加 1（你可以在这里做一些时髦的事情，例如如果您希望您的 ID 始终是偶数，您可以设置 @a+2)；
- 下一步，您删除（删除）旧列 ID；
- 然后将 temp_id 列的名称改回 ID，并将其作为具有自动增量的正整数，这是表的主键。
- 因为 ID 现在是新添加的 temp_id 列，所以它位于表结构的末尾。要将其作为第一列再次移动，请运行最后一个查询，以确保它是第一列。
回答by Eric Shoberg
If you are using something like phpmysql this could be achieved by:
如果您使用的是 phpmysql 之类的东西，可以通过以下方式实现：
- going to the table (left side list of db's and tables), then
- from the options in the upper bar select 'SQL'. Follow the advice by @Ryun, then go to 'Operations' (from the upper bar),
- look for 'TABLE OPTIONS', leave everything except 'AUTO_INCREMENT' unchanged,
- set the 'AUTO_INCREMENT' value to 1 and press go at the bottom of the form.
- 转到表格（数据库和表格的左侧列表），然后
- 从上方栏中的选项中选择“SQL”。按照@Ryun 的建议，然后转到“操作”（从上方的栏），
- 寻找 'TABLE OPTIONS'，保留除 'AUTO_INCREMENT' 以外的所有内容不变，
- 将“AUTO_INCREMENT”值设置为 1，然后按表单底部的 go。
What will this do, in all?
这将做什么？
- It will set the id columns in each from 1 to {count}.
- Then it will reset the index of the table so that your next inserted row will equal +1 the number of columns (and not +1 the old index).
- 它将在每个中设置 id 列，从 1 到 {count}。
- 然后它将重置表的索引，以便您下一个插入的行将等于 +1 列数（而不是 +1 旧索引）。
@Wyzard made reference to just ordering the columns by date when you retrieve them from the table (and not re-indexing). Since, indeed, the Primary Key should be arbitrary (except to any foreign keys and perhaps the consuming platform (but that is another matter)).
@Wyzard 提到，当您从表中检索列（而不是重新索引）时，仅按日期对列进行排序。因为，实际上，主键应该是任意的（除了任何外键和消费平台（但那是另一回事））。
