Can I use a function for a default value in MySql?
MysqlMysql Problem Overview
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 Solutions
Solution 1 - Mysql
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();
Solution 2 - Mysql
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()))
);
Solution 3 - Mysql
As already stated you can't.
If you want to simulate this behavior you can use a trigger in this way:
CREATE TRIGGER before_insert_app_users
BEFORE INSERT ON app_users
FOR EACH ROW
IF new.uuid IS NULL
THEN
SET new.uuid = uuid();
END IF;
You still have to update previously existing rows, like this:
UPDATE app_users SET uuid = (SELECT uuid());
Solution 4 - Mysql
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.
Solution 5 - Mysql
I believe you can't:
> the default value must be a constant; it cannot be a function or an expression
Solution 6 - Mysql
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/
Solution 7 - Mysql
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;
Solution 8 - Mysql
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).
Solution 9 - Mysql
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
.