如果表被定义为MyISAM或InnoDB，SQL查询对COUNT(*)的评估方式会有所不同

Question

我正在运行一个MySQL数据库。

我有以下脚本：

DROP TABLE IF EXISTS `org_apiinteg_assets`;
DROP TABLE IF EXISTS `assessmentinstances`;
CREATE TABLE `org_apiinteg_assets` (
  `id` varchar(20) NOT NULL default '0',
  `instance_id` varchar(20) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE= MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=1;
CREATE TABLE `assessmentinstances` (
  `id` varchar(20) NOT NULL default '0',
  `title` varchar(180) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE= MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=1;

INSERT INTO assessmentinstances(id, title) VALUES ('14026lvplotw6','One radio question survey');
INSERT INTO org_apiinteg_assets(id, instance_id) VALUES ('8kp9wgx43jflrgjfe','14026lvplotw6');

看起来像这样

assessmentinstances
+---------------+---------------------------+
|        id     |          title            |
+---------------+---------------------------+
| 14026lvplotw6 | One radio question survey |
+---------------+---------------------------+
org_apiinteg_assets
+-------------------+---------------+
|        id         |  instance_id  |
+-------------------+---------------+
| 8kp9wgx43jflrgjfe | 14026lvplotw6 |
+-------------------+---------------+

然后我有以下查询(我将其简化为最简单的失败查询(

SELECT ai.id, COUNT(*) AS `count` 
FROM assessmentinstances ai, org_apiinteg_assets a 
WHERE a.instance_id = ai.id 
AND ai.id = '14026lvplotw6'
AND a.id != '8kp9wgx43jflrgjfe';

当我运行查询时，我得到这个

null, 0

直到现在，一切都很好。现在，这是我的问题，当我使用 ENGINE=InnoDB 而不是 ENGINE=MyISAM 重新创建两个表并再次运行相同的查询时，我得到这个：

'14026lvplotw6','0'

所以有两件事让我感到困惑：

• 为什么我没有得到相同的结果？
• 当COUNT(*)实际返回行的值时，如何在第二种情况下返回 0，因此应为 1？

我很迷路，如果有人能向我解释这种行为，我将不胜感激。

编辑：有趣的是，如果我在查询末尾添加GROUP BY ai.id，它在这两种情况下都可以正常工作，并且不返回任何行。

Answer 1

发生这种情况是因为您在使用聚合函数而不GROUP BY..在这种情况下，非聚合列的结果是不可预测的。(通常显示查询期间遇到的第一个值(

尝试添加GROUP BY

SELECT ai.id, COUNT(*) AS `count` 
FROM assessmentinstances ai, org_apiinteg_assets a 
WHERE a.instance_id = ai.id 
AND a.id != '8kp9wgx43jflrgjfe'
AND ai.id = '14026lvplotw6' 
GROUP BY  ai.id;

请记住，在 Group By 中未提及的列的情况下使用聚合在 SQL 中已弃用，并且在大多数数据库和较新版本的 mysql 中不允许使用聚合(从 5.7 开始(

Answer 2

MyISAM 返回的EXPLAIN SELECT：Impossible WHERE noticed after reading const tables 。所以MyISAM根本不处理任何数据。

对于InnoDB有两行EXPLAIN结果：一行Using Index，一行Using where 。因此，正在扫描InnoDB数据并将其中的位滑入输出，因为没有为第一列指定聚合函数，并且AFAIK没有指定在这种情况下应该发生的情况。如果直接指定某个聚合函数，则如果没有匹配的行，它将返回 NULL。因此，例如，SELECT min(ai.id), COUNT(*) ...将返回NULL, 0 .