MySQL set default id UUID

Question

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.

Answer 1

MySQL 5.7, 8.0.12 and older

MySQL as of 5.7 or 8.0.12 does not support using a function or expression 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.

---

MySQL 8.0.13 and newer

With the release of MySQL 8.0.13 an expression can now be used as the default value, provided it is enclosed in parentheses.

Example db<>fiddle

The default value specified in a DEFAULT clause can be a literal constant or an expression. With one exception, enclose expression default values within parentheses to distinguish them from literal constant default values. The exception is that, for TIMESTAMP and DATETIME columns, you can specify the CURRENT_TIMESTAMP [constant] as the default, without enclosing parentheses.

CREATE TABLE foo (b BINARY(16)  DEFAULT (UUID_TO_BIN(UUID())));

When inserting a new row, the default value for a column with an expression default can be inserted either by omitting the column name or by specifying the column as DEFAULT (just as for columns with literal defaults):

INSERT INTO foo () VALUES();
INSERT INTO foo () VALUES(DEFAULT);

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

Answer 2

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());