mysql查询生成序列号

-- 创建表
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE,
    customer_name VARCHAR(100)
);

-- 插入数据
INSERT INTO orders (order_date, customer_name) VALUES ('2023-10-01', 'John Doe');

-- 查询数据
SELECT * FROM orders;

-- 创建序列生成器存储过程
DELIMITER $$
CREATE PROCEDURE GenerateSerialNumber(INOUT serial_number INT)
BEGIN
    DECLARE current_serial INT;
    SELECT COALESCE(MAX(serial_number), 0) + 1 INTO current_serial FROM your_table;
    SET serial_number = current_serial;
END$$
DELIMITER ;

-- 使用存储过程生成序列号
SET @serial_number = 0;
CALL GenerateSerialNumber(@serial_number);
SELECT @serial_number;

ALTER TABLE your_table AUTO_INCREMENT = 1;

START TRANSACTION;
CALL GenerateSerialNumber(@serial_number);
INSERT INTO your_table (serial_number, column1, column2) VALUES (@serial_number, 'value1', 'value2');
COMMIT;

DELIMITER $$
CREATE PROCEDURE GenerateSerialNumber(INOUT serial_number INT)
BEGIN
    DECLARE current_serial INT;
    START TRANSACTION;
    SELECT COALESCE(MAX(serial_number), 0) + 1 INTO current_serial FROM your_table FOR UPDATE;
    SET serial_number = current_serial;
    COMMIT;
END$$
DELIMITER ;