Creating ENUM variable type in MySQL
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
)