为什么 mysql 的 where 之间无法使用 = 检索 bool 数值?

表结构

CREATE TABLE `tmp_rt57517_20230407` (
  `video_id` int(10) unsigned NOT NULL,
  `key_id` varchar(64) NOT NULL COMMENT 'key id from tracking website',
  `trackingWebsite_id` smallint(5) unsigned NOT NULL COMMENT 'tracking website id',
  `is_svddb` enum('true','false') NOT NULL DEFAULT 'true' COMMENT 'filter meta by release date',
  `is_svddb_match` enum('true','false') NOT NULL DEFAULT 'true' COMMENT 'filter meta by release date',
  `match_count` int(11) DEFAULT NULL COMMENT '匹配到几个母本',
  PRIMARY KEY (`video_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

查询:

test> select * from tmp_rt57517_20230407 limit 20; 
+----------+------------------------------------------+--------------------+----------+----------------+-------------+
| video_id | key_id                                   | trackingWebsite_id | is_svddb | is_svddb_match | match_count |
+----------+------------------------------------------+--------------------+----------+----------------+-------------+
| 692322   | ac10624689                               | 62387              | false    | false          | <null>      |
| 840950   | ac22481663                               | 62387              | false    | false          | <null>      |
| 840967   | ac22480945                               | 62387              | false    | false          | <null>      |
| 840970   | ac22480886                               | 62387              | false    | false          | <null>      |
| 954778   | ac22309422                               | 62387              | false    | false          | <null>      |
| 3089164  | ac4892812                                | 62387              | false    | false          | <null>      |
| 5057932  | ac20108789                               | 62387              | false    | false          | <null>      |
| 5058053  | ac19993753                               | 62387              | false    | false          | <null>      |
| 5345601  | ac27443729                               | 62387              | false    | false          | <null>      |
| 6321042  | 3xa3rdn7fitybn6                          | 65134              | false    | false          | <null>      |
| 8286417  | ac15802695                               | 62387              | false    | false          | <null>      |
| 8565348  | cbe03e2e27fa8ae02fa7610b2997875f         | 346                | false    | false          | <null>      |
| 11902698 | dXMvMzQ5MzExOTUyLzI1NzE5MzI5MS5zaHRtbA== | 50                 | false    | false          | <null>      |
| 13356859 | b0979xfjxvk                              | 65088              | false    | false          | <null>      |
| 13357600 | n3061x4uv47                              | 65088              | false    | false          | <null>      |
| 14533625 | p093947flub                              | 65088              | false    | false          | <null>      |
| 14727323 | t09318q679z                              | 65088              | false    | false          | <null>      |
| 15017212 | h3102ndylt8                              | 65088              | false    | false          | <null>      |
| 15370451 | p0965hj95gh                              | 65088              | false    | false          | <null>      |
| 16204303 | m30655a0d4n                              | 65088              | false    | false          | <null>      |
+----------+------------------------------------------+--------------------+----------+----------------+-------------+

20 rows in set
Time: 0.008s

明明有 is_svddb_match = false,但是 count 就是 0?为什么?

test> select count(*) from tmp_rt57517_20230407 where is_svddb_match = false; 
+----------+
| count(*) |
+----------+
| 0        |
+----------+

1 row in set
Time: 0.008s
test>

test> select is_svddb_match, count(*) from tmp_rt57517_20230407 group by is_svddb_match;
+----------------+----------+
| is_svddb_match | count(*) |
+----------------+----------+
| false          | 5960     |
| true           | 45       |
+----------------+----------+

where is_svddb_match = 0 有结果

test> select * from tmp_rt57517_20230407 where is_svddb_match = 1 limit 20; 
+-----------+---------------------+--------------------+----------+----------------+-------------+
| video_id  | key_id              | trackingWebsite_id | is_svddb | is_svddb_match | match_count |
+-----------+---------------------+--------------------+----------+----------------+-------------+
| 275779907 | 6475104023140631053 | 64332              | true     | true           | 0           |
| 279461052 | 3xhwpd2epwqyu3e     | 65134              | true     | true           | 0           |
| 283964496 | 3xbr4a2rprht5uw     | 65134              | true     | true           | 0           |
| 284175991 | 3xss767ixb2qjyg     | 65134              | true     | true           | 0           |
| 289997791 | 3xjes3ti73fnbjs     | 65134              | true     | true           | 0           |
| 301838221 | 3xqpypnqitchjgm     | 65134              | true     | true           | 0           |
| 301932463 | 3x25ge25c9g3rpg     | 65134              | true     | true           | 0           |
| 301990424 | 3xu7npkhsbxzhsc     | 65134              | true     | true           | 0           |
| 303408488 | 3xjcx3qky3ujhqe     | 65134              | true     | true           | 1           |
| 304595913 | 3xnbkigvdbpz2na     | 65134              | true     | true           | 0           |
| 343205878 | 3xxbbzv84fcah6g     | 65134              | true     | true           | 0           |
| 366995789 | 3xf6ez29jppdqwe     | 65134              | true     | true           | 0           |
| 367138943 | 3xcuvkt76n7wa6q     | 65134              | true     | true           | 0           |
| 367140093 | 3xm7tdrr4r9bfc6     | 65134              | true     | true           | 0           |
| 367140153 | 3x28psqt5gds3dy     | 65134              | true     | true           | 0           |
| 367547800 | 3xcxbic44gba46i     | 65134              | true     | true           | 0           |
| 368914522 | 3xstmp9928279e4     | 65134              | true     | true           | 0           |
| 369027752 | 3x32qs6r2y96iyu     | 65134              | true     | true           | 0           |
| 369069949 | 3xy6yrdmuijgp84     | 65134              | true     | true           | 0           |
| 369137177 | 3xucabuesbg6d8a     | 65134              | true     | true           | 0           |
+-----------+---------------------+--------------------+----------+----------------+-------------+

但是 where is_svddb_match = 0 没有结果

test> select * from tmp_rt57517_20230407 where is_svddb_match = 0 limit 20; 
+----------+--------+--------------------+----------+----------------+-------------+
| video_id | key_id | trackingWebsite_id | is_svddb | is_svddb_match | match_count |
+----------+--------+--------------------+----------+----------------+-------------+
+----------+--------+--------------------+----------+----------------+-------------+

0 rows in set
Time: 0.008s

为什么?

阅读 2.1k
2 个回答

enum 的 index 是从 1 开始的。在上面的定义中,'true'1'false'2 。所以判断 =0 是没有的,但是 =1 是有值的。

true / false 在 mysql 里就是 10

在 enum 值长得比较像整数的时候,会引起很多比较混乱的情况,参见Index Values for Enumeration Literals。直接写 = false ,会被认为是 = 0。需要 = 'false' 才能匹配 enum 串。

你用enum还不如用tinyint

撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进
推荐问题
宣传栏