ป้ายกำกับ: Database Optimization

MySQL: แทรก id ที่ว่างอยู่MySQL: แทรก id ที่ว่างอยู่

2020-01-022020-01-02| phunsanitphunsanit| 0 Comment | 10:00

การใช้ CTE ร่วมกับฐานข้อมูล MySQL เพื่อหา “ช่องว่าง” ของ ID และทำการ INSERT ข้อมูลเข้าไปในตำแหน่งที่ว่างเหล่านั้น สามารถทำได้โดยสร้างลำดับตัวเลข ขึ้นมาเปรียบเทียบครับ นี่คือ SQL Script สำหรับการค้นหา ID ที่ว่างในช่วง 1 – 11713 และนำข้อมูลที่คุณต้องการใส่เข้าไปในช่องว่างเหล่านั้น

เทคนิคการใช้ Recursive CTE เพื่อเติม ID ที่ว่างใน WordPress

บ่อยครั้งที่ตาราง wp_posts ของเรามีเลข ID ที่ไม่ต่อเนื่องกัน ซึ่งอาจเกิดจากการลบโพสต์เก่า หรือการสำรองข้อมูลที่ไม่สมบูรณ์ หากเราต้องการจัดการให้ ID ตั้งแต่ 1 ถึง 11713 มีข้อมูลครบทุกแถว เราสามารถใช้ SQL ชุดนี้จัดการได้ครับ

การขยายขีดจำกัดการทำงาน

SET SESSION max_recursive_iterations = 12000;

โดยปกติ MariaDB จะจำกัดการวนลูป ไว้เพียง 1,000 รอบ เพื่อป้องกันปัญหาวนลูปไม่สิ้นสุด แต่ในโจทย์นี้เราต้องการสร้างตัวเลขสูงถึง 11,713 เราจึงต้องขยายเพดานบินให้สูงขึ้นเป็น 12,000 เพื่อให้ Query ทำงานจนจบกระบวนการ

โครงสร้างคำสั่งและการทำงาน

คำสั่งนี้แบ่งการทำงานออกเป็น 3 ส่วนหลัก ดังนี้ครับ

การสร้างลำดับตัวเลข

WITH RECURSIVE id_sequence AS

ส่วนนี้จะสร้าง “ตารางเสมือน” ชื่อว่า id_sequence ที่มี Column n บรรจุเลข 1, 2, 3… ไปจนถึง 11,713 เตรียมไว้ในหน่วยความจำ

การเปรียบเทียบหา “ช่องว่าง”

FROM id_sequence s
LEFT JOIN `wp_posts` t ON s.n = t.ID
WHERE t.ID IS NULL;

เรานำตารางตัวเลขที่สร้างขึ้นไปวางทาบกับตาราง wp_posts จริง ๆ ด้วย LEFT JOIN และใช้เงื่อนไข WHERE t.ID IS NULL เพื่อเลือกเฉพาะ เลข ID ที่ยังไม่มีอยู่ในระบบ เท่านั้น

การแทรกข้อมูล

เมื่อได้เลข ID ที่ว่างแล้ว ระบบจะทำการ INSERT ข้อมูลที่กำหนดลงไป โดยมีไฮไลท์สำคัญคือ

  • ID: ใช้ค่า s.n มาเป็น Primary Key ใหม่
  • post_title: ใช้ฟังก์ชัน CONCAT เพื่อสร้างชื่อโพสต์ตาม ID เช่น https://pitt.plusmagi.com/?p=3
  • post_name: ใส่เลข ID ลงไปเพื่อให้ Slug ของโพสต์ไม่ซ้ำกัน
  • post_status: ตั้งค่าเป็น draft เพื่อความปลอดภัย ไม่ให้โพสต์เหล่านี้ไปโผล่บนหน้าเว็บทันที

add ID in post gap script

-- 1. สำหรับ MariaDB ตั้งค่าจำนวนรอบการวนลูป
SET SESSION max_recursive_iterations = 20000; -- 2. รันคำสั่ง INSERT ที่แก้ไขชื่อ column แล้ว
INSERT INTO `wp_posts` WITH RECURSIVE id_sequence AS SELECT s.n, '1', '2026-04-02 23:09:31', '2026-04-02 16:09:31', '[]', CONCAT ('https://pitt.plusmagi.com/?p=', s.n) , '', 'Scheduled', 'closed', 'closed', '', s.n, '', '', '2027-08-05 07:00:00', '2027-08-05 07:00:00', '', '0', '' , '0', 'post', '', '0'
FROM id_sequence AS s
LEFT JOIN `wp_posts` t ON s.n = t.ID
WHERE t.ID IS NULL;

ข้อควรระวังก่อนใช้งาน

  1. Backup ข้อมูล
    ทุกครั้งที่มีการยุ่งกับ Primary Key ควรทำ Snapshot หรือ Export ตารางไว้ก่อนเสมอ
  2. ประสิทธิภาพ
    การรัน Loop หมื่นกว่ารอบอาจใช้เวลาเพียงเล็กน้อย แต่ถ้าตารางจริงมีข้อมูลมหาศาล ควรตรวจสอบดัชนี ของ Column ID ให้เรียบร้อย
  3. สถานะโพสต์
    ใน Query นี้กำหนดเป็น Scheduled หรือ draft หากคุณต้องการให้เป็นโพสต์จริง สามารถเปลี่ยนเป็น publish ได้ในภายหลัง

การแก้ไขบทความ

  • สามารถ edit รายการที่ต้องการโดยการระบุ ID โดยตรง เช่น
    /wp-admin/post.php?post={ ID }&action=edit
    ถ้าต้องการแก้ ID 3 ก็ใช้
    /wp-admin/post.php?post=3&action=edit
  • หรือจะหาจาก ร่างเอาไว้ ก็ได้
    /wp-admin/edit.php?post_status=draft&post_type=post

สรุป

วิธีนี้เป็นวิธีที่ Clean ที่สุดในการเติมช่องว่างของข้อมูล เพราะไม่ต้องพึ่งพา Script ภายนอก และทำงานได้แม่นยำด้วยพลังของ SQL สมัยใหม่ครับ

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

Read MoreRead More