mysql - 如何根据MySQL表中的时间戳重置主键？

Question

我知道我可以通过这种方式重置主键:

ALTER TABLE `users` DROP `id`;
ALTER TABLE `users` AUTO_INCREMENT = 1;
ALTER TABLE `users` ADD `id` int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

但我想知道如何根据我的时间戳列中的时间戳来调整它以应用主键，即时间戳最旧的行获得值 1，下一个最旧的行获得值 2，等等。

显示创建表的输出

CREATE TABLE `tracks` (
 `id` int(10) unsigned NOT NULL auto_increment,
 `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
 `from_user` varchar(50) NOT NULL,
 `source_filename` varchar(80) NOT NULL,
 `uploaded_page` varchar(50) NOT NULL,
 `operating_system` varchar(50) NOT NULL,
 `browser` varchar(50) NOT NULL,
 `os_browser_version` varchar(200) NOT NULL,
 `title` varchar(100) NOT NULL,
 `artist` varchar(60) NOT NULL,
 `album` varchar(120) NOT NULL,
 `genre` varchar(120) NOT NULL,
 `format` varchar(10) NOT NULL,
 `bitrate` mediumint(9) NOT NULL,
 `conversion_needed` tinyint(1) NOT NULL COMMENT 'if not mp3, or higher than 192kbps',
 `conversion_successful` tinyint(1) NOT NULL,
 `art_extracted` tinyint(1) NOT NULL,
 `art_location` varchar(200) NOT NULL,
 `file_location` varchar(200) NOT NULL,
 `status` varchar(500) NOT NULL,
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=96 DEFAULT CHARSET=utf8

Answer 1

创建一个名为 users2 的新表，其结构与 users 相同:

CREATE TABLE users2 LIKE users;

然后运行这条语句:

INSERT INTO users2
(col1, col2, ... coln)
SELECT col1, col2, ... coln
FROM users
ORDER BY your_timestamp

对于您的特定表:

INSERT INTO tracks2
(`timestamp`,`from_user`,`source_filename`,`uploaded_page`,`operating_system`,`browser`,`os_browser_version`,`title`,`artist`,`album`,`genre`,`format`,`bitrate`,`conversion_needed`,`conversion_successful`,`art_extracted`,`art_location`,`file_location`,`status`)
SELECT `timestamp`,`from_user`,`source_filename`,`uploaded_page`,`operating_system`,`browser`,`os_browser_version`,`title`,`artist`,`album`,`genre`,`format`,`bitrate`,`conversion_needed`,`conversion_successful`,`art_extracted`,`art_location`,`file_location`,`status`
FROM tracks
ORDER BY timestamp