How to Sort Complex Version Numbers in MySQL?

MySQL Sorting of Version Numbers

Sorting version numbers in MySQL can be challenging, especially when dealing with complex formats like 1.1.2, 9.1, and 2.2. Using the standard order by version_number syntax often yields incorrect results.

To address this issue, a clever workaround involving the INET_ATON function can be employed:

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

This trick relies on INET_ATON to convert the version number into an IP address format, making it possible to sort the values numerically. Specifically, it adds '0.0.0' to ensure that each row has at least four parts, and then uses SUBSTRING_INDEX to extract only the first four parts for sorting.

While this workaround provides the desired sorting order, it's important to note that it involves a function call on the column, which can potentially slow down the sorting process compared to using an index.

For more complex version number formats, an alternative approach recommended by experts is to create separate columns for each part of the version number and sort on those columns instead. This ensures that the sorting can leverage indexes and achieve better performance.

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