SQL中count(1)、count(*) 与 count(列名)的区别详细解释

目录

• 前言
• 1. COUNT(1)
• 2. COUNT(*)
• 3. COUNT(列名)
• 示例代码
• 执行速度
• 详细解释
• 1. COUNT(1)
• 2. COUNT(*)
• 3. COUNT(列名)
• 示例验证
• 创建测试表并插入数据
• 执行计数查询并记录执行时间
• 总结

SELECT COUNT(1) FROM employees;

SELECT COUNT(*) FROM employees;

SELECT COUNT(department_id) FROM employees;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class CountExample {
    private static final String JDBC_URL = "jdbc:mysql://localhost:3306/yourdatabase";
    private static final String JDBC_USER = "yourusername";
    private static final String JDBC_PASSWORD = "yourpassword";

    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
             Statement stmt = conn.createStatement()) {

            // 使用 COUNT(1)
            String count1SQL = "SELECT COUNT(1) AS total FROM employees";
            ResultSet rs1 = stmt.executeQuery(count1SQL);
            if (rs1.next()) {
                int total1 = rs1.getInt("total");
                System.out.println("Total rows (COUNT(1)): " + total1);
            }

            // 使用 COUNT(*)
            String countAllSQL = "SELECT COUNT(*) AS total FROM employees";
            ResultSet rsAll = stmt.executeQuery(countAllSQL);
            if (rsAll.next()) {
                int totalAll = rsAll.getInt("total");
                System.out.println("Total rows (COUNT(*)): " + totalAll);
            }

            // 使用 COUNT(column)
            String countColumnSQL = "SELECT COUNT(department_id) AS total FROM employees";
            ResultSet rsColumn = stmt.executeQuery(countColumnSQL);
            if (rsColumn.next()) {
                int totalColumn = rsColumn.getInt("total");
                System.out.println("Total rows (COUNT(department_id)): " + totalColumn);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    department_id INT,
    name VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入大量数据
INSERT INTO employees (department_id, name) 
SELECT FLOOR(RAND() * 10), CONCAT('Employee', FLOOR(RAND() * 1000))
FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t1
CROSS JOIN (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t2
CROSS JOIN (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t3
CROSS JOIN (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t4
CROSS JOIN (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) t5;

-- 计时 COUNT(1)
SET @start_time = NOW(6);
SELECT COUNT(1) FROM employees;
SELECT TIMEDIFF(NOW(6), @start_time) AS execution_time;

-- 计时 COUNT(*)
SET @start_time = NOW(6);
SELECT COUNT(*) FROM employees;
SELECT TIMEDIFF(NOW(6), @start_time) AS execution_time;

-- 计时 COUNT(department_id)
SET @start_time = NOW(6);
SELECT COUNT(department_id) FROM employees;
SELECT TIMEDIFF(NOW(6), @start_time) AS execution_time;