MySQL按日統計是一個非常常見的需求。但是默認情況下，MySQL會自動忽略掉空值，也就是說，如果某一天沒有任何數據，那么該天就不會出現在統計結果中。這對于統計來講是一個不可接受的缺陷。

SELECT DATE_FORMAT(time, '%Y-%m-%d') AS date, COUNT(*) AS count 
FROM your_table
WHERE time BETWEEN '2021-01-01' AND '2021-01-31' 
GROUP BY date

上述代碼實現了MySQL按日統計的核心功能，但有時候會出現統計結果中沒有0的情況。這可能會導致結果分析出現偏差，因此我們需要在查詢結果中顯式地包含0。

SELECT date_table.date, COUNT(your_table.time) AS count 
FROM (
SELECT DATE_FORMAT('2021-01-01', '%Y-%m-%d') + INTERVAL a.day DAY AS date 
FROM (
SELECT t0 + t1 * 10 + t2 * 100 AS day 
FROM (
SELECT 0 AS t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION 
SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) AS a0 /* 10 */ 
CROSS JOIN (
SELECT 0 AS t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION 
SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) AS a1 /* 100 */ 
CROSS JOIN (
SELECT 0 AS t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION 
SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) AS a2 /* 1000 */ 
ORDER BY day
) AS days 
WHERE day BETWEEN 0 AND 30 /* 2021-01-01 ~ 2021-01-31 */ 
) AS date_table 
LEFT JOIN your_table 
ON DATE(your_table.time) = date_table.date 
GROUP BY date_table.date

上述代碼實現了MySQL按日統計并包含0的功能。它使用了一個名為“calendar table”的技巧，創建了一個有30個記錄的日期臨時表，根據這個臨時表和左連接原始表實現了方便、高效、準確的統計。