MySQL Enum Quiz

README.md

Preperation

Read the SQL statements in instructions.sql.

Question

Only one of the following queries will produce 1, the other two will produce 0.

Which query will produce the number 1?

Choice A

select count(*) from example where `status` = "c";

Choice B

select count(*) from example where `status` = "";

Choice C

select count(*) from example where `status` IS NULL;

The prize for working this out (or running the SQL and finding out what happens) is anger.

Want the answer?

instructions.sql

-- Create our example table

CREATE TABLE `example` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `status` enum('a','b') NOT NULL DEFAULT 'a',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- Insert our example

INSERT INTO `example` (`status`) VALUES ('a');

-- Change the statuses around

ALTER TABLE `example`
  change `status` `status` ENUM(
  'b','c'
) NOT NULL DEFAULT 'c';

The only correct answer is: never use enums in schema. It's content, not structure.

^

I can see why you'd want the 'a' values to change to 'c' values, but that's probably not a very safe thing to do automatically, as it can result in seriously corrupted (and confusing!) data on the off-chance the ENUM field is altered accidentally. And the schema declaration explicitly prevents NULL values, so an empty string is the next best thing.

Bit of a mindfuck, no doubt.

@yuriybabenko Nah - "next best thing" would probably be:

ERROR #### (#####): You are trying to do something silly, and I refuse to participate.

Use sql_mode TRADITIONAL.
http://dev.mysql.com/doc/refman/5.7/en/server-sql-mode.html
"Make MySQL behave like a “traditional” SQL database system. A simple description of this mode is “give an error instead of a warning” when inserting an incorrect value into a column."

@michaelmoussa - Too logical, does not compute.

@FrenkyNet I'm not sure I understand your point there sadly. It is just content, but it boils down to the smallest binary key possible to represent that data. That is still data just like anything else. Also a database should be able to take care of itself, so allowing somebody to enter status = "steve" is dangerous, and annoying. Limiting the results is self documenting.

@yuriybabenko it's not like if I allowed it to set null values it would suddenly use null instead of "". If it was interested in avoiding corrupting data it should A) throw an error or B) respect my newly provided default. Giving it a string is just like me asking you what 2345 / 234 is and you replying "tuesday".

@michaelmoussa Right.

@stefanaxelsson It's not trying to insert a value, its just failing to respect the default when a non-existant option remains for an enum value.

@stefanaxelsson I'll take a look at that. Thanks!

The answer for those that didn't get it is B. Which is fucked.