Can I use a function for a default value in MySql?

mmattax_1638 0
Viewed 90519

I want to do something like this:


create table app_users
(
    app_user_id smallint(6) not null auto_increment primary key,
    api_key     char(36) not null default uuid()
);

However this results in a error, is there a way to call a function for a default value in mysql?

thanks.

mysql
9 Answers

No, you can't.

However, you could easily create a trigger to do this, such as:

CREATE TRIGGER before_insert_app_users
  BEFORE INSERT ON app_users 
  FOR EACH ROW
  SET new.api_key = uuid();
Harrison_Fisk_161110

As of mysql v8.0.13 it is possible to use an expression as a default value for a field:

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.

CREATE TABLE t1 (
  uuid_field     VARCHAR(32) DEFAULT (uuid()),
  binary_uuid    BINARY(16)  DEFAULT (UUID_TO_BIN(UUID()))
);
user_53899970

Unfortunately no, MySQL 5 requires constants for the default. The issue was discussed in much more detail in the link below. But the only answer is to allow null and add a table trigger.

MySQL only recently accepted UUID as part of their DB package, and it's not as feature rich as we'd like.

http://www.phpbuilder.com/board/showthread.php?t=10349169

TravisO_351160

I believe you can't:

the default value must be a constant; it cannot be a function or an expression

user_203020

Note that MySQL's UUID() returns CHAR(36), and storing UUIDs as text (as shown in the other answers) is obviously inefficient. Instead, the column should be BINARY(16), and you can use UUID_TO_BIN() when inserting data and BIN_TO_UUID() when reading it back.

CREATE TABLE app_users
(
    app_user_id SMALLINT(6) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    api_key     BINARY(16)
);

CREATE TRIGGER before_insert_app_users
BEFORE INSERT ON app_users
FOR EACH ROW
  IF new.api_key IS NULL
  THEN
    SET new.api_key = UUID_TO_BIN(UUID());
  END IF;

Note that since MySQL doesn't really know this is a UUID, it can be difficult to troubleshoot problems with it stored as binary. This article explains how to create a generated column that will convert the UUID to text as needed without taking up any space or worrying about keeping separate binary and text versions in sync: https://mysqlserverteam.com/storing-uuid-values-in-mysql-tables/

user_11397150

In MariaDB starting from version 10.2.1 you can. See its documentation.

CREATE TABLE test ( uuid BINARY(16) PRIMARY KEY DEFAULT unhex(replace(uuid(),'-','')) );
INSERT INTO test () VALUES ();
SELECT * FROM test;
user_37144340

I'm not sure if the above answers are for an older version, but I saw somewhere that you can do this using the unhex() function. I tried it and it works. (maria db version 10.2)

You can do 

.... column_name binary(16) not null default unhex(replace(uuid(),'-',''))

and it works. To see the uuid just do hex(column_name).

user_22862450

Harrison Fisk's answer was great when it was written, but now it's outdated.

Nowadays you can use an expression as a DEFAULT value. This is supported since MySQL 8.0 and MariaDB 10.2. Note that, if you're going to use non-deterministic functions like NOW() or USER(), you should not use binlog_format=statement.

user_94450590
Related