在 MySQL 中创建 ENUM 变量类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1462497/
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
Creating ENUM variable type in MySQL
提问by jay
I am using an ENUM data type in MySQL and would like to reuse it, but not retype in the values. Is there an equivalent to the C, C++ way of defining types in MySQL?
我在 MySQL 中使用 ENUM 数据类型并希望重用它，但不重新输入值。是否有等效于 C、C++ 在 MySQL 中定义类型的方式？
I would like to do the following:
我想做以下事情：
DEFINE ETYPE ENUM('a','b','c','d');
CREATE TABLE Table_1 (item1 ETYPE, item2 ETYPE);
Is this possible?
这可能吗？
Thanks
谢谢
回答by Bill Karwin
No. MySQL does not support CREATE DOMAINor CREATE TYPEas, for example, PostgreSQL does.
不。MySQL 不支持，CREATE DOMAIN或者CREATE TYPE像 PostgreSQL那样支持。
You'll probably have to enter all the names again. You can mitigate the work it takes to do this by using copy & paste, or SQL scripts.
您可能需要再次输入所有名称。您可以通过使用复制和粘贴或 SQL 脚本来减轻执行此操作所需的工作。
You could also use the INFORMATION_SCHEMAtables to get the text of the ENUM definition, and then interpolate that into a new CREATE TABLEstatement.
您还可以使用这些INFORMATION_SCHEMA表来获取 ENUM 定义的文本，然后将其插入到新CREATE TABLE语句中。
You can also use CREATE TABLE ASin creative ways to copy a type definition. Here's a demonstration:
您还可以CREATE TABLE AS创造性地使用复制类型定义。这是一个演示：
CREATE TABLE foo ( f ENUM('abc', 'xyz') );
CREATE TABLE bar AS SELECT f AS b FROM foo;
SHOW CREATE TABLE bar;
Outputs:
输出：
CREATE TABLE `bar` (
`b` enum('abc','xyz') default NULL
)
Finally, I suggest that if your ENUM has many values in it (which I'm guessing is true since you're looking for a solution to avoid typing them), you should probably be using a lookup table instead of the ENUM data type.
最后，我建议如果您的 ENUM 中有很多值（我猜这是真的，因为您正在寻找避免键入它们的解决方案），您可能应该使用查找表而不是 ENUM 数据类型。
Re comment from @bliako:
来自@bliako 的评论：
You can do what you describe this way:
您可以通过以下方式执行您所描述的操作：
CREATE TABLE bar (pop INT NOT NULL, name VARCHAR(100))
AS SELECT 0 AS pop, NULL AS name, f FROM foo;
I tried this on MySQL 5.7.27, and it worked.
我在 MySQL 5.7.27 上试过这个，它奏效了。
It's interesting to note that you don't have to declare all three columns in the CREATE TABLE line. The third column fwill be added automatically.
有趣的是，您不必在 CREATE TABLE 行中声明所有三列。第三列f将自动添加。
It's also interesting that I had to give column aliases in the SELECTstatement to make sure the column names match those declared in the CREATE TABLE. Otherwise if the column names don't match, you end up with extra columns, and their data types are not what you expect:
同样有趣的是，我必须在SELECT语句中提供列别名以确保列名与CREATE TABLE. 否则，如果列名不匹配，您最终会得到额外的列，并且它们的数据类型不是您所期望的：
create table bar (pop int not null, name varchar(100))
as select 0 as c1, null as c2, f from foo;
show create table bar\G
CREATE TABLE `bar` (
`pop` int(11) NOT NULL,
`name` varchar(100) DEFAULT NULL,
`c1` binary(0) DEFAULT NULL,
`c2` binary(0) DEFAULT NULL,
`f` enum('abc','xyz') DEFAULT NULL
)
回答by vijayabalan
Example given below for mysql db.
下面给出了 mysql db 的示例。
CREATE TABLE `data` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`gender` enum('Male','Female') DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci
