I want to optimize tables with VARCHAR columns, which are considered only a few - in most cases less than tens, and sometimes hundreds - of different values. Therefore, I want to convert the columns to ENUM s, but I think that I cannot foresee the values themselves, so I want to create them on the fly when necessary.
Unfortunately, it seems that MySQL will not throw an error when inserting a value that does not exist, but will instead store NULL .
For instance:
CREATE TABLE `enumed` ( `col` ENUM( 'a', 'b' ) ); INSERT INTO `enumed` ( `col`) VALUES ('b'); INSERT INTO `enumed` ( `col`) VALUES ('z');
Saves a string with 'b' and a string with NULL without errors. It is clear that I cannot allow myself another request to get null, but would prefer an error, in which case I would expand the ENUM definition as follows and repeat the request.
ALTER TABLE `enumed` CHANGE `col` `col` ENUM( 'a', 'b', 'z' ); INSERT INTO `enumed` ( `col`) VALUES ('b');
Is there a way to achieve this or is it just a painful way to handle someone else's collection that requires a table?
source share