MySQL整数字段匹配字符串怎么办? [英] What is going on with MySQL integer field matching string?

问题描述

在我继承的数据库中，二进制问题有时编码为('是'，'否')，有时编码为(1,0).我在数字编码的字段上错误地针对字符串是"查询.我的猜测是该字符串被MySQL转换为"0".

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.

我有这样的查询:

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

它最终只匹配children_under_18为0的记录，这与我想要的相反.我知道我需要多加小心.我正在寻找一个明确的答案，以了解发生了什么事.

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.

推荐答案

与数字相比，字符串始终转换为0(当然，包含数字的字符串+转换为数字的字符串.但这不是一个好习惯)

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' |
+---------+------+-----------------------------------------+

如果要存储二进制值(是/否或真/假)或设置较小的值，ENUM是个不错的选择. 它占用较小的磁盘空间，并且可以使用有意义的String.

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)

这篇关于MySQL整数字段匹配字符串怎么办?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！