I want to stab MySQL between the eyes. Or rather if MySQL had eyes, I’d stab it squarely between them.
It just wasted far too much of my time with this bit of annoying behaviour. I’ll admit it was also my fault, but I still think MySQL reacted in a retarded way. (But then from a “database” system which on its default settings is happy to silently mangle data however it feels, that’s not too surprising).
mysql> select username, admin_approval from jx_members where username = 'davidp';
+----------+----------------+
| username | admin_approval |
+----------+----------------+
| davidp | 0 |
+----------+----------------+
1 row in set (0.00 sec)
OK, so there’s the row. Now let’s update it and change the value of the admin_approval column to 1….
mysql> update jx_members set admin_approval = 1 where username = 'davidp';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
OK, so it found the row matching the where clause (same as the select) but couldn’t be bothered to change it – and didn’t give a warning to explain why (and repeating the select shows that the value is still 0).
The field defination fpr admin_approval is actually enum('0','1') – and therein lies the problem – it’s an enum containing the *strings* ‘0’ and ‘1’, not the *numbers* 0 and 1. But, MySQL in its infinite retardness thinks “ok, what you’ve asked me to set it to isn’t an allowable value in the enum so I’ll just ignore you… but won’t bother to raise a warning to tell you about it either.”
If you try to stuff ‘badger’ into that enum it will raise a warning (and blank out the field), but if you give it a number it ignores you.
Die MySQL, die. (And no, that’s not German for “the MySQL, the”).
Try your site in pirate language: http://www.syddware.com/cgi-bin/pirate.pl
leaving aside the issue why you’d want an enum(‘0′,’1’) your analysis is flawed.
If you set an enum column using an integer value, you are actually referencing the 1-based array of possible values, thus setting it 1 corresponds to the first enum element, which if course ‘0’
Setting it zero will cause the column to have an empty string for its value.
If you want it set to ‘1’, you’d have to set it to 2
Me, I tend to use tinyint for boolean columns, but if I was going to use an enum, why not go with enum(‘yes’,’no’) or similar?
@Paul:
I didn’t want an enum(‘0′,’1’) – the developers of a piece of software I was trying to work with did!
You’re absolutely right though that setting an integer value for an enum type is referencing a 1-based array. I actually briefly thought of that, but discounted it, as I expected it to be zero-based – what a muppet eh? :)