เรียงลำดับ MySQL ตามส่วนของคอลัมน์ (ตัวเลขท้ายหลัง Underscore)

จัดเรียงตามส่วนตัวเลขหลัง Delimiter (Underscore)

ข้อมูลแบบ 'John_120', 'John_201' ต้องการเรียงตามตัวเลขท้าย (120, 201) ไม่ใช่แบบ String ใช้ SUBSTRING_INDEX() แยกส่วน แล้ว CAST เป็นตัวเลข

สร้างตารางและข้อมูล

mysql> CREATE TABLE DemoTable1502 (StudentId VARCHAR(40));
Query OK, 0 rows affected (0.54 sec)

mysql> INSERT INTO DemoTable1502 VALUES
    -> ('John_120'), ('John_201'), ('Mike_178'), ('Bob_198');
Query OK, 4 rows affected

คำสั่ง: แยกส่วนซ้าย/ขวา แล้วเรียงตามส่วนขวา (ตัวเลข)

mysql> SELECT 
    ->   SUBSTRING_INDEX(StudentId, '_', 1) AS LeftPart,
    ->   SUBSTRING_INDEX(StudentId, '_', -1) AS RightPart
    -> FROM DemoTable1502
    -> ORDER BY RightPart;

ผลลัพธ์

+----------+-----------+
| LeftPart | RightPart |
+----------+-----------+
| John     |       120 |
| Mike     |       178 |
| Bob      |       198 |
| John     |       201 |
+----------+-----------+

คำอธิบาย SUBSTRING_INDEX

SUBSTRING_INDEX(str, delim, count)
-- count > 0: คืน substring ก่อน delimiter ครั้งที่ count (นับจากซ้าย)
-- count < 0: คืน substring หลัง delimiter ครั้งที่ |count| (นับจากขวา)

SUBSTRING_INDEX('John_120', '_', 1)   -- 'John'   (ก่อน _ ตัวแรก)
SUBSTRING_INDEX('John_120', '_', -1)  -- '120'    (หลัง _ ตัวสุดท้าย)

เวอร์ชันที่เรียงเป็นตัวเลขจริง (CAST)

SELECT StudentId
FROM DemoTable1502
ORDER BY CAST(SUBSTRING_INDEX(StudentId, '_', -1) AS UNSIGNED);

กรณี Delimiter หลายตัว (เช่น 'A_B_C_123')

-- ต้องการส่วนท้ายหลัง _ ตัวสุดท้าย
SUBSTRING_INDEX(col, '_', -1)  -- ได้ '123' ✓

-- ต้องการส่วนท้ายหลัง _ ตัวที่ 2 จากขวา
SUBSTRING_INDEX(SUBSTRING_INDEX(col, '_', -2), '_', 1)  -- ได้ 'C'

ประสิทธิภาพ: เพิ่ม Generated Column + Index (MySQL 5.7+)

ALTER TABLE DemoTable1502
ADD COLUMN RightPart INT AS (CAST(SUBSTRING_INDEX(StudentId, '_', -1) AS UNSIGNED)) STORED,
ADD INDEX idx_rightpart (RightPart);

-- แล้ว ORDER BY RightPart จะใช้ Index ได้เร็วขึ้น