Featured Database Articles

MySQL Oddities - Page 2

More AUTO_INCREMENT oddities

As we have seen above,
AUTO_INCREMENT fields take the next value in the sequence as default, even when
specifically supplied a NULL value. Here are a few more cases where things are
not quite what they seem.

MySQL is behaving quite consistently here, but in a
surprising way. AUTO_INCREMENT fields, as we saw above, take the next value in
the sequence as default if you attempt to insert a NULL value. If you attempt
to insert a negative value, or a larger value than the numeric can handle, MySQL's
behavior is undefined. Running MySQL 5.0.2, MySQL successfully inserts a
negative number (other versions simply insert the next number in the sequence)
and the attempt to insert zero is treated the same as if you had inserted a
null (so 4 is inserted, the next in the sequence). Entering a number greater
than the integer can handle inserts a number equal to the maximum value of the
integer (other versions have also simply inserted the next in the sequence).
However, as soon as you attempt to insert a valid number (1 in this case),
MySQL will attempt to insert this actual number, and, being a primary key, this
fails since we already have a record with this value.

Not quite boolean

Since MySQL has no boolean data
types, many use the ENUM type, as follows:

mysql>
CREATE table boolean (
i1 INT,
b1 ENUM('0','1')
)

And that works fine in most
cases. However, it is easy to make a few mistakes. Here is a common one:

In spite of setting the default
to 1, MySQL has entered 0 as a value. What happened? Simple - ENUM is a
character type, so the default value should be a string. MySQL however does not
warn you of your error. Let's correct that mistake, and try again.

We have made the same mistake
as before, except this time in inserting the data. We have forgotten that b1
is a string, and our value should have been enclosed in quotes. The field i1
is NULL, as we would expect if we did not define it, as it has been defined to
accept NULLs

Conclusion

Most people do not work only in one DBMS, and if you have
moved from another DBMS, or plan to work on another in the future, do not let MySQL's
oddities catch you by surprise. There are still many more lurking and we will look
at some of these in future articles.