mysql在指定位置插入记录

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_number VARCHAR(255),
    order_date DATE
);

INSERT INTO orders (order_number, order_date) VALUES ('ORD123', '2023-10-01');

-- 创建临时表
CREATE TEMPORARY TABLE temp_orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_number VARCHAR(255),
    order_date DATE
);

-- 插入数据到临时表
INSERT INTO temp_orders (order_number, order_date) VALUES ('ORD124', '2023-10-02');

-- 将临时表数据插入到目标表的指定位置
INSERT INTO orders (order_number, order_date)
SELECT * FROM temp_orders
ORDER BY order_number
LIMIT 1;

-- 删除临时表
DROP TEMPORARY TABLE temp_orders;

DELIMITER //

CREATE TRIGGER after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    -- 在这里编写逻辑来调整数据顺序
END;

//

DELIMITER ;