In this tutorial, you will learn about various natural sorting techniques in MySQL by using the ORDER BY clause.
Let’s start the tutorial with sample data.
Suppose we have a table named items that contains two columns: id and item_no. To create items table we use the CREATE TABLE statement as follows:
CREATE TABLE if not exists items( id INT AUTO_INCREMENT PRIMARY KEY, item_no VARCHAR(255) NOT NULL );
We use the INSERT statement to insert some data into the items table:
INSERT INTO items(item_no) VALUES ('1'), ('1C'), ('10Z'), ('2A'), ('2'), ('3C'), ('20D');When we select data and sort it by item_no, we get the following result:
SELECT item_no FROM items ORDER BY item_no;
This is not what we expected. We expect to see the result like the following:
This is called natural sorting. Unfortunately, MySQL does not provide any built-in natural sorting syntax or function. The ORDER BY clause sorts strings in a linear fashion i.e., one character a time, starting from the first character.
To overcome this, first we split the item_no column into 2 columns: prefix and suffix. The prefix column stores the number part of the item_no and suffix column stores the alphabetical part. Then, we can sort the data based on these columns as the following query:
SELECT CONCAT(prefix,suffix) FROM items ORDER BY prefix, suffix
The query sorts data numerically first and sort the data alphabetically then. We get the expected result.
The disadvantage of this solution is that we have to break the item_no into two parts before you insert or update it. In addition, we have to combine two columns into one when we select the data.
If the item_no data is in fairly standard format, you can use the following query to perform natural sorting without changing the table structure.
SELECT item_no FROM items ORDER BY CAST(item_no AS UNSIGNED), item_no;
In this query, first we convert item_no data into unsigned integer by using the CAST function. Second, we use the ORDER BY clause to sort the rows numerically first and alphabetically then.
Let’s take a look at other common set of data that we often have to deal with.
TRUNCATE TABLE items; INSERT INTO items(item_no) VALUES('A-1'), ('A-2'), ('A-3'), ('A-4'), ('A-5'), ('A-10'), ('A-11'), ('A-20'), ('A-30');The expected result after sorting is as follows:
To achieve this result, we can use the LENGTH function. Notice that LENGTH function returns the length of a string. The idea is to sort the item_no data by length first and then by column value as the following query:
SELECT item_no FROM items ORDER BY LENGTH(item_no), item_no;
As you see the data is sorted naturally.
In case all above solutions didn’t work in your situation. You need to perform natural sorting in the application layer. Some languages support natural sorting function e.g., PHP provides natsort() function that sorts an array using natural sorting algorithm.
In this tutorial, we have shown you several techniques to perform natural sorting in MySQL.
随时随地看视频
