จัดเรียง VARCHAR ที่เป็นตัวเลข ให้ถูกต้องตามค่าตัวเลข (ASC/DESC) ใน MySQL

ปัญหาการจัดเรียง VARCHAR ตัวเลข: '10' < '100' < '2' (Lexicographical)

คอลัมน์ VARCHAR เก็บตัวเลข จะจัดเรียงแบบ String ทำให้ '10' < '100' < '110' < '2000' < '9' (ผิด)

วิธีแก้: CAST เป็น DECIMAL/UNSIGNED ก่อน ORDER BY

ORDER BY CAST(column_name AS DECIMAL(20,2))  -- หรือ UNSIGNED, SIGNED

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

สร้างตารางและเพิ่มตัวเลขแบบ String:

mysql> CREATE TABLE DemoTable726 (Value VARCHAR(100));
Query OK, 0 rows affected (0.60 sec)

mysql> INSERT INTO DemoTable726 VALUES ('100'), ('10'), ('110'), ('2000'), ('1000');
Query OK, 5 rows affected

ข้อมูลต้นฉบับ (เรียง String):

mysql> SELECT * FROM DemoTable726;

+-------+
| Value |
+-------+
| 100   |
| 10    |
| 110   |
| 2000  |
| 1000  |
+-------+
5 rows in set (0.00 sec)

เรียงน้อยไปมาก (ASC) - แปลงเป็น DECIMAL ก่อน:

mysql> SELECT * FROM DemoTable726 ORDER BY CAST(Value AS DECIMAL(20,2));

+-------+
| Value |
+-------+
| 10    |
| 100   |
| 110   |
| 1000  |
| 2000  |
+-------+
5 rows in set (0.00 sec)

เรียงมากไปน้อย (DESC):

mysql> SELECT * FROM DemoTable726 ORDER BY CAST(Value AS DECIMAL(20,2)) DESC;

+-------+
| Value |
+-------+
| 2000  |
| 1000  |
| 110   |
| 100   |
| 10    |
+-------+
5 rows in set (0.00 sec)

ชนิดข้อมูลสำหรับ CAST

แก้ไขรากเหว: เปลี่ยนชนิดคอลัมน์เป็น NUMBER

ALTER TABLE DemoTable726 MODIFY Value INT;  -- หรือ DECIMAL(10,2)

หลังเปลี่ยนชนิดแล้ว ORDER BY Value จะทำงานถูกต้องอัตโนมัติ และใช้ Index ได้

ประสิทธิภาพ

• CAST ใน ORDER BY ทำให้ **ไม่ใช้ Index** ได้ (Filesort)
• ตารางใหญ่ → เปลี่ยน Data Type หรือสร้าง Generated Column + Index
• MySQL 8.0: ALTER TABLE t ADD COLUMN val_num INT GENERATED ALWAYS AS (CAST(val AS UNSIGNED)) STORED, ADD INDEX (val_num);