產生唯一的13位數：MySQL中的方法與技巧

Question

大家好，我一整天都在嘗試為資料庫中的現有產品產生唯一的 13 個數字。每個產品都必須有 EAN13 條碼。 我嘗試了網路上的許多例子，但沒有一個給我帶來好的結果。有人有辦法解決這個問題嗎？

我嘗試了這個，但沒有任何效果

select cast(  (@n := (13*@n + 100) % 899999999981)+1e12 as char(15)) as num
from   (select @n := floor(rand() * 10e14) ) init,
       (select 1 union select 2) m01,
       (select 1 union select 2) m02,
       (select 1 union select 2) m03,
       (select 1 union select 2) m04,
       (select 1 union select 2) m05,
       (select 1 union select 2) m06,
       (select 1 union select 2) m07,
       (select 1 union select 2) m08,
       (select 1 union select 2) m09,
       (select 1 union select 2) m10,
       (select 1 union select 2) m11,
       (select 1 union select 2) m12,
       (select 1 union select 2) m13,
       (select 1 union select 2) m14,
       (select 1 union select 2) m15,
       (select 1 union select 2) m16,
       (select 1 union select 2) m17,
       (select 1 union select 2) m18,
       (select 1 union select 2) m19,
       (select 1 union select 2) m20,
       (select 1 union select 2) m21,
       (select 1 union select 2) m22
limit 5;

我現在如何使用上面的程式碼更新現有表中的所有 12000 行。我嘗試 UPDATE，但當我嘗試混合 UPDATE 和 CAST 時出現錯誤

產品表結構為：

#

productid INT(11)
productName Varchar(225)
barcode INT(13)

Answer 1

更新條碼。建立一個新表，然後將值插入到新表中，然後使用新表更新現有表所使用的更新。

建立表格查詢：-

CREATE TABLE unique_numbers (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  barcode VARCHAR(13) NOT NULL,
  UNIQUE KEY barcode (barcode)
);

要插入的值：-

INSERT INTO unique_numbers (barcode) 
SELECT CAST(  (@n := (13*@n + 100) % 899999999981)+1e12 as char(15)) as num
FROM 
  (SELECT @n := floor(rand() * 10e14) ) init,
  (SELECT 1 UNION SELECT 2) m01,
  (SELECT 1 UNION SELECT 2) m02,
  (SELECT 1 UNION SELECT 2) m03,
  (SELECT 1 UNION SELECT 2) m04,
  (SELECT 1 UNION SELECT 2) m05,
  (SELECT 1 UNION SELECT 2) m06,
  (SELECT 1 UNION SELECT 2) m07,
  (SELECT 1 UNION SELECT 2) m08,
  (SELECT 1 UNION SELECT 2) m09,
  (SELECT 1 UNION SELECT 2) m10,
  (SELECT 1 UNION SELECT 2) m11,
  (SELECT 1 UNION SELECT 2) m12,
  (SELECT 1 UNION SELECT 2) m13,
  (SELECT 1 UNION SELECT 2) m14,
  (SELECT 1 UNION SELECT 2) m15,
  (SELECT 1 UNION SELECT 2) m16,
  (SELECT 1 UNION SELECT 2) m17,
  (SELECT 1 UNION SELECT 2) m18,
  (SELECT 1 UNION SELECT 2) m19,
  (SELECT 1 UNION SELECT 2) m20,
  (SELECT 1 UNION SELECT 2) m21,
  (SELECT 1 UNION SELECT 2) m22
LIMIT 12000;

更新查詢：-

UPDATE product a
JOIN unique_numbers b ON a.productid = b.id
SET a.barcode = b.barcode;