views:

691

answers:

2

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

A: 

Hi,

this should be possible: SELECT @min_price:=MIN(price), but I am not sure about possible values of the column...

dusoft
I am not sure you understand the question. I am creating a table with a variable of the ENUM type that has a very long list of items in it. I would like to have a variable or definition (like that in a "C" #define statement) that I can use in the TABLE statement to define the type of a column and make the declaration of the table easier to edit and read.
jay
that's not possible and you will have to manage it via scripting language.
dusoft
A: 

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
) ENGINE=MyISAM DEFAULT CHARSET=latin1

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.

Bill Karwin