ปัญหาการจัดเรียง VARCHAR ที่ผสมตัวเลขและตัวอักษร
คอลัมน์ VARCHAR จัดเรียงแบบ **Lexicographical (ตัวอักษร)** ทำให้ '100' < '190' < '20' < 'John' ซึ่งไม่ใช่การจัดเรียงตัวเลขที่ถูกต้อง
วิธีแก้: ใช้ ORDER BY IF(CAST(...))
แปลงค่าเป็น SIGNED INTEGER หากแปลงได้ (ไม่ใช่ 0) ใช้ค่านั้น จัดเรียงก่อน หากแปลงไม่ได้ (เป็นข้อความ) ให้ค่ามากมาก (เช่น 100000000000) จัดทีหลัง
Syntax
ORDER BY IF(CAST(column AS SIGNED) = 0, 100000000000, CAST(column AS SIGNED))
ตัวอย่างการใช้งาน
สร้างตารางและเพิ่มข้อมูลผสม:
mysql> CREATE TABLE DemoTable (EmployeeCode VARCHAR(100));
Query OK, 0 rows affected (1.17 sec)
mysql> INSERT INTO DemoTable VALUES ('190'), ('100'), ('John'), ('120');
Query OK, 4 rows affected
ข้อมูลต้นฉบับ (เรียงตามตัวอักษร):
mysql> SELECT * FROM DemoTable;
+--------------+ | EmployeeCode | +--------------+ | 190 | | 100 | | John | | 120 | +--------------+ 4 rows in set (0.00 sec)
จัดเรียงให้ตัวเลขอยู่ก่อน (น้อยไปมาก) ตามด้วยตัวอักษร:
mysql> SELECT * FROM DemoTable ORDER BY IF(CAST(EmployeeCode AS SIGNED) = 0, 100000000000, CAST(EmployeeCode AS SIGNED));
+--------------+ | EmployeeCode | +--------------+ | 100 | | 120 | | 190 | | John | +--------------+ 4 rows in set, 1 warning (0.00 sec)
วิธีอื่นที่ทันสมัยกว่า (MySQL 8.0+)
ใช้ REGEXP แยกตัวเลขและข้อความ:
ORDER BY CASE WHEN EmployeeCode REGEXP '^[0-9]+$' THEN 0 ELSE 1 END, -- ตัวเลขก่อน CAST(EmployeeCode AS UNSIGNED), -- เรียงตัวเลข EmployeeCode -- เรียงข้อความ
ข้อควรระวัง
CAST('abc' AS SIGNED)คืนค่า 0 พร้อม Warning- ตัวเลขนำหน้าศูนย์ (Leading zeros) เช่น '001', '010' จะถูกแปลงเป็น 1, 10
- ค่าลบ เช่น '-100' ทำงานได้ปกติ
- สำหรับข้อมูลใหญ่ ควรสร้าง Generated Column หรือ Index แยก
