MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE IF NOT EXISTS article (
  `id`        INT(11) NOT NULL AUTO_INCREMENT,
  `order`     INT(11) UNIQUE,
  PRIMARY KEY (`id`)
);

INSERT INTO article
VALUES
  (1, 0),
  (2, 1),
  (3, 2),
  (4, 3),
  (5, 4),
  (6, 5),
  (7, 6),
  (8, 7),
  (9, 8),
  (10, 9);
--
-- move id 3 to position 6
--
SET @id := 3;
SET @orig := (select `order` from article where id = @id);
SET @dest := 6;
-- select @id id,@orig orig ,@dest dest;

UPDATE article 
SET `order` = NULL
WHERE id = @id;

UPDATE article 
SET `order` =  
    case 
    when  @orig < @dest and `order` between @orig and @dest 
     then `order` - 1
    when  @orig > @dest and `order` between @dest and @orig 
     then `order` + 1
    when `order` is null then @dest
    else `order`
    end
WHERE (
      `order` is null 
   OR `order` between least(@orig,@dest) and greatest(@dest,@orig)
)
ORDER BY if(@orig>@dest,1,-1)*`order` DESC;
SELECT * 
FROM article
ORDER BY article.order;


id
order


1
0

2
1

4
2

5
3

6
4

7
5

3
6

8
7

9
8

10
9