Adding and finding UUIDs in a table

Question

I'm trying to add a column to an existing database to store a UUID, and then retrieve the row with that record's UUID, but I keep getting an empty set. I created the column as BINARY(64) because when I tried BINARY(16) as some sites suggested, it wasn't long enough to hold the data. Here's my create statement:

ALTER TABLE `my_table`  ADD `email_uuid` BINARY(64) NULL,  ADD UNIQUE (`email_uuid`)

Then I populated it:

UPDATE `my_table` SET `email_uuid` = UUID();

To test it, I queried the table to get the UUID:

mysql> select emailaddr,email_uuid FROM my_table WHERE emailaddr = 'myemail@mydomain.com';
+------------------------------------------+------------------------------------------+
| emailaddr                                | email_uuid                               |
+------------------------------------------+------------------------------------------+
| myemail@mydomain.com | a332f4d4-ddb2-11e2-88f9-80ee731fd7a1     |
+------------------------------------------+------------------------------------------+

I've tried a few different ways to get that row back:

mysql> select emailaddr,email_uuid FROM my_table WHERE email_uuid = 'a332f4d4-ddb2-11e2-88f9-80ee731fd7a1';
Empty set (0.00 sec)

mysql> select emailaddr,email_uuid FROM my_table WHERE email_uuid = '{a332f4d4ddb211e288f980ee731fd7a1}';
Empty set (0.00 sec)

mysql> select emailaddr,email_uuid FROM my_table WHERE email_uuid = BIN('{a332f4d4ddb211e288f980ee731fd7a1}');
Empty set (0.02 sec)

mysql> select emailaddr,email_uuid FROM my_table WHERE email_uuid = '61333332663464342d646462322d313165322d383866392d38306565373331666437613100000000';
Empty set (0.00 sec)

mysql> select emailaddr,email_uuid FROM my_table WHERE email_uuid = UNHEX(REPLACE('a332f4d4-ddb2-11e2-88f9-80ee731fd7a1','-',''));
Empty set (0.02 sec)

Is there something I'm missing?

Maybe you are looking for this ? http://stackoverflow.com/questions/10950202/how-to-store-uuid-as-number/10951183#10951183 — David Bélanger, Jun 25 '13 at 18:42
BINARY(16) was too small. When I added in the UUID, it was truncated down to "1edeaa98-ddb2-11" — aynber, Jun 25 '13 at 18:47
I didn't add any dashes at that point. I set the field using UUID(), and then got the field by searching for the email address. That's all that showed. — aynber, Jun 25 '13 at 18:50
That is what I am saying in my post. Do it at the prog level (not mysql)... if you use PHP let's say, do it in PHP. — David Bélanger, Jun 25 '13 at 18:51
Normally I would, but I created a trigger to autocreate the UUID so I didn't have to worry about different scripts doing it. — aynber, Jun 25 '13 at 18:54

score 0 · Accepted Answer · answered Jun 25 '13 at 18:41

0

The UUID string is 36 bytes long. With that minor variation, it works like a charm for me:

mysql> create table U (`email_uuid` BINARY(36)):
Query OK, 0 rows affected (0.34 sec)

mysql> insert into U VALUES ( 'a332f4d4-ddb2-11e2-88f9-80ee731fd7a1' ):
Query OK, 1 row affected (0.01 sec)

mysql> select * from U where `email_uuid` = 'a332f4d4-ddb2-11e2-88f9-80ee731fd7a1':
+--------------------------------------+
| email_uuid                           |
+--------------------------------------+
| a332f4d4-ddb2-11e2-88f9-80ee731fd7a1 |
+--------------------------------------+
1 row in set (0.00 sec)

answered Jun 25 '13 at 18:41

Sylvain Leroux

50,096
7
103
125

You are wasting space. A lot of space. Please see this: http://stackoverflow.com/questions/10950202/how-to-store-uuid-as-number/10951183#10951183 – David Bélanger Jun 25 '13 at 18:44
Thanks. Funny how the field length made a difference. – aynber Jun 25 '13 at 18:49
@DavidBélanger Yes of course. UUIDs are 128 bit long. So they require only 16 byte to store them. I assumed here that the OP were looking for a simple solution using the human-readable representation of UUID. When/If that work -- it should still be time be refactor the code to "optimize" for space. – Sylvain Leroux Jun 25 '13 at 18:54

Adding and finding UUIDs in a table

1 Answers1