CODEWITHSUNDEEP
mysqlsqlprimary-keycreate-tableuid
CodingLittle
CodingLittle

Reputation: 1857

How to generate/autoincrement guid on insert without triggers and manual inserts in mysql?

Im revisiting my database and noticed I had some primary keys that were of type INT.

This wasn't unique enough so I thought I would have a guid. I come from a microsoft sql background and in the ssms you can choose type to "uniqeidentifier" and auto increment it.

In mysql however Ive found that you have to make triggers that execute on insert for the tables you want to generate a guide id for. Example:

Table:

CREATE TABLE `tbl_test` (
  `GUID` char(40) NOT NULL,
  `Name` varchar(50) NOT NULL,
  PRIMARY KEY (`GUID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Trigger:

CREATE TRIGGER `t_GUID` BEFORE INSERT ON `tbl_test`
 FOR EACH ROW begin
 SET new.GUID = uuid();

Alternatively you have to insert the guid yourself in the backend.

Im no DB expert but still remember that triggers cause performance problems.

The above is something I found here and is 9 years old so I was hoping something has changed?

Upvotes: 7

Views: 38730

Answers (3)

GMB
GMB

Reputation: 222462

As far as stated in the documentation, you can use uuid() as a column default starting version 8.0.13, so something like this should work:

create table tbl_test (
    guid binary(16) default (uuid_to_bin(uuid())) not null primary key,
    name varchar(50) not null
);

This is pretty much copied from the documentation. I don't have a recent enough version of MySQL at hand to test this.

Upvotes: 21

aiootp
aiootp

Reputation: 156

As per the documentation, BINARY(x) adds some hidden padding bytes to the end of each entry, & VARCHAR(40) also wastes space by not being encoded directly in binary. Using VARBINARY(16) would be more efficient.

Also, more entropy (unguessability / security) per byte is available from RANDOM_BYTES(16) than standardized UUIDs, because they use some sections to encode constant metadata.

Perhaps the below will work for your needs.

-- example
CREATE TABLE `tbl_test` (
  `GUID` VARBINARY(16) DEFAULT (RANDOM_BYTES(16)) NOT NULL PRIMARY KEY,
  `Name` VARCHAR(50) NOT NULL
);

Upvotes: 1

nbk
nbk

Reputation: 49375

You can make a 

INSERT INTO  `tbl_test` VALUES  (uuid(),'testname');

This would generate a new uuid, when you call it.

Or you can also use the modern uuid v4 by using one of these functions instead of the standard uuid(), which is more random than the uuid in mysql

How to generate a UUIDv4 in MySQL?

You can use since 8.0.13

CREATE TABLE t1 (
    uuid_field     VARCHAR(40) DEFAULT (uuid())
);

But you wanted more than unique, but here are only allowed internal functions and not user defined as for uuid v4, for that uyou need the trogger

Upvotes: 8

Related Questions