Menjana Nombor 13 Digit Unik: Kaedah dan Teknik dalam MySQL

Question

Hai semua, saya telah mencuba sepanjang hari untuk menjana 13 nombor unik untuk produk sedia ada dalam pangkalan data saya. Setiap produk mesti ada EAN13 barcode. Saya mencuba banyak contoh di internet tetapi tiada satu pun daripada mereka memberi saya hasil yang baik. Adakah sesiapa mempunyai penyelesaian untuk masalah ini?

Saya cuba ini tetapi tiada yang berkesan

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;

Bagaimana saya boleh mengemas kini semua 12000 baris dalam jadual sedia ada menggunakan kod di atas. Saya cuba UPDATE tetapi saya mendapat ralat apabila saya cuba mencampurkan UPDATE dan CAST

Struktur jadual produk ialah:

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

Answer 1

Kemas kini kod bar. Buat jadual baharu, kemudian masukkan nilai ke dalam jadual baharu, kemudian gunakan jadual baharu untuk mengemas kini jadual sedia ada menggunakan kemas kini.

Buat pertanyaan jadual:-

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

Nilai yang perlu diselitkan:-

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;

Kemas kini pertanyaan:-

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