MySQL ORDER BY สตริงที่มีตัวเลข (Natural Sort)

ใช้ SUBSTR() แยกส่วนตัวอักษร และ CAST(... AS UNSIGNED) แปลงส่วนตัวเลข เพื่อเรียงแบบ Natural Sort

ไวยากรณ์

SELECT * FROM yourTable 
ORDER BY SUBSTR(yourColumn FROM 1 FOR 1), CAST(SUBSTR(yourColumn FROM 2) AS UNSIGNED);

ตัวอย่างการใช้งาน

สร้างตาราง OrderByStringWithNumbers:

mysql> CREATE TABLE OrderByStringWithNumbers (
    -> Id INT NOT NULL AUTO_INCREMENT,
    -> Words VARCHAR(10),
    -> PRIMARY KEY(Id)
    -> );

เพิ่มข้อมูล (รูปแบบ A + ตัวเลข):

mysql> INSERT INTO OrderByStringWithNumbers(Words) VALUES('A10'),('A30'),('A12'),('A11'),('A28'),('A21');

ข้อมูลเดิม (เรียงแบบ String: A10, A11, A12, A21, A28, A30)

+----+-------+
| Id | Words |
+----+-------+
| 1  | A10   |
| 2  | A30   |
| 3  | A12   |
| 4  | A11   |
| 5  | A28   |
| 6  | A21   |
+----+-------+

คำสั่ง Natural Sort (ตัวอักษรตัวแรก แล้วตัวเลข)

mysql> SELECT * FROM OrderByStringWithNumbers 
    -> ORDER BY SUBSTR(Words FROM 1 FOR 1), CAST(SUBSTR(Words FROM 2) AS UNSIGNED);

ผลลัพธ์ (เรียงตัวเลขถูกต้อง: A10, A11, A12, A21, A28, A30)

+----+-------+
| Id | Words |
+----+-------+
| 1  | A10   |
| 4  | A11   |
| 3  | A12   |
| 6  | A21   |
| 5  | A28   |
| 2  | A30   |
+----+-------+

คำอธิบาย: SUBSTR(Words FROM 1 FOR 1) เอาตัวอักษรตัวแรก (A), SUBSTR(Words FROM 2) เอาส่วนที่เหลือ (10,30,12...) แล้ว CAST เป็น UNSIGNED เรียงเป็นตัวเลข

MySQL 8.0+: ใช้ ORDER BY Words COLLATE utf8mb4_general_ci หรือ ORDER BY CAST(Words AS UNSIGNED) ได้ง่ายกว่า (ถ้าตัวอักษรนำหน้าเหมือนกันหมด)