Generating completely different UUID for MySql

user_316469 0
Viewed 1596

I have seen other similar questions on SO but what I am asking here is not answered there.

I have a field called GUID on my table that is a primary key.

I want this field to be populated with a random UUID every time a recorded is inserted.

So, I have created this table:

CREATE TABLE `myTable` (
  `id` int(6) NOT NULL,
  `first_name` varchar(64) NOT NULL,
  `last_name` varchar(64) NOT NULL,
  `GUID` char(40) NOT NULL,
   PRIMARY KEY(`GUID`) 

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DELIMITER //
CREATE TRIGGER `t_GUID` BEFORE INSERT ON `myTable`
 FOR EACH ROW begin
 SET new.GUID := (SELECT uuid());
END //
DELIMITER ;

I have inserted 4 new records to this table and these are the UUIDs added to the entries:

  • ae353781-9022-11ea-b775-1866daed31d4
  • ae353a23-9022-11ea-b775-1866daed31d4
  • ae353b4e-9022-11ea-b775-1866daed31d4
  • ae353c26-9022-11ea-b775-1866daed31d4

except for 3 digits these are basically the same UUID.

How do I generate completely different UUIDs that are unique for the table as new records are being inserted?

uuid mysql
2 Answers

To answer your question, yes a guid or uuid is not designed to be hard to predict. It is only designed to be unique which is a difficult enough task.

Having large primary keys like this will really eat up space, which decreases performance.

I wouldn't recommend it for anything other than a last resort. Usually people will use guid's for keys when they are planning in advance to shard data into multiple tables.

Another argument against doing this is that obfuscation is not security. If there is a problem from a security standpoint with someone seeing something they shouldn't because they accessed for example a url with id=3, then that same problem exists if the url is id=ae353c26-9022-11ea-b775-1866daed31d4. The application should not allow someone to access id=3 if they should not be able to access it.

With that said, one workaround used for this type of scheme would be to hash the input.

You could use the uuid concatenated with some of the data from the row, and perhaps a timestamp, and something random and run that through sha1() for example. It will produce a large 40 character hex string.

There is a chance of a collision, so you would probably want to check and recover from that.

CREATE TABLE `myTable` (
  `id` int(6) NOT NULL,
  `first_name` varchar(64) NOT NULL,
  `last_name` varchar(64) NOT NULL,
  `GUID` char(40) NOT NULL,
   PRIMARY KEY(`GUID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DELIMITER //

CREATE TRIGGER `t_GUID` BEFORE INSERT ON `myTable`
 FOR EACH ROW begin
 SET new.GUID := (SELECT SHA1(CONCAT(new.first_name, RAND(), UUID(), new.last_name, NOW())));
END //
DELIMITER ;
user_8600340

The uuid() function generates UUIDv1, which is defined in a way that is fairly predictable. If you generate a bunch of new values very quickly, they will only vary by a few bits—but they are still guaranteed to be unique.

If you want unpredictable values, then you need to switch to a function that generates UUIDv4. This is only statistically unique, but unless you are generating billions of values per second for billions of years, it's good enough in practice.

Aside: you may want to consider storing UUIDs as BINARY(16) to save space and improve search efficiency. You can use a generated column to get the text version if you need that inside the DB for troubleshooting.

user_11397150
Related