Generating Unique 13-Digit Numbers: Methods and Techniques in MySQL

Question

Hi everyone, I've been trying all day to generate unique 13 numbers for existing products in my database. Each product must have an EAN13 barcode. I tried many examples on the internet but none of them gave me good results. Does anyone have a solution to this problem?

I tried this but nothing worked

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;

How can I now update all 12000 rows in an existing table using the above code. I try UPDATE but I get error when I try to mix UPDATE and CAST

The product table structure is:

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

Answer 1

Update barcode. Create a new table, then insert the values ​​into the new table, then update the existing table using the new table.

Create table query:-

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

Value to be inserted: -

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 query:-

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