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

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.

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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

Method 2

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

Method 3

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

Method 4

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

Method 5

I believe you can’t:

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

Method 6

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/

Method 7

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;

Method 8

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).

Method 9

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.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating