Simulating Natural Sorting in MySQL with PHP

Question

I have a MySQL table which contains product SKUs among other things. PHP powers importing data into this database, managing data through a control panel, displaying the information on the front-end, etc. (For clarity, this is a Magento site.) On the front-end, I need to be able to list SKUs in natural sorting order, however MySQL does not have a natsort method the way PHP does.

There are a number of MySQL only solutions, each with their own problems. The simplest that you see is to cast the string with something like ORDER BY sku + 0 but this only works for the simplest strings. Let's look at more complex options:

MySQL: ORDER BY LENGTH(col), col

The idea here is that test5 is shorter than test10 and will be sorted first. But test10 is alphabetically before test20 so that also is sorted first, giving you test5, test10, test20 which looks correct.

However, this doesn't match PHP's natsort method. For example, test50 will come before test100 but test50a will come after test100 which is incorrect. This is because test50a is longer than test50 and the 5 comes after the 1 when comparing test50a and test100. This should read test50, test50a, test100

PHP: natsort

I have the opportunity for the PHP application (Magento) to create an Index and either totally rebuild it on command, or update it on a SKU-by-SKU basis. At first, my solution was something like this:

// $skus = array() of strings

// Natural sort the SKUs
natsort($skus);

// Reset the keys to be in sequential order by array position
$skus = array_values($skus);

foreach ($skus as $position => $sku) {
     // Assume this is correctly escaped and sent to MySQL
     $sql = "UPDATE database.products SET position = {$position} WHERE sku = '{$sku}'";
}

You could then sort by position.

The problem with this is that any time you change a SKU or add a new SKU you would have to re-sort the entire array again, and update the entire index. (Deleting a SKU wouldn't have an effect.)

PHP: preg_replace_callback + str_pad

I landed on this method:

// $skus = array() of strings

foreach ($skus as $sku) {
     $paddedSku = preg_replace_callback('#\d+#', function($m) {
          return str_pad($m[0], 8, '0', STR_PAD_LEFT);
     }, $sku);

     // Assume this is correctly escaped and sent to MySQL
     $sql = "UPDATE database.products SET padded_sku = '{$paddedSku}' WHERE sku = '{$sku}'";
}

In this case, test50 becomes test00000050 and test50a becomes test00000050a and test100 becomes test00000100. You can then do ORDER BY padded_sku and it will alphanumerically sort correctly. This even works for more complex strings like test50a-abc45

In actuality, I've collected all of these padded SKUs into an array, and then inserted them using a REPLACE (which my indexes in MySQL make possible) in chunks of 500. The padded_sku column can be indexed for speed, and I can insert or change a single SKU at any time without having to rebuild the whole index.

Question

Is there a better way to naturally sort in MySQL that supports test50 coming before test50a coming before test100? I think that with some complex MySQL I could have it doing this preg_replace and str_pad through MySQL functions on the fly, but isn't it faster to calculate this once and store it as a simple string? (Even if it has to go back and forth to PHP to do that?)

Answer 1

Essentially, you want to have a custom, non-trivial sorting order, based on logic that doesn't exist in the table schema and not supported by standard SQL operators.

The general (and perhaps obvious) solution to this problem seems to be including the necessary data in the table schema explicitly. In other words, adding a dedicated column to represent the sorting key seems a sensible solution.

Suppose it's possible to use some SQL operations to generate this sorting key on the fly. The problem with that is that you would have to re-type the (potentially complicated) operation every time you want to select an ordered subset from the table. An option to make that more workable is embedding the sorting key logic inside a view on the source table. If this approach is possible, then the right solution will depend on the impact on performance due to the recalculation of the sorting key column in the view every time you access it.