What is going on with MySQL integer field matching string?

Question

In my inherited database, sometimes binary questions are encoded as ('Yes', 'No'), and sometimes as (1,0). I mistakenly queried against the string 'Yes' on a field which was numerically encoded. My guess is that the string was turned into a '0' by MySQL.

I have a query like this:

SELECT `children_under_18`
  FROM `households`
 WHERE `children_under_18` = 'Yes'
 GROUP BY `children_under_18`

It ended up only matching records where children_under_18 was 0, the opposite of what I wanted. I know I need to be more careful. I am looking for a definitive answer as to what happened.

Jorge: children_under_18 is an int type. Usually the database designer chose to encode this type of value as enum('Yes', 'No'). I forgot what the encoding was for this field, which is how I obtained undesired results. — Bradley Slavik
– Bradley Slavik, Commented Nov 21, 2013 at 15:59

Jason Heo · Accepted Answer · 2013-11-21 00:57:52Z

1

String is always converted to 0 when compared to numeric (of course string containing numeric + string is converted numeric. but this is not good practice)

mysql> SELECT 'Yes' + 0;
+-----------+
| 'Yes' + 0 |
+-----------+
|         0 |
+-----------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'Yes' |
+---------+------+-----------------------------------------+

If you want store binary value (Yes/No or True/False) or small value set, ENUM is good choice. It takes small disk space and can use meaningful String.

mysql> CREATE TABLE enum_test(a ENUM('Yes', 'No'));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into enum_test values('Yes'), ('No'), ('Invalid');
Query OK, 3 rows affected, 1 warning (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 1

mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'a' at row 3 |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

mysql> select * from enum_test where a = 'Yes';
+------+
| a    |
+------+
| Yes  |
+------+
1 row in set (0.00 sec)

mysql> select * from enum_test where a = 'No';
+------+
| a    |
+------+
| No   |
+------+
1 row in set (0.00 sec)

answered Nov 21, 2013 at 0:57

Jason Heo

10.3k2 gold badges39 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

eggyal Over a year ago

For a simple boolean, it's arguable that ENUM may be appropriate (although the truncation of invalid values to the special empty-string value '' as demonstrated above, in addition to explicit NULL values if so permitted, may suggest otherwise). However, one nevertheless ought to read Chris Komlenic's article 8 Reasons Why MySQL's ENUM Data Type Is Evil before deploying it in a production system.

Jason Heo Over a year ago

@eggyal Great post it is! actually I hadn't used ENUM I'm just lazy, but int the future never use it, even if be industrious.

Collectives™ on Stack Overflow

What is going on with MySQL integer field matching string?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related