问对同时包含字母和数字字符的产品代码进行MySQL排序

EN

SELECT
    *
    ,SUBSTRING(
        REVERSE(CAST(REVERSE(CONCAT(`product_name`,'8')) AS UNSIGNED)),1,
        CHARACTER_LENGTH(
            REVERSE(CAST(REVERSE(CONCAT(`product_name`,'8')) AS UNSIGNED))
        )-1
    ) AS 'numericVal'
FROM `some_table`
ORDER BY
    SUBSTRING(`product_name`,1,CHAR_LENGTH(`product_name`)-CHAR_LENGTH(`numericVal`)),
    CAST(`numericVal` AS UNSIGNED INTEGER)

# Example record "Post Lantern PL10"...
SELECT
    *
    ,SUBSTRING(                                         # 5a) substring of this is calculated
        REVERSE(                                        # 4) gets re-reversed into "108"
            CAST(                                       # 3) gets casted into an integer so "801" part is parsed
                REVERSE(                                # 2) gets reversed: "801LP nretnaL tsoP"
                    CONCAT(`product_name`,'8')          # 1) is concatenated with an 8: "Post Lantern PL108"
                )
            AS UNSIGNED)
        ),
        1,                                              # 5b) from the first character (index is 1 for this in SQL)
        CHARACTER_LENGTH(                               # 5c) and the length is recalculated (steps 1-4 repeated)
            REVERSE(
                CAST(
                    REVERSE(
                        CONCAT(`product_name`,'8')
                    )
                AS UNSIGNED)
            )
        )-1                                             # 5d1) minus 1 because at the beginning we appended an 8 and we
                                                        # 5d2) want to get rid of it now, so we're dropping the last digit
    ) AS 'numericVal'
FROM `some_table`
ORDER BY                                                        # 6) order by
    SUBSTRING(`product_name`,                                   # 7a) first, substring `product_name`
        1,                                                      # 7b) from the first character
        CHAR_LENGTH(`product_name`)-CHAR_LENGTH(`numericVal`)   # 7c) with the total length - length of numeric part
    ),
    CAST(`numericVal` AS UNSIGNED INTEGER)                      # 8a) then, by the numeric part, which gets casted into
                                                                # 8b) an integer for accurate numeric ordering