我可以在 MySql 中使用函数作为默认值吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/270309/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can I use a function for a default value in MySql?
提问by mmattax
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?
但是这会导致错误，有没有办法在mysql中为默认值调用函数？
thanks.
谢谢。
回答by Harrison Fisk
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();
回答by Shadow
As of mysql v8.0.13it is possible to use an expression as a default value for a field:
从mysql v8.0.13 开始，可以使用表达式作为字段的默认值：
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.
DEFAULT 子句中指定的默认值可以是文字常量或表达式。除了一个例外，将表达式默认值括在括号内以将它们与文字常量默认值区分开来。
CREATE TABLE t1 (
uuid_field VARCHAR(32) DEFAULT (uuid()),
binary_uuid BINARY(16) DEFAULT (UUID_TO_BIN(UUID()))
);
回答by Pamput
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());
回答by TravisO
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 5 需要默认常量。在下面的链接中更详细地讨论了这个问题。但唯一的答案是允许 null 并添加表触发器。
MySQL only recently accepted UUID as part of their DB package, and it's not as feature rich as we'd like.
MySQL 最近才接受 UUID 作为其数据库包的一部分，它的功能并不像我们希望的那样丰富。
回答by Thibaut Barrère
回答by StephenS
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.
需要注意的是MySQL的UUID()回报CHAR(36)，和存储的UUID为文本（在其他的答案如图所示）显然是低效的。相反，该列应该是BINARY(16)，您可以UUID_TO_BIN()在插入数据和BIN_TO_UUID()读回数据时使用。
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/
请注意，由于 MySQL 并不真正知道这是一个 UUID，因此很难解决将其存储为二进制文件的问题。这篇文章解释了如何创建一个生成的列，根据需要将 UUID 转换为文本而不占用任何空间或担心保持单独的二进制和文本版本同步：https: //mysqlserverteam.com/storing-uuid-values-in -mysql-表/
回答by ibotty
In MariaDB starting from version 10.2.1 you can. See its documentation.
从版本 10.2.1 开始，您可以在 MariaDB 中使用。请参阅其文档。
CREATE TABLE test ( uuid BINARY(16) PRIMARY KEY DEFAULT unhex(replace(uuid(),'-','')) );
INSERT INTO test () VALUES ();
SELECT * FROM test;
回答by ravindu1024
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)
我不确定上述答案是否适用于旧版本，但我在某处看到您可以使用 unhex() 函数执行此操作。我试过了，它有效。（玛丽亚数据库版本 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).
它有效。要查看 uuid，只需执行 hex(column_name)。
