mysql 实现非主键自增长

-- 创建一个示例表
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    order_number VARCHAR(20) NOT NULL,
    order_date DATE
);

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

-- 初始化序列表
INSERT INTO order_sequence (seq_name, current_value) VALUES ('order_number_seq', 1000);

-- 创建触发器
DELIMITER $$
CREATE TRIGGER before_order_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    DECLARE next_value INT;
    SELECT current_value INTO next_value FROM order_sequence WHERE seq_name = 'order_number_seq';
    SET NEW.order_number = CONCAT('ORD', LPAD(next_value, 5, '0'));
    UPDATE order_sequence SET current_value = current_value + 1 WHERE seq_name = 'order_number_seq';
END$$
DELIMITER ;

import mysql.connector

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

cursor = db.cursor()

# 获取当前序列值
cursor.execute("SELECT current_value FROM order_sequence WHERE seq_name = 'order_number_seq'")
result = cursor.fetchone()
current_value = result[0] + 1

# 插入数据
order_number = f"ORD{current_value:05d}"
sql = "INSERT INTO orders (order_number, order_date) VALUES (%s, %s)"
val = (order_number, "2023-10-01")
cursor.execute(sql, val)

# 更新序列值
update_sql = "UPDATE order_sequence SET current_value = %s WHERE seq_name = 'order_number_seq'"
cursor.execute(update_sql, (current_value,))

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