- 19.07.15
- 12:14
- 5662
- 0
При починке таблиц после потери автоинкремента переопределение полей идентификаторов может привести к коллизиям. Выглядит это так:
1 2 3 4 |
mysql> ALTER TABLE wp_comments CHANGE comment_ID -> `comment_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY; ERROR 1062 (23000): ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY' |
Перенумеровать таблицу в этом случае можно обходным путём:
- удаляются ключи, требующие уникальности каких-либо полей:
12ALTER TABLE tbl DROP PRIMARY KEY;ALTER TABLE tbl DROP INDEX unique_column; - отключается автозамена нулевых значений поля при добавлении к нему автоинкремента — чтобы избежать коллизий:
1SET @@SESSION.sql_mode = CONCAT(@@sql_mode, ',NO_AUTO_VALUE_ON_ZERO'); - для поля идентификатора создаётся неуникальный ключ, затем оно переопределяется в автоинкрементное:
12ALTER TABLE tbl ADD KEY (id);ALTER TABLE tbl CHANGE id `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT; - включается автозамена нулевых значений — чтобы пронумеровать строки:
1SET @@SESSION.sql_mode = REPLACE(@@sql_mode, 'NO_AUTO_VALUE_ON_ZERO', ''); - содержимое строк с нулевым идентификатором добавляется в эту же таблицу, после чего сами строки удаляются:
12INSERT INTO tbl SELECT * FROM tbl WHERE id = 0;DELETE FROM tbl WHERE id = 0; - восстанавливаются уникальные ключи, создаётся первичный ключ:
12ALTER TABLE tbl ADD UNIQUE unique_column (unique_column);ALTER TABLE tbl ADD PRIMARY KEY (id); - удаляется неуникальный ключ, созданный прежде для поля идентификаторов:
1ALTER TABLE tbl DROP INDEX id;
Пример с wp_comments
— ключи UNIQUE
отсутствуют:
1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- автоинкремент требует индексированного поля: ALTER TABLE wp_comments ADD KEY (comment_ID) -- отключение автозамены нулей при переопределении поля: SET @@SESSION.sql_mode = CONCAT(@@sql_mode, ',NO_AUTO_VALUE_ON_ZERO'); ALTER TABLE wp_comments CHANGE comment_ID `comment_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT; -- включение автозамены нулей - для замены их во вставляемых INSERT'ом строках; SET @@SESSION.sql_mode = REPLACE(@@sql_mode, 'NO_AUTO_VALUE_ON_ZERO', ''); INSERT INTO wp_comments SELECT * FROM wp_comments WHERE comment_ID = 0; DELETE FROM wp_comments WHERE comment_ID = 0; -- восстановление ключей: ALTER TABLE wp_comments ADD PRIMARY KEY (comment_ID); ALTER TABLE wp_comments DROP INDEX comment_ID; |
Восстановление первичного ключа и автоинкремента в wp_options
, поле option_name
является уникальным:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
-- удаление уникального индекса - чтобы не мешало вставлять значения: ALTER TABLE wp_options DROP INDEX option_name; ALTER TABLE wp_options ADD KEY (option_id); -- ключ для автоинкремента SET @@SESSION.sql_mode=CONCAT(@@SESSION.sql_mode, ',NO_AUTO_VALUE_ON_ZERO'); ALTER TABLE wp_options CHANGE option_id `option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT; -- если значения автоинкрементного столбца не копируются, они -- генерируются автоматически вне зависимости от @@sql_mode: INSERT INTO wp_options (option_name, option_value, autoload) SELECT option_name, option_value, autoload FROM wp_options WHERE option_id = 0; DELETE FROM wp_options WHERE option_id = 0; -- восстановление ключей таблицы: ALTER TABLE wp_options ADD UNIQUE option_name (option_name); ALTER TABLE wp_options ADD PRIMARY KEY (option_id); ALTER TABLE wp_options DROP INDEX option_id; |