обновить значения первичного ключа на основе новой последовательности в MySQL/MariaDB

avatar
Matías Cánepa
8 августа 2021 в 21:37
137
1
-2

Рассмотрим таблицу my_table со столбцом id_my_table в качестве первичного ключа с автоинкрементом

CREATE TABLE `my_table` (
  `id_my_table` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `some_name` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id_my_table`)
) ENGINE=InnoDB;

На этот первичный ключ ссылаются некоторые другие таблицы.

У меня есть несколько записей, и для примера предположим, что у меня есть пять из них, имеющих id_my_table следующие значения: 1, 2, 3, 4 и 5. Из-за запроса клиента, который включает в себя конкретной последовательности из устаревшего набора данных, я обновил значения первичного ключа, чтобы они соответствовали этой последовательности.

Вот что я сделал:

ALTER TABLE `my_table` ADD COLUMN `my_old_id` INT UNSIGNED NULL AFTER `some_name`;

UPDATE `my_table` set `my_old_id` = `id_my_table`;

SET FOREIGN_KEY_CHECKS=0;

-- +100 so no duplicate error...
UPDATE `my_table` set `id_my_table` = `id_my_table` + 100;

UPDATE `my_table` set `id_my_table` = 25 where `id_my_table` = 1;
UPDATE `my_table` set `id_my_table` = 37 where `id_my_table` = 2;
UPDATE `my_table` set `id_my_table` = 58 where `id_my_table` = 3;
UPDATE `my_table` set `id_my_table` = 72 where `id_my_table` = 4;
UPDATE `my_table` set `id_my_table` = 96 where `id_my_table` = 5;

ALTER TABLE `my_table` AUTO_INCREMENT = 97;

UPDATE another_table at
INNER JOIN my_table mt on mt.my_old_id = at.reference_to_id_my_table
SET at.reference_to_id_my_table = mt.id_my_table;

SET FOREIGN_KEY_CHECKS=1;

Теперь все работает должным образом, и новые записи имеют правильную последовательность (на основе устаревшей), но...

Могу ли я продолжить как обычно? Или я перепутал какой-то внутренний индекс и не вижу потенциальной проблемы в будущем?

Источник
nbk
8 августа 2021 в 21:57
0

индексы автоматически обновляются, когда вы манипулируете ими, но вы можете проверить, насколько индекс работоспособен, и/или перестроить его.

Matías Cánepa
8 августа 2021 в 22:18
0

Как проверить здоровье? CHECK TABLE my_table?

nbk
8 августа 2021 в 22:36
0

SHOW EXTENDED INDEX from table1;

Matías Cánepa
10 августа 2021 в 01:16
0

@nbk если что-то не так, то я должен увидеть что-то в столбце Index_comment?

Akina
16 августа 2021 в 08:25
0

ON UPDATE CASCADE в определении FK сделает то же самое, но в тысячу раз проще...

Ответы (1)

avatar
Rick James
11 августа 2021 в 01:10
1

Короткий ответ: Выглядит нормально.

Длинный ответ:

"Правильный" способ тестировать это скопировать все соответствующие таблицы на другой сервер (или на тот же сервер, но в другую базу данных). Затем выполните операции, выполняющие такие действия, как выборка по внешнему ключу или добавление новой строки (которая получит id=97).

Еще одна вещь, которую нужно сделать: поместите START TRANSACTION и COMMIT вокруг множества UPDATEs. Эта единственная транзакция гарантирует, что все обновления или нет.

Навскидку не вижу ничего плохого в вашем коде. (Я предполагаю, что обновление с объединением будет выполняться для каждой другой таблицы, которая ссылается на my_table.) (Если вы уже выполнили все обновления, не беспокойтесь о транзакции. Думайте об этом как о подсказке в следующий раз. '.)

Что касается того, как работает AUTO_INCREMENT, я не вижу никаких проблем в будущем. Новые идентификаторы будут 97 и выше; числа в пробеле навсегда исчезнут.

Имейте в виду, что REPLACE равно DELETE + INSERT, так что во всех(?) случаях будет потерян идентификатор auto_inc.

Вы должны были написать 5 обновлений? Возможно, было бы проще создать таблицу с сопоставлением, а затем применить его ко всем соответствующим таблицам. В случае с основной таблицей, только если существует риск дублирования идентификаторов, вам может понадобиться какой-то трюк (например, +100).

Подумайте о том, чтобы сделать DROP COLUMN позже.

Matías Cánepa
11 августа 2021 в 01:30
0

Спасибо за ваш подробный ответ. Пять обновлений были просто для иллюстрации, на самом деле у меня есть одно обновление с соединением (откуда взята последовательность). Я реализую транзакцию, как вы предложили