CODEWITHSUNDEEP
mysql
Laravelian
Laravelian

Reputation: 598

Insert different UUID on each row of a large table

I have a table with ~80k rows with imported data. Table structure is as follows:

order_line_items  
  - id  
  - order_id  
  - product_id  
  - quantity  
  - price  
  - uuid

On import, the order_id, product_id, quantity, and price were imported, but the uuid field was left null.

Is there a way, using mysql's UUID() function, to add a uuid to each row of the table in bulk? I could use a script to cycle through each row and update it but if there is a MySQL solution, that would be fastest.

Upvotes: 19

Views: 13851

Answers (2)

Oliver Jörns
Oliver Jörns

Reputation: 1

According to my own tests, it only changes the first 8 characters. This means about 4 billion different values (16 ^ 8). If I have a big table let's say 10 million entries, this means that I have a collision with the probability of ~ 1 : 400. Honestly. This uuid generation is not worth much. In the case you really have a big table with millions of data. So exactly then when this function would be handy, you should generate the uuids outside of mysql.

Upvotes: -1

Déjà vu
Déjà vu

Reputation: 28840

Each call to uuid() returns a different, unique value.

So a simple

UPDATE order_line_items SET uuid = uuid();

should assign each uuid field a unique value.

Edit March 2022

Note that using this method only a few characters change in the uuids, which make them look identical at a glance, but actually they're all different.

*Edit June 2020*

With @RickJames (see comments) we are trying to comprehend how some people can get the same UUID after running the Update command above (they should be all different).

  1. MySQL/MariaDB. The question is tagged ; be sure you are running MySQL or MariaDB, as another DBMS might not render that MySQL behavior for the UUID() on multiple rows

  2. Perform the Update as shown here, UUID() is a MySQL function (thus the ())

  3. Check the field that receives the Update, it must be large enough to hold 36 chars

See also this related question on DBA SE.

Upvotes: 19

Related Questions