จากที่เคยเขียน MySQL: แทรก id ที่ว่างอยู่ จะเขียน advance ขี้น โดยจะเอา string list มา insert รายการที่ id ถูกข้ามไป หรือลบไปในการเขียนบทความ SQL ทคนิคการจัดการฐานข้อมูลที่น่าสนใจอย่างหนึ่งคือการ “เติมเต็มช่องว่าง” ( Fill the Gaps ) ของ Primary Key ที่หายไป บทความนี้จะอธิบายการทำงานของ SQL Script ซึ่งใช้สำหรับเพิ่มข้อมูลลงในตารางของ WordPress ( wp_terms ) โดยฉลาดพอที่จะหา ID ที่ว่างอยู่มาใช้งานครับ Tag ( ป้ายกำกับ ) และ Category ( หมวดหมู่ ) ของ WordPress
💡 แนวคิดการ Insert ข้อมูลลงใน Gap ID
ปกติแล้วเวลาเรา INSERT ข้อมูลที่มีคุณสมบัติ AUTO_INCREMENT ตัวเลขจะรันต่อไปเรื่อยๆ แม้ว่าเราจะลบข้อมูลเก่าออกไปแล้วทำให้เกิดช่องว่าง ( Gap ) เช่น 1, 2, 5, 6 ( เลข 3 และ 4 หายไป )
Script นี้ถูกออกแบบมาเพื่อ นำเลข 3 และ 4 กลับมาใช้ใหม่ ก่อนที่จะไปใช้เลขใหม่ล่าสุดครับ
🛠 เจาะลึกการทำงานของ SQL
โครงสร้างของ Query นี้ใช้ CTE ( Common Table Expressions ) แบ่งการทำงานเป็น 4 ขั้นตอนหลัก
- เตรียมข้อมูล ( RawData )
ใช้JSON_TABLEเพื่อแปลงรายการคำที่ต้องการ ( Tags ) จากรูปแบบ JSON ให้กลายเป็นตารางข้อมูลเสมือน เพื่อความสะดวกในการเปรียบเทียบกับฐานข้อมูลเดิม - ตรวจสอบข้อมูลซ้ำ ( TagStatus )
ทำการLEFT JOINกับตารางจริงเพื่อเช็คว่า Tag ไหนมีอยู่แล้วบ้าง เพื่อป้องกันการเพิ่มข้อมูลซ้ำ ( Duplication ) - ค้นหาช่องว่าง ( AvailableIDs )
นี่คือหัวใจของบทความนี้ครับ Script จะมองหาเลข ID ที่ “แหว่ง” ไป โดยใช้ตรรกะ- หา
term_id + 1ที่ไม่มีอยู่ในตารางปัจจุบัน - จำกัดขอบเขตไม่ให้เกิน
MAX(term_id)เพื่อไม่ให้ไปทับกับเลขที่รันต่อตามปกติ
- หา
- จับคู่และ Insert ( Final Select )
นำ Tag ใหม่ที่ยังไม่มีในระบบ มาจับคู่กับgap_idที่หาได้แบบ 1 ต่อ 1 ผ่านแถวลำดับ (ROW_NUMBER) แล้วทำการINSERTเข้าสู่ตารางwp_termsพร้อมสร้างslugให้อัตโนมัติ
⚠️ ข้อควรระวังในการใช้งาน
การดึง ID ที่ว่างกลับมาใช้ใหม่มีข้อดีคือทำให้เลขเรียงสวยงาม แต่มีสิ่งที่ต้องพิจารณา ดังนี้
- Data Integrity: หากมีตารางอื่นอ้างอิง ( Foreign Key ) ถึง ID เก่าที่เคยลบไป การนำ ID นั้นมาใช้ใหม่อาจทำให้เกิดความสับสนของข้อมูลได้
- Performance: ในฐานข้อมูลที่มีขนาดใหญ่มาก การรัน Query เพื่อหา Gap ทุกครั้งอาจใช้ทรัพยากรสูงกว่าการปล่อยให้ Auto Increment ทำงานไปตามปกติ
- Collation: ในตัวอย่างมีการใช้
utf8mb4_unicode_520_ciเพื่อรองรับภาษาไทยและอักขระพิเศษ ต้องตรวจสอบให้แน่ใจว่า Collation นี้ตรงกับโครงสร้างตารางจริงของคุณ
add ID in wp_terms gap script
INSERT INTO wp_terms (term_id, name, slug, term_group)
WITH RawData AS (
-- เตรียมข้อมูลและจัดการ Collation ให้ตรงกับตารางใน DB
SELECT DISTINCT TRIM(j.tag) COLLATE utf8mb4_unicode_520_ci AS tag_name
FROM JSON_TABLE(
'["Fill","Pre-check","ช่องว่าง","ติมเต็ม","มีอยู่แล้ว","ยังไม่มี","advance","Array","Assigned","Auto","Automation","Category","Check","Cleanliness","COLLATE","Concurrency","Constraint","CTEs","Current","Database","Exists","Gaps","High","ID","Increment","Auto Increment","Insert","Integrity","JSON","Key","list","Locking","Mapping","MySQL","Name","ชื่อ","New","Performance","Primary","Query","ROW_NUMBER()","Script","SQL","Status","มีอยู่ใ","Step-by-Step","string","Tag","term_id","wp_terms","กระโดด","ตาราง","หา","ข้อมูล","ซ้ำ","ฐานข้อมูล","เรียงตัว","สวยงาม","เรียงลำดับ","ตาม","ตัวอักษร","หมายเลข","ลำดับ"]',
'$[*]' COLUMNS(tag VARCHAR(50) PATH '$')
) AS j
),
TagStatus AS (
-- เช็คว่า Tag ไหนมีอยู่แล้ว
SELECT rd.tag_name, t.term_id AS existing_id,
CASE WHEN t.term_id IS NULL THEN 'New' ELSE 'Exists' END AS status
FROM RawData rd
LEFT JOIN wp_terms AS t ON rd.tag_name = TRIM(t.name) COLLATE utf8mb4_unicode_520_ci
),
AvailableIDs AS (
-- หา ID ที่แหว่ง (Gaps) เพื่อนำมาใช้ใหม่
SELECT (t1.term_id + 1) AS gap_id,
ROW_NUMBER() OVER (ORDER BY t1.term_id ASC) as rn
FROM wp_terms AS t1
WHERE NOT EXISTS (SELECT 1 FROM wp_terms AS t2 WHERE t2.term_id = t1.term_id + 1)
AND t1.term_id < (SELECT MAX(term_id) FROM wp_terms)
),
NewTagsNumbered AS (
-- เรียงลำดับ Tag ใหม่ที่จะเพิ่ม
SELECT tag_name, ROW_NUMBER() OVER (ORDER BY tag_name ASC) as rn
FROM TagStatus WHERE status = 'New'
)
-- ดึงข้อมูลมา Insert พร้อมสร้าง Slug
SELECT
a.gap_id,
n.tag_name,
LOWER(REPLACE(REPLACE(REPLACE(n.tag_name, ' ', '-'), '.', '-'), '_', '-')),
0
FROM NewTagsNumbered n
INNER JOIN AvailableIDs a ON n.rn = a.rn;
สรุป
Script นี้เป็นตัวอย่างที่ดีของการใช้ Advanced SQL ในการจัดการพื้นที่ของ Primary Key อย่างคุ้มค่า เหมาะสำหรับงานที่ต้องการจัดระเบียบข้อมูลให้เรียบร้อยและไม่มีช่องว่างในลำดับตัวเลขครับ
อ่านเพิ่มเติม