MySQL行排序：拖拽TableView后调整items表顺序的SQL问题

Hey there! Let's work through this reordering problem for your MySQL table. First off, I’m assuming your items table has a numeric sort field (let’s call it sort_order — if you don’t have one yet, we’ll add it first) that controls the display order in your drag-and-drop TableView. Here’s a step-by-step, reliable solution to move the 'test2' entry between 'test5' and 'test6':

If your items table doesn’t already have a column to track order, add one first:

ALTER TABLE items ADD COLUMN sort_order INT NOT NULL DEFAULT 0;

If you need to initialize the sort order to match your current table’s display sequence, run this to set sequential values:

SET @row_number = 0;
UPDATE items SET sort_order = (@row_number := @row_number + 1) ORDER BY id; -- Replace `id` with your default sort column if needed

First, fetch the existing sort_order values for the three entries involved. Replace name with the actual column name that stores your entry labels (like 'test2', 'test5'):

SELECT sort_order INTO @test2_sort FROM items WHERE name = 'test2';
SELECT sort_order INTO @target_after_sort FROM items WHERE name = 'test5'; -- We want to place test2 AFTER this entry
SELECT sort_order INTO @target_before_sort FROM items WHERE name = 'test6'; -- We want to place test2 BEFORE this entry

We need two different sets of queries depending on whether test2 is currently before or after the target position. Wrap these in a transaction to avoid partial updates:

Case 1: test2 is currently BEFORE test5 #

If @test2_sort < @target_after_sort, we’ll shift the entries between test2’s old position and test5 forward by one, then set test2 to test5’s original sort position:

START TRANSACTION;

UPDATE items 
SET sort_order = sort_order - 1 
WHERE sort_order > @test2_sort AND sort_order <= @target_after_sort;

UPDATE items 
SET sort_order = @target_after_sort 
WHERE name = 'test2';

COMMIT;

Case 2: test2 is currently AFTER test6 #

If @test2_sort > @target_before_sort, we’ll shift the entries between test6 and test2’s old position backward by one, then set test2 to test6’s original sort position:

START TRANSACTION;

UPDATE items 
SET sort_order = sort_order + 1 
WHERE sort_order >= @target_before_sort AND sort_order < @test2_sort;

UPDATE items 
SET sort_order = @target_before_sort 
WHERE name = 'test2';

COMMIT;

To confirm the reorder worked, query the table sorted by your sort_order column:

SELECT * FROM items ORDER BY sort_order ASC;

Quick Notes #

• If you’re running these queries from an application, you can first fetch the three sort values in your code, then conditionally execute the appropriate transaction block.
• Using a transaction ensures that if any step fails, none of the changes are applied, keeping your data consistent.

内容的提问来源于stack exchange，提问作者Jonas Borneland