การเรียงตัวเลขใน VARCHAR ให้ถูกต้องตามค่าตัวเลขจริง
เมื่อตัวเลขเก็บใน VARCHAR และมีเลข 0 นำหน้า (Leading zeros) เช่น '085', '090' การเรียงแบบสตริง (Alphabetical) จะผิด (090 จะอยู่ก่อน 85) วิธีแก้คือคูณด้วย 1 เพื่อแปลงเป็นตัวเลขใน ORDER BY
สร้างตารางและเพิ่มข้อมูล
mysql> CREATE TABLE DemoTable (Value VARCHAR(100));
Query OK, 0 rows affected (0.62 sec)
mysql> INSERT INTO DemoTable VALUES('90');
mysql> INSERT INTO DemoTable VALUES('86');
mysql> INSERT INTO DemoTable VALUES('45');
mysql> INSERT INTO DemoTable VALUES('85');
mysql> INSERT INTO DemoTable VALUES('085');
mysql> INSERT INTO DemoTable VALUES('090');
mysql> INSERT INTO DemoTable VALUES('045');แสดงข้อมูลทั้งหมด (ลำดับ Insert)
mysql> SELECT * FROM DemoTable;ผลลัพธ์:
+-------+
| Value |
+-------+
| 90 |
| 86 |
| 45 |
| 85 |
| 085 |
| 090 |
| 045 |
+-------+
7 rows in set (0.00 sec)คำสั่งเรียงตามค่าตัวเลขจริง (คูณ 1) แล้วตามสตริงต้นฉบับ
mysql> SELECT * FROM DemoTable ORDER BY Value * 1, Value;ผลลัพธ์:
+-------+
| Value |
+-------+
| 045 |
| 45 |
| 085 |
| 85 |
| 86 |
| 090 |
| 90 |
+-------+
7 rows in set (0.00 sec)คำอธิบาย
Value * 1: บังคับให้ MySQL แปลง VARCHAR เป็น Number เพื่อเรียง (Numeric Sort)Value: ใช้เป็น Tie-breaker เมื่อค่าตัวเลขเท่ากัน (045 และ 45 เท่ากัน 45) เรียงตามสตริงต้นฉบับ → '045' ก่อน '45'- ผลลัพธ์: 45, 85, 86, 90 พร้อม Leading zero version อยู่ก่อนค่าปกติ
- วิธีนี้ใช้ได้กับคอลัมน์ VARCHAR ที่เก็บตัวเลขเท่านั้น
