36

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.

Yves M.
  • 29,855
  • 23
  • 108
  • 144
Doghouse308
  • 489
  • 1
  • 5
  • 7
  • 2
    Possible duplicate of [Can I use a function for a default value in MySql?](https://stackoverflow.com/questions/270309/can-i-use-a-function-for-a-default-value-in-mysql) – Will B. Jul 10 '18 at 18:06

3 Answers3

46

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

Will B.
  • 17,883
  • 4
  • 67
  • 69
  • 2
    Thank you for your help. I'm a little confused why the uuid doesn't work, but the timestamp now() does. – Doghouse308 Sep 09 '17 at 20:26
  • 1
    The function `NOW()` isn't used, but the constant `CURRENT_TIMESTAMP` is. – Will B. Sep 09 '17 at 23:46
  • Meaning after executing `DEFAULT NOW()` it will be converted to `CURRENT_TIMESTAMP` when using `SHOW CREATE TABLE foo`. – Will B. Sep 09 '17 at 23:51
  • A short explanation of why they decided to not support using a function as a default would be nice. – Copy and Paste Jul 10 '18 at 18:00
  • I suggest having the following condition to ensure we can't insert an empty string: `IF new.id IS NULL OR new.id = '' THEN ...` – Julien Jan 02 '21 at 18:25
  • 1
    The conditional as provided is intended to function how MySQL typically handles a `DEFAULT` column value to allow `UUID` as the `PRIMARY KEY` replacement. Which would allow `''` but error if it exists or when strict mode is enabled. It would be up to the developer to determine their desired end-results such as `IF CHAR_LENGTH(TRIM(NEW.id)) = 0 THEN ...` as another alternative to prevent `NULL` or empty strings from being inserted, or by even ensuring the UUID 128-bit length is met. – Will B. Jan 03 '21 at 05:04
  • Thank you for your answer. Now I can insert with UUID without adding in my query. – mbdrian Jun 22 '23 at 06:25
11

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());
devcd603
  • 169
  • 1
  • 6
  • 6
    The solution described doesn't work with MySQL 8.0.17 and the default value generated is "uuid()" instead of a uuid – bdzzaid Mar 10 '21 at 17:13
1

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

unhex(replace(uuid(),'-',''))
Yves M.
  • 29,855
  • 23
  • 108
  • 144
ravindu1024
  • 1,496
  • 1
  • 13
  • 30
  • This is the only method I was able to get any traction with in MariaDB 10.6.12, except I had to enclose it in parentheses: `(unhex(replace(uuid(),'-','')))` – David Mancini Jun 02 '23 at 02:45