5

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?

CodingLittle
  • 1,761
  • 2
  • 17
  • 44
  • 2
    *This wasn't unique enough*: primary keys are unique by design. Why do you actually want to use a guid instead of an auto-incremented integer key? – GMB Apr 21 '20 at 17:45
  • As I said it is not unique enough. I was getting 1,2,3,4,5 etc as my primary keys. And this is something I would use when sending request from frontend-backend and so I wanted something more unique so that some1 with less fun intentions could not figure out the PK – CodingLittle Apr 21 '20 at 17:47
  • @CodingLittle The values 1,2,3,4,5 are still unique, you don't have any duplicate values (the definition of unique). However you can still add a new column for the UUID and insert the value of the `uuid()` function in your `INSERT INTO` query. – Progman Apr 21 '20 at 17:50
  • I never said it wasn't unique I said it was not unique enough from my point of view when it comes to security. As for inserting it manually is part of the question where Im asking if it is possble to get a generated guid WITHOUT tirggers and manual inserts – CodingLittle Apr 21 '20 at 17:52
  • You're confusing an external reference with an internal key. – Strawberry Apr 21 '20 at 18:12

3 Answers3

18

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.

GMB
  • 216,147
  • 25
  • 84
  • 135
7

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

nbk
  • 45,398
  • 8
  • 30
  • 47
0

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
);
aiootp
  • 154
  • 4