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

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}> 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

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

User · Answer

select  i  uuid    update some table set guid     i  uuid

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

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

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

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

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

[mysql] Generate GUID in MySQL for existing Data?

Examples related to mysql

Examples related to random

Examples related to guid