如何在PHP/MySQL中对含连字符的表ID进行降序排序？

Hey, I’ve dealt with this exact problem before! The issue here is that MySQL sorts string values lexicographically (character by character) instead of numerically. So when you sort id DESC directly, "20633-..." comes before "184945-..." because the first character "2" is larger than "1"—even though the actual numeric value of the first segment is smaller.

Here are two reliable solutions, depending on whether you can adjust your SQL query or need to handle sorting in PHP:

Option 1: Sort Directly in MySQL (Recommended) #

This is the better approach, especially for large datasets, since databases are optimized for sorting operations. We’ll extract the numeric segment before the hyphen, cast it to an integer, and sort by that value.

Basic Sort (By First Segment) #

SELECT * FROM your_table 
WHERE id IS NOT NULL -- Replace with your actual WHERE condition
ORDER BY CAST(SUBSTRING_INDEX(id, '-', 1) AS UNSIGNED) DESC;

• SUBSTRING_INDEX(id, '-', 1) grabs everything before the first hyphen (e.g., "20633" from "20633-18489")
• CAST(... AS UNSIGNED) converts that string segment to a numeric value so we sort numerically, not lexicographically
• This will give you the exact order you want: 184945-190028 > 183661-188782 > 20633-18489 > 1575-1610

Sort by Both Segments (If Needed) #

If you ever need to sort by the second numeric segment when the first segments are identical, add a second sort condition:

SELECT * FROM your_table 
WHERE id IS NOT NULL
ORDER BY CAST(SUBSTRING_INDEX(id, '-', 1) AS UNSIGNED) DESC,
         CAST(SUBSTRING_INDEX(id, '-', -1) AS UNSIGNED) DESC;

SUBSTRING_INDEX(id, '-', -1) grabs everything after the last hyphen (the second numeric segment).

Option 2: Sort in PHP #

If you can’t modify the SQL query for some reason, you can sort the result set directly in PHP using a custom comparator.

Example Code #

// Assume $dbResults is your fetched array from the database
$dbResults = [
    ['id' => '20633-18489'],
    ['id' => '184945-190028'],
    ['id' => '183661-188782'],
    ['id' => '1575-1610'],
];

// Use usort with a custom callback to sort numerically
usort($dbResults, function($a, $b) {
    // Split each ID into its two numeric segments
    $aSegments = explode('-', $a['id']);
    $bSegments = explode('-', $b['id']);
    
    // Compare the first segments (descending order)
    $firstCmp = (int)$bSegments[0] - (int)$aSegments[0];
    if ($firstCmp !== 0) {
        return $firstCmp;
    }
    
    // If first segments are equal, compare the second segments (descending)
    return (int)$bSegments[1] - (int)$aSegments[1];
});

// Now $dbResults is sorted as expected
print_r($dbResults);

• usort lets you define your own sorting logic via an anonymous function
• We split each ID with explode('-', $id), convert the segments to integers, then compare them (subtract $a from $b to get descending order)

Final Notes #

Always prefer the SQL solution if possible—it’s faster, especially with large datasets, and offloads the sorting work to the database where it’s most efficient. The PHP solution works great for small result sets or when you can’t adjust the query.

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