mysql 检查是否重复

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) UNIQUE,
    email VARCHAR(255)
);

SELECT username, COUNT(*)
FROM users
GROUP BY username
HAVING COUNT(*) > 1;

try {
    // 尝试插入数据
    String sql = "INSERT INTO users (username, email) VALUES (?, ?)";
    PreparedStatement pstmt = connection.prepareStatement(sql);
    pstmt.setString(1, "exampleUser");
    pstmt.setString(2, "user@example.com");
    pstmt.executeUpdate();
} catch (SQLException e) {
    if (e.getErrorCode() == 1062) { // MySQL错误代码1062表示违反唯一约束
        System.out.println("用户名已存在");
    } else {
        e.printStackTrace();
    }
}

-- 添加索引
ALTER TABLE users ADD INDEX idx_username (username);

-- 分页查询
SELECT username, COUNT(*)
FROM users
GROUP BY username
HAVING COUNT(*) > 1
LIMIT 10 OFFSET 0;