Persisting a Custom Sort Order in MySQL

Reading rows in a defined order is straightforward: store a position and use it in ORDER BY. The more interesting problem appears when a user rearranges an entire list in the browser and the server must persist the submitted sequence efficiently and safely.

MySQL 8 can turn a JSON array into rows with JSON_TABLE(). That makes it possible to validate and apply the complete order with one set-based UPDATE, without constructing SQL from comma-separated input or sending one statement per item.

Model the Stored Order

Each entry belongs to one user and carries a zero-based position:

CREATE TABLE entry (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(200) NOT NULL,
    sort_position INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    KEY idx_entry_user_order (user_id, sort_position, id)
);

The composite index supports both ownership filtering and ordered retrieval. Including id as the final key gives equal positions a deterministic order:

SELECT id, title
FROM entry
WHERE user_id = 5
ORDER BY sort_position, id;

Suppose that query currently yields these identifiers:

[315, 321, 318, 320, 300]

After dragging an item in the interface, the client submits the desired order as a JSON array:

[315, 318, 321, 320, 300]

Turn the Array into Rows

JSON_TABLE() produces one relational row per array element. FOR ORDINALITY numbers those rows from one, so subtracting one gives the stored zero-based position:

SET @new_order = JSON_ARRAY(315, 318, 321, 320, 300);

SELECT
    requested.ordinality - 1 AS sort_position,
    requested.entry_id
FROM JSON_TABLE(
    @new_order,
    '$[*]' COLUMNS (
        ordinality FOR ORDINALITY,
        entry_id BIGINT UNSIGNED PATH '$' ERROR ON ERROR
    )
) AS requested;

The result is a temporary two-column relation:

sort_position  entry_id
0              315
1              318
2              321
3              320
4              300

Apply a Validated Order

A reorder request must satisfy three conditions before it changes anything:

• Every submitted identifier is unique.
• Every submitted entry belongs to the current user.
• The array contains all entries in that user's list.

The following statement checks all three conditions and updates the list only when they hold:

SET @user_id = 5;
SET @new_order = JSON_ARRAY(315, 318, 321, 320, 300);

WITH
requested AS (
    SELECT
        item.ordinality - 1 AS sort_position,
        item.entry_id
    FROM JSON_TABLE(
        @new_order,
        '$[*]' COLUMNS (
            ordinality FOR ORDINALITY,
            entry_id BIGINT UNSIGNED PATH '$' ERROR ON ERROR
        )
    ) AS item
),
valid_request AS (
    SELECT 1 AS valid
    FROM requested AS r
    LEFT JOIN entry AS owned
        ON owned.user_id = @user_id
       AND owned.id = r.entry_id
    HAVING COUNT(*) = COUNT(DISTINCT r.entry_id)
       AND COUNT(*) = COUNT(owned.id)
         AND JSON_TYPE(@new_order) = 'ARRAY'
       AND COUNT(*) = (
           SELECT COUNT(*)
           FROM entry
           WHERE user_id = @user_id
       )
)
UPDATE entry AS e
JOIN requested AS r ON r.entry_id = e.id
JOIN valid_request AS validation ON validation.valid = 1
SET e.sort_position = r.sort_position
WHERE e.user_id = @user_id;

The common table expression does not trust the client to describe the current list correctly. A non-array value, a duplicate, an unknown identifier, an entry owned by another user, or an omitted entry makes valid_request empty, so the update touches no rows. The ownership predicate also remains on the target table as a final authorization boundary.

The statement is atomic: other sessions see either the old positions or the new positions, not a partly reordered list. If inserts, deletions, and reorder operations may run concurrently for the same list, serialize them by locking the owning list or user row in a transaction, or reject stale requests with an optimistic version number.

Bind the JSON Value

In production code, replace @new_order and @user_id with parameter markers. Serialize the application array as JSON and bind it as one value. Do not interpolate identifiers into the SQL string:

$order = json_decode($requestBody, true, flags: JSON_THROW_ON_ERROR);

if (!array_is_list($order)) {
    throw new InvalidArgumentException('Expected a JSON array.');
}

$statement = $pdo->prepare($reorderSql);
$statement->execute([
    'new_order' => json_encode($order, JSON_THROW_ON_ERROR),
    'owner_for_join' => $authenticatedUserId,
    'owner_for_count' => $authenticatedUserId,
    'owner_for_update' => $authenticatedUserId,
]);

Here, $reorderSql contains :new_order in place of the JSON variable and the three distinct owner placeholders in place of the corresponding @user_id occurrences. Distinct names remain compatible with native PDO prepares. Parsing each JSON element as BIGINT UNSIGNED rejects values that cannot be converted as requested, while bound parameters prevent SQL injection. The database checks ownership and completeness independently of the client-side shape check.

Why Not Build FIELD() from CSV?

A compact legacy pattern uses FIELD(id, ...) and inserts a comma-separated list directly into the statement. It has several problems:

• The SQL text changes with every request and requires dynamic placeholder generation.
• String concatenation creates an injection risk.
• Missing identifiers receive 0 from FIELD() unless the update is restricted carefully.
• Duplicates, foreign entries, and incomplete lists are not validated by the expression.

FIELD() remains convenient for a small, trusted, one-off ordering expression. For persisted client input, a JSON value and a relational join preserve the boundary between data and SQL.

Statement Count Is Not Time Complexity

Replacing a client loop with one update removes $n$ database round trips, but it does not make the operation $O(1)$. The server still parses $n$ identifiers and updates $n$ rows, so the work remains $O(n)$. The improvement is one request, one atomic statement, and set-based execution.

For short user-managed lists, rewriting every position is usually the simplest correct design. Very large lists that change one item at a time may benefit from sparse or fractional position keys, followed by occasional normalization. That trades simpler writes for more involved key management and concurrency rules.

References

• MySQL Oracle, MySQL 8.4 Reference Manual: JSON Table Functions