mysql字段设置唯一约束条件

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    product_id INT NOT NULL,
    UNIQUE (user_id, product_id)
);

-- 检查值是否存在
SELECT COUNT(*) FROM users WHERE email = 'example@example.com';

-- 使用INSERT IGNORE
INSERT IGNORE INTO users (username, email) VALUES ('user1', 'example@example.com');

-- 使用ON DUPLICATE KEY UPDATE
INSERT INTO users (username, email) VALUES ('user1', 'example@example.com')
ON DUPLICATE KEY UPDATE username = 'user1';

ALTER TABLE users DROP INDEX email;