CODEWITHSUNDEEP
mysqluuid
Brett
Brett

Reputation: 12007

How can I generate a unique UUID for all my records on a MySQL query?

I have a very simple MySQL table that I am querying. However, I would like to return a generated UUID for each result row within my query.

I am running the SELECT statement:

SELECT REPLACE(UUID(),'-','') as id, employee_id, store_id FROM employee_store;

And I am seeing the results:

id                                 employee_id  store_id
617de8d416e711ea91c552540047b61e   123          4
617de8d416e711ea91c552540047b61e   134          3
617de8d416e711ea91c552540047b61e   43           3
617de8d416e711ea91c552540047b61e   333          1
617de8d416e711ea91c552540047b61e   544          2

I would have expected all the id fields to be different.

How can I perform the SELECT and have the query generate a unique UUID for each result row?

Upvotes: 0

Views: 1420

Answers (1)

GMB
GMB

Reputation: 222472

When I run your query in this MySQL 5.7 DB Fiddle, it gives me:

id                               | employee_id | store_id
:------------------------------- | ----------: | -------:
c6c54bdd16e911ea976c00163ec888bf |         123 |        4
c6c54c1e16e911ea976c00163ec888bf |         134 |        3
c6c54c3116e911ea976c00163ec888bf |          43 |        3
c6c54c3f16e911ea976c00163ec888bf |         333 |        1
c6c54c4c16e911ea976c00163ec888bf |         544 |        2

If you look closely at the results, you can see that the UUIDs are different. This is easier to see when checking the first 8 characters

c6c54bdd
c6c54c1e
c6c54c31
c6c54c3f
c6c54c4c

Running the same query in MySQL 5.6 and 8.0, I also get UUIDs that are slightly different. So I would assume that you do get the same kind of results (unlike what is showned in your sample data).

If you want to generate ids that are more obviously different, you could hash them, as follows:

SELECT REPLACE(MD5(UUID()),'-','') as id, employee_id, store_id FROM employee_store;

id                               | employee_id | store_id
:------------------------------- | ----------: | -------:
e1ae8f1d052c0132d1c111d221acd464 |         123 |        4
91ea4f39657204a4c53ed9f1da53c89b |         134 |        3
9b66fe7957723cf1f581b81d892e798e |          43 |        3
a1240b02775fcc3fca0e1739a27a39fe |         333 |        1
8672b220f28050f9c8680248ded4ccd4 |         544 |        2

Upvotes: 2

Related Questions