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

Question

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?

Answer 1

As far as stated in the documentation, you can use uid() 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.