生成唯一的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;