mysql 多字段主键自增

CREATE TABLE example (
    id INT,
    name VARCHAR(50),
    age INT,
    PRIMARY KEY (id, name)
);

DELIMITER $$
CREATE TRIGGER example_before_insert
BEFORE INSERT ON example
FOR EACH ROW
BEGIN
    SET NEW.id = (SELECT IFNULL(MAX(id), 0) + 1 FROM example WHERE name = NEW.name);
END $$
DELIMITER ;

CREATE TABLE example (
    id INT,
    name VARCHAR(50),
    age INT,
    PRIMARY KEY (id, name)
);

DELIMITER $$
CREATE TRIGGER example_before_insert
BEFORE INSERT ON example
FOR EACH ROW
BEGIN
    SET NEW.id = (SELECT IFNULL(MAX(id), 0) + 1 FROM example WHERE name = NEW.name);
END $$
DELIMITER ;

INSERT INTO example (name, age) VALUES ('Alice', 30);
INSERT INTO example (name, age) VALUES ('Bob', 25);
INSERT INTO example (name, age) VALUES ('Alice', 35);