如何在MySQL中插入最新记录并停用其他记录

CREATE TABLE records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO records (name) VALUES ('New Record');

UPDATE records
SET status = 'inactive'
WHERE id != (SELECT MAX(id) FROM records);

START TRANSACTION;

INSERT INTO records (name) VALUES ('New Record');

UPDATE records
SET status = 'inactive'
WHERE id != (SELECT MAX(id) FROM records);

COMMIT;

CREATE INDEX idx_id ON records(id);

START TRANSACTION;

SELECT MAX(id) INTO @latest_id FROM records FOR UPDATE;

INSERT INTO records (name) VALUES ('New Record');

UPDATE records
SET status = 'inactive'
WHERE id != @latest_id;

COMMIT;