mysql 实现非主键自增

-- 创建一个示例表
CREATE TABLE example (
    id INT PRIMARY KEY AUTO_INCREMENT,
    custom_id INT NOT NULL,
    data VARCHAR(255)
);

-- 创建一个序列表
CREATE TABLE sequence (
    seq_name VARCHAR(50) PRIMARY KEY,
    next_val INT NOT NULL
);

-- 初始化序列
INSERT INTO sequence (seq_name, next_val) VALUES ('custom_id_seq', 1);

-- 创建触发器
DELIMITER $$
CREATE TRIGGER before_example_insert
BEFORE INSERT ON example
FOR EACH ROW
BEGIN
    SET NEW.custom_id = (SELECT next_val FROM sequence WHERE seq_name = 'custom_id_seq');
    UPDATE sequence SET next_val = next_val + 1 WHERE seq_name = 'custom_id_seq';
END$$
DELIMITER ;

-- 插入数据
INSERT INTO example (data) VALUES ('test data');

-- 查询结果
SELECT * FROM example;

import mysql.connector

# 连接数据库
db = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="yourdatabase"
)

cursor = db.cursor()

# 查询当前最大custom_id
cursor.execute("SELECT MAX(custom_id) FROM example")
max_custom_id = cursor.fetchone()[0] or 0

# 插入数据
new_custom_id = max_custom_id + 1
sql = "INSERT INTO example (custom_id, data) VALUES (%s, %s)"
val = (new_custom_id, 'test data')
cursor.execute(sql, val)

db.commit()

# 查询结果
cursor.execute("SELECT * FROM example")
result = cursor.fetchall()
for row in result:
    print(row)