MySQL 8.4

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE my_table (
    user_name VARCHAR(100) NOT NULL,
    idx INT NOT NULL,
    some_data TEXT,
    PRIMARY KEY (user_name, idx)
);

INSERT INTO my_table (user_name, idx, some_data) VALUES
('Bob', 1, 'First'),
('Bob', 2, 'Second'),
('Bob', 3, 'Third');

Records: 3  Duplicates: 0  Warnings: 0


SELECT * FROM my_table;


user_name
idx
some_data


Bob
1
First

Bob
2
Second

Bob
3
Third


UPDATE my_table SET idx = idx + 1 WHERE user_name = 'Bob';

Duplicate entry 'Bob-2' for key 'my_table.PRIMARY'


UPDATE my_table
SET idx = idx + 1
WHERE user_name = 'Bob'
ORDER BY idx DESC;

Rows matched: 3  Changed: 3  Warnings: 0


SELECT * FROM my_table;


user_name
idx
some_data


Bob
2
First

Bob
3
Second

Bob
4
Third