MySQL ORDER BY Keyword Match: เรียงตามคำค้นหาใน Text

จัดลำดับผลลัพธ์ตาม Keyword Matching

เมื่อต้องการให้แถวที่มี Keyword ตรงกับคำค้นหาอยู่บนสุด ใช้ CASE หรือ INSTR() / LOCATE() ใน ORDER BY

ตัวอย่าง: เรียง Game ตามคำว่า "Candy" และ "cash"

CREATE TABLE DemoTable602 (GameName TEXT);

INSERT INTO DemoTable602 VALUES
('Candy cash game'),
('Pubg'),
('cash Candy game'),
('subway');

Query: ใส่ "Candy" ก่อน แล้วตามด้วย "cash" (มากไปน้อย)

SELECT * FROM DemoTable602
ORDER BY 
    CASE WHEN INSTR(GameName, 'Candy') = 0 THEN 1 ELSE 0 END,
    INSTR(GameName, 'cash') DESC;

ผลลัพธ์:

+-----------------+
| GameName        |
+-----------------+
| Candy cash game |  -- มี Candy และ cash
| cash Candy game |  -- มี Candy และ cash
| Pubg            |  -- ไม่มี Candy, ไม่มี cash
| subway          |  -- ไม่มี Candy, ไม่มี cash
+-----------------+

อธิบาย Logic

1. CASE WHEN INSTR(GameName, 'Candy') = 0 THEN 1 ELSE 0 END - ไม่มี Candy = 1 (ท้ายสุด), มี Candy = 0 (บนสุด)
2. INSTR(GameName, 'cash') DESC - ในกลุ่มที่มี Candy เรียงตามตำแหน่ง "cash" มากไปน้อย (เจอเร็ว = ตำแหน่งเล็ก = บนสุด)

วิธีทางเลือก: ใช้ BOOLEAN MODE Fulltext Search (แนะนำสำหรับ Production)

-- ต้องมี FULLTEXT INDEX ก่อน
ALTER TABLE DemoTable602 ADD FULLTEXT(GameName);

-- Query แบบ Natural Language
SELECT *, MATCH(GameName) AGAINST('Candy cash' IN NATURAL LANGUAGE MODE) AS relevance
FROM DemoTable602
ORDER BY relevance DESC;

-- Query แบบ Boolean Mode (ควบคุมได้ละเอียด)
SELECT * FROM DemoTable602
WHERE MATCH(GameName) AGAINST('+Candy +cash' IN BOOLEAN MODE)
ORDER BY MATCH(GameName) AGAINST('+Candy +cash' IN BOOLEAN MODE) DESC;

เปรียบเทียบวิธีต่างๆ

ข้อแนะนำ

• ตาราง < 10,000 rows: CASE + INSTR ใช้ได้ ไม่ซับซ้อน
• ตาราง > 10,000 rows หรือ Fulltext Search จริง: ใช้ FULLTEXT INDEX
• ต้องการ Relevance Score จริง: ใช้ MATCH...AGAINST