在 MySQL 中创建 ENUM 变量类型 [英] Creating ENUM variable type in MySQL

问题描述

我在 MySQL 中使用 ENUM 数据类型并想重用它，但不重新输入值.在 MySQL 中是否有等效于 C、C++ 定义类型的方式?

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?

我想执行以下操作:

DEFINE ETYPE ENUM('a','b','c','d');
CREATE TABLE Table_1 (item1 ETYPE, item2 ETYPE);

这可能吗?

谢谢

推荐答案

没有.MySQL 不支持 CREATE DOMAIN 或 CREATE TYPE，例如 PostgreSQL 确实.

No. MySQL does not support CREATE DOMAIN or CREATE TYPE as, for example, PostgreSQL does.

您可能需要再次输入所有名称.您可以通过使用 copy & 来减轻执行此操作所需的工作量.粘贴或 SQL 脚本.

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.

您还可以使用 INFORMATION_SCHEMA 表来获取 ENUM 定义的文本，然后将其插入到新的 CREATE TABLE 语句中.

You could also use the INFORMATION_SCHEMA tables to get the text of the ENUM definition, and then interpolate that into a new CREATE TABLE statement.

您还可以创造性地使用 CREATE TABLE AS 来复制类型定义.这是一个演示:

You can also use CREATE TABLE AS in creative ways to copy a type definition. Here's a demonstration:

CREATE TABLE foo ( f ENUM('abc', 'xyz') );
CREATE TABLE bar AS SELECT f AS b FROM foo;
SHOW CREATE TABLE bar;

输出:

CREATE TABLE `bar` (
  `b` enum('abc','xyz') default NULL
) 

最后，我建议如果您的 ENUM 中有很多值(我猜这是真的，因为您正在寻找避免输入它们的解决方案)，您可能应该使用查找表而不是 ENUM数据类型.

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.

来自@bliako 的重新评论:

Re comment from @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;

我在 MySQL 5.7.27 上试过这个，它奏效了.

I tried this on MySQL 5.7.27, and it worked.

有趣的是，您不必在 CREATE TABLE 行中声明所有三列.第三列 f 将自动添加.

It's interesting to note that you don't have to declare all three columns in the CREATE TABLE line. The third column f will be added automatically.

有趣的是，我不得不在 SELECT 语句中提供列别名，以确保列名称与 CREATE TABLE 中声明的名称相匹配.否则，如果列名不匹配，您最终会得到额外的列，并且它们的数据类型不是您所期望的:

It's also interesting that I had to give column aliases in the SELECT statement 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:

create table bar (pop int not null, name varchar(100)) 
as select 0 as c1, null as c2, f from foo;

show create table barG

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
)

这篇关于在 MySQL 中创建 ENUM 变量类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！