mysql 字段匹配字符串,MySQL整数字段匹配字符串怎么办？

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.

解决方案

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)