mysql复合主键自增

DELIMITER //

CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employee
FOR EACH ROW
BEGIN
    SET NEW.id = (SELECT IFNULL(MAX(id), 0) + 1 FROM employee);
END;
//

DELIMITER ;

import mysql.connector

db = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="yourdatabase"
)

cursor = db.cursor()

# 查询当前最大id
cursor.execute("SELECT MAX(id) FROM employee")
result = cursor.fetchone()
next_id = result[0] + 1 if result[0] else 1

# 插入数据
sql = "INSERT INTO employee (id, name, department) VALUES (%s, %s, %s)"
val = (next_id, "John Doe", "IT")
cursor.execute(sql, val)

db.commit()
cursor.close()
db.close()