如何利用MySQL正则表达式实现test_varc字段的自定义排序？

Got it, let's break down how to get exactly the sorted output you want. First, let's map out the clear sorting rules from your input and desired result:

• First, keep all records with 80: in test_varc (your original IDs 0-3) at the top, sorted by their ID in ascending order.
• Next, show records that start with 40: (original IDs 7-9) before the ones that start with 91: (original IDs 4-6).
• Within each of these subgroups, sort by the numeric value tied to the 40 key (since that's the consistent value we can use to order consistently).

Full SQL Query #

SELECT
    id,
    -- Optional: Reorder key-value pairs in test_varc to match your desired format
    CASE
        WHEN test_varc REGEXP '80:' THEN test_varc
        ELSE
            -- Swap pairs so the smaller numeric value comes first
            CONCAT(
                IF(
                    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(test_varc, ',', 1), ':', -1) AS UNSIGNED) < CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(test_varc, ',', -1), ':', -1) AS UNSIGNED),
                    SUBSTRING_INDEX(test_varc, ',', 1),
                    SUBSTRING_INDEX(test_varc, ',', -1)
                ),
                ',',
                IF(
                    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(test_varc, ',', 1), ':', -1) AS UNSIGNED) < CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(test_varc, ',', -1), ':', -1) AS UNSIGNED),
                    SUBSTRING_INDEX(test_varc, ',', -1),
                    SUBSTRING_INDEX(test_varc, ',', 1)
                )
            )
    END AS test_varc
FROM test_table
ORDER BY
    -- Priority 1: Push records with 80: to the top
    (test_varc REGEXP '80:') DESC,
    -- Priority 2: Push records starting with 40: before those starting with 91:
    (test_varc REGEXP '^40:\\d+,') DESC,
    -- Priority 3: Sort by the numeric value of the 40: key
    CAST(
        IF(
            test_varc REGEXP '^40:',
            SUBSTRING_INDEX(test_varc, ':', -1),
            SUBSTRING_INDEX(SUBSTRING_INDEX(test_varc, ',', -1), ':', -1)
        ) AS UNSIGNED
    ) ASC;

Step-by-Step Explanation #

1. The Sorting Logic

• First Order Condition: (test_varc REGEXP '80:') DESC
  In MySQL, REGEXP returns 1 if there's a match, 0 otherwise. Using DESC ensures records with 80: (which return 1) come before those without (which return 0)—that's your first group sorted correctly.

• Second Order Condition: (test_varc REGEXP '^40:\\d+,') DESC
  The regex ^40:\\d+, matches strings that start with 40: followed by digits and a comma—exactly your original IDs 7-9. Again, DESC pushes these to the top of the non-80 group, before the 91:-starting records.

• Third Order Condition: CAST(...) AS UNSIGNED ASC
  We extract the numeric value after 40: (whether it's at the start or end of the string), convert it to an integer, and sort ascending. This keeps your subgroups ordered correctly (15→16→17 for IDs 7-9, 35→36→37 for IDs 4-6).

2. Optional: Fixing the test_varc Format

Your desired output reorders key-value pairs in some records (like aligning pairs by their numeric value). The CASE statement in the query does exactly that: it splits the two pairs, compares their numeric values, and concatenates them in ascending order of value to match your expected format.

Why Your Initial Attempt Was Close #

Your original query select id, test_varc from test_table order by test_varc REGEXP '(40:\d+,)' desc was on the right track—it separates records with 40:... from others—but it didn't account for the 80: records that need to be first. Adding the first order condition fixes that gap.

内容的提问来源于stack exchange，提问作者Kenan Şimşek Birusk Kuresofa