[Solved] MySQL set default id UUID

I’m trying to create tables in a database that has an id field that will populate the id with an UUID by default.

I tried something like:

CREATE TABLE FOO (
  id CHAR(36) PRIMARY KEY DEFAULT uuid()
);

I greatly appreciate your assistance.

Solution #1:

MySQL as of 5.7 does not support using a function as the default value of a column.

The DEFAULT value clause in a data type specification indicates a default value for a column.

With one exception, the default value must be a constant; it cannot be a function or an expression.

https://dev.mysql.com/doc/refman/5.7/en/data-type-defaults.html

The alternative would be to use a trigger to monitor the BEFORE INSERT of the desired table.

DELIMITER ;;
CREATE TRIGGER `foo_before_insert` 
BEFORE INSERT ON `foo` FOR EACH ROW 
BEGIN
  IF new.id IS NULL THEN
    SET new.id = uuid();
  END IF;
END;;
DELIMITER ;

This will change the default value of an INSERT statement to the uuid() value, unless it has been explicitly defined.

Respondent: Will B.
Solution #2:

If you use binary(16) as the type, you can set the default as follows:

unhex(replace(uuid(),'-',''))
Respondent: ravindu1024
Solution #3:

for Mysql version 8 and above the answer is found in; Can I use a function for a default value in MySql?

CREATE TABLE t1 (   'uuid_field' VARCHAR(32) DEFAULT (uuid());
Respondent: devcd603
The answers/resolutions are collected from stackoverflow, are licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0 .

Leave a Reply

Your email address will not be published.