UUID as default for MySQL id column

user_14260254 0
Viewed 3159

I am trying to add a column to an existing table in MySql 8.0.17. The column needs to contain a UUID and I am trying to set it as a default value.

This is the statement I am executing

ALTER TABLE myTable ADD COLUMN UUID varchar(36) NOT NULL DEFAULT (UUID());

However I am getting the following error

Error Code: 1674. Statement is unsafe because it uses a system function that may return a different value on the slave.

I have read from other posts that it is possible to create a Trigger on the table however i would like to find out whether it is possible to set it directly as the default value on the column.

Also, what would be the advantage of using a binary conversion of the UUID over just a simple UUID ?

Eg.

ALTER TABLE myTable ADD COLUMN UUID binary(16) NOT NULL DEFAULT (UUID_TO_BIN(UUID(), true));

Thanks for your help.

uuid mysql
2 Answers

assigning UUID() as DEFAULT value won't work, because It does not guarantee that the same value will be generated on your replica. That is why using TRIGGER is good option for new records (insertions).

If your intention is to update current records as well, you can write an update statement

update myTable
set UUID = UUID()

your column is of type binary(16) which means UUID data is implicitly converted to binary. using UUID_TO_BIN is not needed.

EDIT:

CHAR/VARCHAR is the human-readable format. whereas, binary is the compact format. That means compressing the 32 characters (36 or more with separators) to the 16-bit format or back to the human-readable format.

If you dont mind about reading UUID, best is to use binary format

user_11189780

Change VARCHAR to CHAR, this will let you use 16bit.

Old Method

ALTER TABLE myTable ADD COLUMN UUID varchar(36) NOT NULL DEFAULT (UUID());

New Method

ALTER TABLE myTable ADD COLUMN UUID BINARY(36) NOT NULL DEFAULT (UUID_TO_BIN(UUID()));
user_82559810
Related