时间戳

首页 > 解决方案 > 将现有记录重新排序和重新标识到具有相同结构的新表中

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

问题描述

我有一个"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 |
+----+--------+---------------------+

标签： phpmysqlsqldatetimeindexing

解决方案

查询

您要求的查询是：

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 查询运行。

假设，

  1. 您有正确的权限；和
  2. 您已经在正确的数据库中；

然后，

--
-- 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;

推荐阅读