Creating ENUM variable type in MySQL

enums, mysql, variables

Solution

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

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.

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.

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;

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.

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;

I tried this on MySQL 5.7.27, and it worked.

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.

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

Problem

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? 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

Original source