15

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.

Abhishek Sharma
  • 6,689
  • 1
  • 14
  • 20
Laravelian
  • 558
  • 1
  • 3
  • 10

1 Answers1

12

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.

iHiD
  • 2,450
  • 20
  • 32
Déjà vu
  • 28,223
  • 6
  • 72
  • 100
  • well, at least on AWS Aurora aurora_version 1.17.3 innodb_version 1.2.10 protocol_version 10 slave_type_conversions version 5.6.10 version_comment MySQL Community Server (GPL) version_compile_machine x86_64 version_compile_os Linux – Alexander Popov Apr 23 '19 at 11:35
  • @AlexanderPopov What is the type of your field? See https://stackoverflow.com/questions/43056220/store-uuid-v4-in-mysql – Déjà vu Apr 23 '19 at 11:59
  • This didn't work for each different row. I updated all my rows with an unique uuid with from this answer https://stackoverflow.com/a/53888040/7662267 – klendi Jun 15 '20 at 13:04
  • 1
    @klendi - What version of MySQL/MariaDB/Aurora? Show us the whole SQL statement. Was it inside a Stored Routine? – Rick James Jun 17 '20 at 01:02
  • 1
    I got different values; what are we doing differently? See my Answer in https://dba.stackexchange.com/questions/269189/mysql-uuid-behavior – Rick James Jun 17 '20 at 01:23
  • @RickJames That's the question... In my case nothing simpler than the Update above. Maybe other engines (myisam...?) work differently. NB, I'm glad someone gets different uuids as well! – Déjà vu Jun 17 '20 at 02:36
  • 1
    @e2-e4 - MyISAM worked the same (diff uuids for single update) in 9 versions. Mysterious. – Rick James Jun 17 '20 at 03:01
  • @AlexanderPopov - Please provide a stand-alone test case. – Rick James Jun 17 '20 at 03:04
  • I also received the same UUID value for all rows. `UPDATE MyTable SET uuid = UUID();`. This is on `MySQL/InnoDB version8.0.19 – Brian H. Sep 22 '20 at 14:59
  • 3
    I have a reproduction of the problem with `UPDATE` causing all UUIDs to be the same, using docker-compose, here: https://gist.github.com/guss77/6747e48dd401d1ad36e5b4092ccfcfdd (there's also a video). I noticed that if I perform the update after the database is finished initializing (instead of as part of the setup), then UUIDs are different - I think it is a performance issue as I observe the problem with a high performance RDS database but not in a test on a local slow machine. – Guss Sep 22 '20 at 21:06
  • 17
    Be aware, 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. I spent a good 15mins thinking this was failing when actually it was working. I wonder if this is catching other people out too. – iHiD Apr 25 '21 at 14:00
  • 1
    Thank you @iHiD this was also the case for me! – HasBert Feb 08 '22 at 15:36