MySQL如何按枚举分组统计并包含计数为0的枚举值？

嘿，这个需求我之前也碰到过，MySQL默认用GROUP BY category统计的时候，确实会漏掉那些没有对应数据的枚举值。不过有两种靠谱的解决思路，我给你详细拆解下：

方法一：手动构造所有枚举值（简单直观，适合枚举固定的场景） #

如果你的category枚举值是固定不变的，直接用UNION ALL构造一个包含所有枚举值的临时表，再和items表做LEFT JOIN就能拿到包含0计数的统计结果了。

示例代码：

SELECT
  enum_cats.category,
  COUNT(items.id) AS item_count
FROM
  -- 这里列出所有枚举值，顺序可与枚举定义一致
  (SELECT 'one' AS category UNION ALL
   SELECT 'two' AS category UNION ALL
   SELECT 'three' AS category) AS enum_cats
LEFT JOIN items ON enum_cats.category = items.category
GROUP BY enum_cats.category
ORDER BY enum_cats.category;

关键细节： #

• 一定要用LEFT JOIN：这样即使items表里没有对应分类的数据，临时表的枚举值也会被保留。
• 统计时用COUNT(items.id)而非COUNT(*)：LEFT JOIN后无匹配行的items.id为NULL，COUNT()会忽略NULL值，刚好得到0；如果用COUNT(*)，会把临时表的行算进去，结果会变成1，这不是我们想要的。

方法二：动态获取枚举值（适合枚举可能变动的场景） #

如果你的category枚举值可能会修改，每次改枚举都要改SQL太麻烦，可以从MySQL的元数据中动态提取所有枚举值。

示例代码：

SELECT
  enum_cats.category,
  COUNT(items.id) AS item_count
FROM
  (
    -- 从information_schema中提取枚举的定义字符串
    SELECT TRIM(BOTH "'" FROM SUBSTRING_INDEX(SUBSTRING_INDEX(col_type, "'", 2 + nums.n), "'", -1)) AS category
    FROM
      (
        SELECT column_type AS col_type
        FROM information_schema.COLUMNS
        WHERE 
          table_schema = '你的数据库名'  -- 替换成你的数据库名称
          AND table_name = 'items'
          AND column_name = 'category'
      ) AS enum_def,
      -- 这里的数字要覆盖枚举值的最大数量，比如枚举有5个值就写到4（从0开始）
      (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS nums
    WHERE nums.n < (LENGTH(col_type) - LENGTH(REPLACE(col_type, "'", ""))) / 2
  ) AS enum_cats
LEFT JOIN items ON enum_cats.category = items.category
GROUP BY enum_cats.category
ORDER BY enum_cats.category;

注意事项： #

• 你需要有访问information_schema的权限，如果没有的话，还是用方法一更稳妥。
• nums子查询里的数字要足够多，比如如果你的枚举有10个值，就要加到SELECT 9，保证能覆盖所有枚举项。

带过滤条件的情况 #

如果需要统计特定条件下的数据（比如某个时间范围内的条目），记得把过滤条件放到LEFT JOIN的ON子句里，而不是WHERE子句，否则会把计数为0的行过滤掉：

SELECT
  enum_cats.category,
  COUNT(items.id) AS item_count
FROM
  (SELECT 'one' AS category UNION ALL SELECT 'two' UNION ALL SELECT 'three') AS enum_cats
LEFT JOIN items 
  ON enum_cats.category = items.category 
  AND items.created_at >= '2024-01-01'  -- 过滤条件放到ON里
GROUP BY enum_cats.category;

内容的提问来源于stack exchange，提问作者voutasaurus