將行轉移到新表中，強制生成唯一的主鍵直到其可插入

假設我們有以下場景……

+-----------------+-------------+---------------+
| Table           | PK          | Additional    |
+-----------------+-------------+---------------+
| playerinventory | id (int)    | amount, level |
+-----------------+-------------+---------------+
| newinventory    | id (BIGINT) | amount, level |
+-----------------+-------------+---------------+

我們正在對遊戲數據庫進行架構更改。

“amount, level” 應該從 “playerinventory” 中提取並**插入到 “newinventory”**中。

限制

• “newinventory”沒有自動增量pk，因此我們需要為每個生成一個唯一的long ……我們也不能將“auto-increment”添加到表中
• “newinventory”可能已經包含這些身份，因此我們需要檢查生成的 id 是否已經存在，如果存在，則對受影響的一組提取列**重複該過程，**直到它被 成功插入。
• 我們不能簡單地將“ playerinventory ”中的每一行插入到具有目前 ID 的“newinventory”中……這根本不可能（如果我要解釋為什麼這篇文章會長得多），這就是為什麼我們需要生成一個新的唯一 ID

在幾乎任何程式語言中，這個過程看起來都是這樣的

for row in playerInventory
  id = generate new ID
  while(id in newInventory) id = generate new ID
  insert into newInventory( id, row.amount, row.level )
     

我們如何在 MySQL/SQL 中實現這一點？

“newinventory”中的最高 ID 已經是 9223372036854775806，因此我們不能再增加了……我們需要生成和測試直到它適合。

使用 BEFORE INSERTTRIGGGER 生成唯一數字。

與SET @NEW_ID = 1;您一起定義第一個新值

架構 (MySQL v8.0)

CREATE table playerinventory ( id int PRIMARY KEY ,    amount INT , level int);
CREATE table newinventory ( id bigint PRIMARY KEY ,    amount INT , level int);

INSERT INTO playerinventory VALUEs (5,1,1),(6,2,2),(7,3,3),(8,4,4);

DELIMITER $$
CREATE TRIGGER before_newinventory_insert
BEFORE INSERT
ON newinventory FOR EACH ROW
BEGIN
   
   SELECT MAX(id) + 1
   INTO @NEW_ID
   FROM newinventory;
   IF @NEW_ID IS NULL THEN
       SET @NEW_ID = 1;
   END IF;
   set NEW.id = @NEw_ID;
END $$

DELIMITER ;

INSERT INTO newinventory SELECT * FROM playerinventory;

---

查詢 #1

SELECT * FROM newinventory;

| id  | amount | level |
| --- | ------ | ----- |
| 1   | 1      | 1     |
| 2   | 2      | 2     |
| 3   | 3      | 3     |
| 4   | 4      | 4     |

---

在 DB Fiddle 上查看

假設在此過程中沒有其他人插入表中，您可以獲得一批可用的 id:s，例如：

SELECT id+1
FROM newinventory x
WHERE NOT EXISTS (
   SELECT 1 
   FROM newinventory y
   WHERE y.id = x.id + 1
)
ORDER BY id+1
LIMIT 1000; -- batch size

編輯：上面只會在每個現有的之後為您提供下一個可用的 id。如果差距很大，可以使用遞歸 CTE 查找某個 id 以下的所有可用 id：

with recursive T (n) as ( select 0 union all select n+1 from T where n+1 < 1000 )
select n from T
where not exists (
   select 1 from newinventory 
   where id = n
)
order by n
limit 100;

當您用完可用的 id:s 時，重複上述操作。在後一種情況下，您可以使用最後找到的 id 初始化基本情況：

with recursive T (n) as ( 
   select 1+@last_found 
   union all 
   select n+1 from T 
   where n+1 < 1000 + @last_found
)
select n from T
where not exists (
   select 1 from newinventory 
   where id = n
)
order by n
limit 100;

引用自：https://dba.stackexchange.com/questions/273474