mysql 字段唯一约束

-- 创建表并添加唯一约束
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE
);

-- 插入数据（违反唯一约束）
INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com');

-- 解决方法1：先检查再插入
SELECT * FROM users WHERE username = 'john_doe' OR email = 'john@example.com';
INSERT INTO users (username, email) SELECT 'john_doe', 'john@example.com' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM users WHERE username = 'john_doe' OR email = 'john@example.com');

-- 解决方法2：使用INSERT IGNORE
INSERT IGNORE INTO users (username, email) VALUES ('john_doe', 'john@example.com');

-- 解决方法3：使用ON DUPLICATE KEY UPDATE
INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com') ON DUPLICATE KEY UPDATE username=VALUES(username), email=VALUES(email);

-- 删除唯一约束
ALTER TABLE users DROP INDEX username;