Generate GUID in MySQL for existing Data

Question

I ve just imported a bunch of data to a MySQL table and I have a column  GUID  that I want to basically fill down all existing rows with new and unique random GUID s   How do I do this in MySQL    I tried  UPDATE db tablename   SET columnID   UUID     where columnID is not null   And just get every field the same

User · Answer

Just a minor addition to make as I ended up with a weird result when trying to modify the UUIDs as they were generated. I found the answer by Rakesh to be the simplest that worked well, except in cases where you want to strip the dashes.

For reference:

UPDATE some_table SET some_field=(SELECT uuid());

This worked perfectly on its own. But when I tried this:

UPDATE some_table SET some_field=(REPLACE((SELECT uuid()), '-', ''));

Then all the resulting values were the same (not subtly different - I quadruple checked with a GROUP BY some_field query). Doesn't matter how I situated the parentheses, the same thing happens.

UPDATE some_table SET some_field=(REPLACE(SELECT uuid(), '-', ''));

It seems when surrounding the subquery to generate a UUID with REPLACE, it only runs the UUID query once, which probably makes perfect sense as an optimization to much smarter developers than I, but it didn't to me.

To resolve this, I just split it into two queries:

UPDATE some_table SET some_field=(SELECT uuid());
UPDATE some_table SET some_field=REPLACE(some_field, '-', '');

Simple solution, obviously, but hopefully this will save someone the time that I just lost.

User · Answer

The approved solution does create unique IDs but on first glance they look identical  only the first few characters differ   If you want visibly different keys  try this   update CityPopCountry set id    select md5 UUID        MySQL  imran lenovo   world  gt  select city  id from CityPopCountry limit 10   ------------------------ ----------------------------------    city                     id                                  ------------------------ ----------------------------------    A Coru  a  La Coru  a      c9f294a986a1a14f0fe68467769feec7     Aachen                   d6172223a472bdc5f25871427ba64e46     Aalborg                  8d11bc300f203eb9cb7da7cb9204aa8f     Aba                      98aeeec8aa81a4064113764864114a99     Abadan                   7aafe6bfe44b338f99021cbd24096302     Abaetetuba               9dd331c21b983c3a68d00ef6e5852bb5     Abakan                   e2206290ce91574bc26d0443ef50fc05     Abbotsford               50ca17be25d1d5c2ac6760e179b7fd15     Abeokuta                 ab026fa6238e2ab7ee0d76a1351f116f     Aberdeen                 d85eef763393862e5fe318ca652eb16d    ------------------------ ----------------------------------    I m using MySQL Server version      5 5 40-0 wheezy1  Debian

User · Answer

UID Format  30B9BE365FF011EA8F4C125FC56F0F50 UPDATE  events  SET  evt uid     SELECT UPPER REPLACE  i  UUID    -             UID Format  c915ec5a-5ff0-11ea-8f4c-125fc56f0f50 UPDATE  events  SET  evt uid     SELECT UUID         UID Format  C915EC5a-5FF0-11EA-8F4C-125FC56F0F50 UPDATE  events  SET  evt uid     SELECT UPPER  i  UUID

User · Answer

I had a need to add a guid primary key column in an existing table and populate it with unique GUID s and this update query with inner select worked for me   UPDATE sri issued quiz SET quiz id  SELECT uuid       So simple  -

User · Answer

I m not sure if it s the easiest way  but it works  The idea is to create a trigger that does all work for you  then  to execute a query that updates your table  and finally to drop this trigger   delimiter    create trigger beforeYourTableUpdate  BEFORE UPDATE on YourTable FOR EACH ROW BEGIN   SET new guid column     SELECT UUID     END      Then execute   UPDATE YourTable set guid column    SELECT UUID       And DROP TRIGGER beforeYourTableUpdate   UPDATE Another solution that doesn t use triggers  but requires primary key or unique index    UPDATE YourTable  INNER JOIN  SELECT unique col  UUID   as new id FROM YourTable  new data  ON  new data unique col   YourTable unique col  SET guid column   new data new id   UPDATE once again  It seems that your original query should also work  maybe you don t need WHERE columnID is not null  so all my fancy code is not needed

User · Answer

select  i  uuid    update some table set guid     i  uuid

User · Answer

I faced mostly the same issue   Im my case uuid is stored as BINARY 16  and has NOT NULL UNIQUE constraints  And i faced with the issue when the same UUID was generated for every row  and UNIQUE constraint does not allow this  So this query does not work   UNHEX REPLACE uuid     -         But for me it worked  when i used such a query with nested inner select   UNHEX REPLACE  SELECT uuid      -         Then is produced unique result for every entry

User · Answer

Looks like a simple typo  Didn t you mean     where columnId is null    UPDATE db tablename   SET columnID   UUID     where columnID is null

User · Answer

UPDATE db tablename SET columnID    SELECT UUID    where columnID is not null

User · Answer

MYsql UPDATE tablename   SET columnName   UUID    oracle UPDATE tablename   SET columnName   SYS GUID     SQLSERVER UPDATE tablename   SET columnName   NEWID

[mysql] Generate GUID in MySQL for existing Data?

Examples related to mysql

Examples related to random

Examples related to guid