我可以在 MySql 中使用默认值的函数吗？

我想这样做:


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

然而，这会导致一个错误，有没有一种方法来调用一个函数的默认值在 mysql？

谢谢。

92095 次浏览
小开

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

小开
最佳答案

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

I believe you can't:

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

小开

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

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

小开

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/

小开

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;
小开

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

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.


提交答案