如何在排序项之间插入一个项SQL

Question

我有一个这样的表:

id     name  family  order
861    a     a       1
5241   b     b       2
15     c     c       3
251    d     d       4
.
.
.
541    e     e       9001
41     f     f       9002
8161   g     g       9003

当我想要获取我的数据时，我使用这个查询:

SELECT * FROM users ORDER BY order ASC;

但是我还需要在id为861和5241的两个项目之间插入一个项目。这样的话，我需要改变所有行的顺序

UPDATE users SET order=9004 WHERE order=9003;
UPDATE users SET order=9003 WHERE order=9002;
UPDATE users SET order=9002 WHERE order=9001;
.
.
.
UPDATE users SET order=4 WHERE order=3;
UPDATE users SET order=3 WHERE order=2;
INSERT INTO users (name, family, order) VALUES ('z', 'z', 2);

我们知道这是一个非常糟糕的主意。有没有更好的办法?

Answer 1

像这样:

START TRANSACTION;
UPDATE users SET order_c = order_c+1 where order_c >= 2;
insert into users values(123, 'f', 'f', 2);
COMMIT;

这是一个演示

我想这是@EdmCoff推荐的?

Answer 2

给定

select * from t;
+------------+----------+
| dte        | order_id |
+------------+----------+
| 2018-01-02 |        1 |
| 2018-01-03 |        2 |
| 2018-01-04 |        3 |
| 2018-01-05 |        4 |
| 2018-01-06 |        5 |
| 2018-01-07 |        6 |
| 2018-01-08 |        7 |
| 2018-01-09 |        8 |
| 2018-01-10 |        9 |
| 2018-01-11 |       10 |
+------------+----------+

如果你想写代码却忘了，考虑

drop trigger if exists t;
delimiter $$
create trigger t after insert on t
for each row 
begin
insert into t1 values (new.dte,new.order_id,null,0);
end $$
delimiter ; 
create table t1 like t;
alter table t1
add column AI_order_id int auto_increment primary key,
add column new_order_id int;
alter table t1
add unique key (order_id,AI_order_id);
truncate table t1;
insert into t1  select dte,order_id, null,0 from t; #bulk load
#select * from t1;
insert into t values (date('2023-05-01'),2),(date('2024-05-01'),2),(date('2024-05-01'),10),(date('2024-05-01'),1);
update t1 join
(select order_id,AI_order_id, row_number() over(order by order_id,AI_order_id desc) rn from t1) s
on t1.order_id = s.order_id and t1.AI_order_id = s.AI_order_id
set new_order_id = s.rn
where 1 = 1;
update t join t1 on t1.order_id = t.order_id and t1.dte = t.dte
set    t.order_id = t1.new_order_id
where 1 = 1;
select * from t1 order by order_id,new_order_id;
+------------+----------+-------------+--------------+
| dte        | order_id | AI_order_id | new_order_id |
+------------+----------+-------------+--------------+
| 2024-05-01 |        1 |          19 |            1 |
| 2018-01-02 |        1 |           1 |            2 |
| 2024-05-01 |        2 |          17 |            3 |
| 2023-05-01 |        2 |          16 |            4 |
| 2018-01-03 |        2 |           2 |            5 |
| 2018-01-04 |        3 |           3 |            6 |
| 2018-01-05 |        4 |           4 |            7 |
| 2018-01-06 |        5 |           5 |            8 |
| 2018-01-07 |        6 |           6 |            9 |
| 2018-01-08 |        7 |           7 |           10 |
| 2018-01-09 |        8 |           8 |           11 |
| 2018-01-10 |        9 |           9 |           12 |
| 2024-05-01 |       10 |          18 |           13 |
| 2018-01-11 |       10 |          10 |           14 |
+------------+----------+-------------+--------------+
14 rows in set (0.001 sec)
select * from t  order by order_id;
+------------+----------+
| dte        | order_id |
+------------+----------+
| 2024-05-01 |        1 |
| 2018-01-02 |        2 |
| 2024-05-01 |        3 |
| 2023-05-01 |        4 |
| 2018-01-03 |        5 |
| 2018-01-04 |        6 |
| 2018-01-05 |        7 |
| 2018-01-06 |        8 |
| 2018-01-07 |        9 |
| 2018-01-08 |       10 |
| 2018-01-09 |       11 |
| 2018-01-10 |       12 |
| 2024-05-01 |       13 |
| 2018-01-11 |       14 |
+------------+----------+
14 rows in set (0.001 sec)