Autogrowing ENUMs in MySQL

Question

I want to optimize tables having VARCHAR columns that count only few -- most of time less than tens, sometimes hundreds -- different values. So I wish to convert the columns to ENUMs, the point is that I can't anticipate on the values themselves, so I wish to build them on-the-fly, whenever necessary.

Unfortunately, it appears that that MySQL won't throw an error when inserting a value that do not exist, but instead will store NULL.

For instance:

CREATE TABLE `enumed` (
  `col` ENUM( 'a', 'b' )
);

INSERT INTO `enumed` ( `col`) VALUES ('b');
INSERT INTO `enumed` ( `col`) VALUES ('z');

Will store a row with 'b' and a row with NULL with no error. It's clear that I cannot afford another query to fetch the nullity of the value, but would prefer an error, in which case I would grow the ENUM definition like the following and repeat the query.

ALTER TABLE `enumed` CHANGE `col` `col` ENUM( 'a', 'b', 'z' );
INSERT INTO `enumed` ( `col`) VALUES ('b');

Is there any way to achieve this or is it just a painful way to handle a foreign collection that would require a table ?

Answer 1

If you enable strict SQL mode, attempting to store an invalid ENUM value will report an error. You could check for this error, extend the ENUM, and retry the INSERT.