其他关于这个问题的回答都没有说到重点。
假设我们有一张表:
CREATE TABLE `table` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`value` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `value` (`value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
有 10 行,id 和值都从 1 到 10:
INSERT INTO `table`(`id`, `value`) VALUES (1, 1),(2, 2),(3, 3),(4, 4),(5, 5),(6, 6),(7, 7),(8, 8),(9, 9),(10, 10);
尝试以下 2 个查询:
SELECT `value` v FROM `table` WHERE `value`>5; -- Get 5 rows
SELECT `value` v FROM `table` HAVING `value`>5; -- Get 5 rows
你会得到完全相同的结果,你可以看到HAVING
子句可以在没有 GROUP BY 子句的情况下工作。
区别如下:
SELECT `value` v FROM `table` WHERE `v`>5;
上面的查询将引发错误:错误#1054 - 'where 子句'中的未知列'v'
SELECT `value` v FROM `table` HAVING `v`>5; -- Get 5 rows
WHERE
子句允许条件使用任何表列,但不能使用别名或聚合函数。HAVING
子句允许条件使用选定的 (!) 列、别名或聚合函数。
这是因为WHERE
子句在 select 之前过滤数据,但是HAVING
子句过滤选择后的结果数据。
所以把条件代入WHERE
如果表中有很多行,子句会更有效。
Try EXPLAIN
查看主要区别:
EXPLAIN SELECT `value` v FROM `table` WHERE `value`>5;
+----+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+
| 1 | SIMPLE | table | range | value | value | 4 | NULL | 5 | Using where; Using index |
+----+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+
EXPLAIN SELECT `value` v FROM `table` having `value`>5;
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
| 1 | SIMPLE | table | index | NULL | value | 4 | NULL | 10 | Using index |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
你可以看到WHERE
or HAVING
使用索引,但行不同。