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

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;