mysql中怎么按年查询

-- 查询2023年创建的用户数量
SELECT COUNT(*) AS user_count, YEAR(created_at) AS year
FROM users
WHERE YEAR(created_at) = 2023
GROUP BY YEAR(created_at);

-- 或者使用范围查询
SELECT COUNT(*) AS user_count, YEAR(created_at) AS year
FROM users
WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY YEAR(created_at);

CREATE INDEX idx_created_at ON users(created_at);

SELECT COUNT(*) AS user_count, YEAR(CONVERT_TZ(created_at, '+00:00', '+08:00')) AS year
FROM users
WHERE YEAR(CONVERT_TZ(created_at, '+00:00', '+08:00')) = 2023
GROUP BY YEAR(CONVERT_TZ(created_at, '+00:00', '+08:00'));