将现有记录重新排序和重新标识到具有相同结构的新表中

Question

我有一个"table_name"用几列命名的表，包括id(type=INT, autoincrement, primary) 和date(type=datetime formatted Ymd h:m:s)。

id 与 datetime 列的时间顺序不匹配，因为一旦该表与另一个表合并。我不介意在 id 计数中有空白，但我希望至少根据“日期”列按时间顺序排列。

万一这可能会有所帮助，我已经准备了一个与原始参考表格式完全相同的空表，名为 let's say "table_name_new"。

那么如何实现索引id值的重新分配呢？

PHP 或 SQL，两者都是受欢迎的。

当前状态（示例）：

+----+--------+---------------------+

| id | color  | date                |

+----+--------+---------------------+

| 1  | blue   | 2019-05-06 20:30:00 |

| 2  | orange | 2019-05-12 10:30:00 |

| 8  | yellow | 2019-05-18 22:30:00 |

| 4  | white  | 2019-05-29 12:30:00 |

| 9  | black  | 2019-06-10 09:30:00 |

| 10 | green  | 2019-06-14 08:30:00 |

| 3  | red    | 2019-06-24 17:30:00 |

| 5  | purple | 2019-07-05 10:30:00 |

| 7  | brown  | 2019-08-09 19:30:00 |

| 6  | grey   | 2019-09-12 20:30:00 |

+----+--------+---------------------+

期望的结果：

+----+--------+---------------------+

| id | color  | date                |

+----+--------+---------------------+

| 1  | blue   | 2019-05-06 20:30:00 |

| 2  | orange | 2019-05-12 10:30:00 |

| 3  | yellow | 2019-05-18 22:30:00 |

| 4  | white  | 2019-05-29 12:30:00 |

| 5  | black  | 2019-06-10 09:30:00 |

| 6  | green  | 2019-06-14 08:30:00 |

| 7  | red    | 2019-06-24 17:30:00 |

| 8  | purple | 2019-07-05 10:30:00 |

| 9  | brown  | 2019-08-09 19:30:00 |

| 10 | grey   | 2019-09-12 20:30:00 |

+----+--------+---------------------+

Answer 1

查询您要求的查询是：INSERT INTO `table_name_new` (`color`, `date`)  SELECT `color`, `date` FROM `table_name` ORDER BY `date`;此处的进一步文档描述了 MySQL 中类似操作的其他变体。但正如许多评论所说，不建议id在定义后乱七八糟。您的代码的其他一些部分可能期望id具有特定含义。ORDER BY另外，在需要时使用子句重新排序记录非常简单。因此，除非您确定这id在您的整个代码库中绝对是微不足道的/毫无意义的，否则建议您不要进行这种操作。请慎重考虑。如何防止手动构建列列表以仅省略 1 列？不幸的是，没有 MySQL 语法直接支持通过定义要省略的列来选择表列。但是，我们可以构建自己的 SQL 查询以供其他 MySQL 查询运行。假设，您有正确的权限；和您已经在正确的数据库中；然后，---- Build a comma separated column list (omitting "`id`," and ",`id`")-- as session variable "@columns"---- @column would be "`color`,`date`" for the example table in our question.--SET @columns = (  SELECT    REPLACE(REPLACE(GROUP_CONCAT(CONCAT('`', COLUMN_NAME, '`')), '`id`,', ''), ',`id`', '')  FROM INFORMATION_SCHEMA.COLUMNS  WHERE TABLE_NAME = 'table_name' AND TABLE_SCHEMA = (SELECT DATABASE()));---- Build a SQL query string @sql according to @columns--SET @sql = CONCAT(  'INSERT INTO `table_name_new` (',  @columns,  ') SELECT ',  @columns,  ' FROM `table_name` ORDER BY date;');---- Prepare and execute the SQL statement--PREPARE stmt1 FROM @sql;EXECUTE stmt1;