หมวดหมู่: WordPress

จากที่เคยเขียน 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 ขั้นตอนหลัก

1. เตรียมข้อมูล (RawData)
   ใช้ JSON_TABLE เพื่อแปลงรายการคำที่ต้องการ (Tags) จากรูปแบบ JSON ให้กลายเป็นตารางข้อมูลเสมือน เพื่อความสะดวกในการเปรียบเทียบกับฐานข้อมูลเดิม
2. ตรวจสอบข้อมูลซ้ำ (TagStatus)
   ทำการ LEFT JOIN กับตารางจริงเพื่อเช็คว่า Tag ไหนมีอยู่แล้วบ้าง เพื่อป้องกันการเพิ่มข้อมูลซ้ำ (Duplication)
3. ค้นหาช่องว่าง (AvailableIDs)
   นี่คือหัวใจของบทความนี้ครับ Script จะมองหาเลข ID ที่ “แหว่ง” ไป โดยใช้ตรรกะ
   1. หา term_id + 1 ที่ไม่มีอยู่ในตารางปัจจุบัน
   2. จำกัดขอบเขตไม่ให้เกิน MAX (term_id) เพื่อไม่ให้ไปทับกับเลขที่รันต่อตามปกติ
4. จับคู่และ 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 อย่างคุ้มค่า เหมาะสำหรับงานที่ต้องการจัดระเบียบข้อมูลให้เรียบร้อยและไม่มีช่องว่างในลำดับตัวเลขครับ

อ่านเพิ่มเติม

• WordPress: หมวดหมู่, ป้ายกำกับ
• MySQL: แทรก id ที่ว่างอยู่
• Database: List String