How Can I Sort MySQL Version Numbers Correctly?

MySQL Version Number Sorting

Problem:

MySQL sorting routines may not provide the desired sort order for version numbers stored as varbinary(300). Sorting needs to account for up to 20 digits and produce results like:

1.1.2
1.2.3.4
2.2
3.2.1.4.2
3.2.14
4
9.1

Solution:

Utilize the INET_ATON function with the following query:

SELECT version_number FROM table ORDER BY INET_ATON(SUBSTRING_INDEX(CONCAT(version_number,'.0.0.0'),'.',4))

This technique leverages INET_ATON's ability to convert IP addresses to integers. By appending .0.0.0 to the version number and extracting only the first four components using SUBSTRING_INDEX, we effectively translate the version numbers into comparable integer representations.

Additional Considerations:

• This approach does not leverage indexing, as it operates on a function of the column rather than the column itself. Sorting may be relatively slow in some cases.
• For larger version numbers, consider using separate columns for each part of the version number.

The above is the detailed content of How Can I Sort MySQL Version Numbers Correctly?. For more information, please follow other related articles on the PHP Chinese website!