Drop Shadow

Text vs. Numbers in MySQL 5.0

When checking text against a numeric field, MySQL 5.0 will convert the text to a number first. Usually, this will result in the number “0”. For example:

SELECT * FROM Songs WHERE status = "Hello";

Under MySQL 4.0, if status is actually a numeric field, this will produce nothing. Under MySQL 5.0, this will produce all fields where status is 0 (or empty).

Similarly, if the field is a text field and you check it against a number, MySQL 5.0 will convert the field to a number.

SELECT * FROM Songs WHERE Artist = 0;

Under MySQL 4.0, if Artist is a text field this will produce only songs whose artist actually is “0”. Under MySQL 5.0, this will produce all artists whose names evaluate to zero: likely most of them.

IN lists

If you have an “IN” list in your WHERE statements, check to make sure that you are not mixing numbers with text. If you are, MySQL 5.0 will convert all instances of “TEXT” to the number 0 (assuming that the text isn’t also a number, both in the query and in the field you’re checking against. For example:

SELECT * FROM Songs WHERE SongID IN (198, 'Edwards');

Under MySQL 4.0, the above SQL statement would produce all songs with the ID “198” or “Edwards”. Under MySQL 5.0, it will produce all songs whose ID field evaluates to 198 or zero. It will, for example, show all songs whose ID is text and whose text does not evaluate to a number.

Solution

You shouldn’t be matching numbers against text. If this is not a text field of some kind, you shouldn’t be matching against text in the query: the field isn’t going to contain (in this case) “Edwards”, because that isn’t a number. If this is a text field that sometimes contains numbers, treat the number as text:

SELECT * FROM Songs WHERE SongID IN ('198', 'Edwards');