mysql - 根据日期时间列重置 auto_increment

Question

我正在通过 phpMyAdmin 使用 MySQL。我需要重置数据库中其中一个表中的 ID 字段，但我需要根据每一行的发布日期进行重置。我一直在到处寻找，但似乎找不到解决方案:(

下面几行代码工作正常，但根据日期时间列并没有完全按照我的要求进行:

SET @count = 0;
UPDATE `table_name` SET `table_name`.`ID` = @count:= @count + 1;

这就是我所拥有的:

+----+---------------------+
| ID | post_date           |
+----+---------------------+
| 1  | 2013-11-04 20:06:28 |
| 2  | 2012-03-30 11:20:22 |
| 3  | 2014-06-26 22:59:51 |
+----+---------------------+

这就是我需要的:

+----+---------------------+
| ID | post_date           |
+----+---------------------+
| 1  | 2005-08-02 16:51:48 |
| 2  | 2005-08-02 16:59:36 |
| 3  | 2005-08-02 17:01:54 |
+----+---------------------+

提前致谢，伙计们:)

Answer 1

试试这个，虽然这是一个简单的方法。

But you will lose all the relations to other tables since you are resetting the PRIMARY ID Keys.

# Copy entire table to a temporary one based on the publication date
CREATE TEMPORARY TABLE IF NOT EXISTS `#temp_table` AS SELECT * FROM `wp_posts` ORDER BY `post_date`;

# Drop `ID` column from the temporary table
ALTER TABLE `#temp_table` DROP COLUMN `ID`;

# Reset the table `wp_posts` as well as its `ID`
TRUNCATE TABLE `wp_posts`;

# Exclude `ID` column in the INSERT statement below
INSERT INTO  `wp_posts`(`post_author`, `post_date`, ..., `comment_count`) SELECT * FROM `#temp_table`;

# Remove the temporary table
DROP TABLE `#temp_table`;

另请参阅下面的 WP3.0 的 ERD，

引用:https://codex.wordpress.org/Database_Description/3.3