时间戳

首页 > 解决方案 > 根据 MySQL / MariaDB 中的新序列更新主键值

mysql - 根据 MySQL / MariaDB 中的新序列更新主键值

问题描述

考虑一个具有自动增量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;

现在一切都按预期工作，新记录具有正确的顺序（基于旧记录），但是......

可以正常继续吗？或者我是否搞砸了一些内部索引并且我没有看到未来的潜在问题？

标签： mysqlmariadbforeign-keyssequenceprimary-key

解决方案

简短的回答： 看起来不错。

长答案：

测试这一点的“正确”方法是将所有相关表复制到另一台服务器（或在同一台服务器上，但在不同的数据库中）。然后执行诸如通过外键获取或添加新行（将获得 id=97）之类的操作。

START TRANSACTION还有一件事要做：COMMIT将UPDATEs. 这个单一事务将确保所有更新或非更新都已完成。

副手我看不出你的代码有什么问题。（我假设每个其他引用的表都将使用 Join 进行更新my_table。）（如果您已经运行了所有更新，请不要担心事务。将其视为“下次”的提示。）

至于如何AUTO_INCREMENT运作，我看不到任何未来的陷阱。新的 ID 将是 97 及以上；差距中的数字将永远消失。

请注意，它REPLACE是DELETE+ INSERT，因此在所有（？）情况下，它都会丢失 auto_inc id。

您是否必须编写 5 个更新？使用映射构建表可能更容易，然后将其应用于所有相关表。在主表的情况下，只有当存在重复 ID 的风险时，您才需要一些技巧（如 +100）可能是必要的。

考虑DROP COLUMN以后做。

推荐阅读