ในบทช่วยสอนนี้ เราจะเรียนรู้วิธีลบแถวที่ซ้ำกันในฐานข้อมูล MySQL เพื่อลดขนาดฐานข้อมูลและช่วยเพิ่มประสิทธิภาพของเซิร์ฟเวอร์
ก่อนที่เราจะดำเนินการต่อ สมมติว่า:
- คุณมี MySQL ติดตั้งและทำงานบนระบบของคุณ
- คุณมีการเข้าถึงรูทไปยังฐานข้อมูล
- คุณสามารถเข้าถึงฐานข้อมูลสำหรับการทดลองหรือการทดสอบ
บันทึก : หากคุณต้องการฐานข้อมูลตัวอย่างเพื่อลองใช้แนวคิดที่ให้ไว้ในคู่มือนี้ โปรดพิจารณาฐานข้อมูล Sakila หรือดาวน์โหลดสำเนาของฐานข้อมูลที่ใช้ในคู่มือนี้
ทรัพยากรมีให้ด้านล่าง:
การใช้งานพื้นฐาน
ก่อนที่เราจะเริ่มต้น เราจะจงใจสร้างตารางที่มีค่าที่ซ้ำกันเพื่อการทดสอบ แบบสอบถาม SQL เพื่อดำเนินการนี้อยู่ด้านล่าง:
ใช้ โลก;หยด ตาราง ถ้า มีอยู่ ผู้ใช้;
สร้าง ตาราง ผู้ใช้(NS INT คีย์หลัก ไม่ โมฆะ AUTO_INCREMENT ,ชื่อผู้ใช้ VARCHAR (10) ไม่ โมฆะ ,ชื่อเต็ม VARCHAR (ยี่สิบ),อีเมล VARCHAR (255) ไม่ โมฆะ );
แทรก เข้าไปข้างใน ผู้ใช้(ชื่อผู้ใช้,ชื่อเต็ม,อีเมล) ค่า
('ราศีกันย์', 'คลอดด์ เอ็ม โมริ', ' [ป้องกันอีเมล] '),
('กด', 'ทิฟฟานี่ จี. เบลีย์', ' [ป้องกันอีเมล] '),
('จรวด', 'คริสโตเฟอร์ เอส. เพย์ตัน', ' [ป้องกันอีเมล] '),
('สสารมืด', 'แพทริเซีย เจ. ฟอกซ์', ' [ป้องกันอีเมล] '),
('เรื่อง', 'เฟย์ เอช. ฮาร์ทลี่ย์', ' [ป้องกันอีเมล] '),
('สสารมืด', 'แพทริเซีย เจ. ฟอกซ์', ' [ป้องกันอีเมล] '),
('จรวด', 'คริสโตเฟอร์ เอส. เพย์ตัน', ' [ป้องกันอีเมล] '),
('อาร์ทิมิส', 'เวสลีย์ ซี. ดิลลาร์ด', ' [ป้องกันอีเมล] ');
อย่าลังเลที่จะแก้ไขแบบสอบถามด้านบนเพื่อให้เหมาะกับความต้องการของคุณ คุณควรตรวจสอบให้แน่ใจว่าคุณได้สร้างฐานข้อมูล (โลก) เพื่อหลีกเลี่ยงข้อผิดพลาด
ตอนนี้ หากเราได้รับข้อมูลทั้งหมดภายในตารางและเรียงตามชื่อผู้ใช้ เราจะเห็นข้อมูลที่ซ้ำกันดังที่แสดง:
mysql> ใช้ โลก;ฐานข้อมูล เปลี่ยน
mysql> เลือก * จาก ผู้ใช้ สั่งโดย ชื่อผู้ใช้;
+ ---- + ------------ + ----------------------- + ------- ---------------- +
|NS|ชื่อผู้ใช้|ชื่อเต็ม|อีเมล|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
| 8 |อาร์ทิมิส|เวสลีย์ ซี. ดิลลาร์ด|[ป้องกันอีเมล]|
| 4 |สสารมืด|Patricia J. Fox|[ป้องกันอีเมล]|
| 6 |สสารมืด|Patricia J. Fox|[ป้องกันอีเมล]|
| 2 |กด|ทิฟฟานี่ จี. เบลีย์|[ป้องกันอีเมล]|
| 5 |เรื่อง|Faye H. Hartley|[ป้องกันอีเมล]|
| 3 |จรวด|คริสโตเฟอร์ เอส. เพย์ตัน|[ป้องกันอีเมล]|
| 7 |จรวด|คริสโตเฟอร์ เอส. เพย์ตัน|[ป้องกันอีเมล]|
| 1 |ราศีกันย์|คลอดด์ เอ็ม. โมริ|[ป้องกันอีเมล]|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
ดังที่คุณเห็นจากตารางด้านบน เรามีค่าที่ซ้ำกันสองค่าที่ทำให้ฐานข้อมูลใหญ่ขึ้นโดยไม่มีเหตุผลและทำให้ความเร็วช้าลง
ให้เราเรียนรู้ว่าเราจะลบค่าเหล่านี้ได้อย่างไร
#1 – ลบเข้าร่วม
วิธีหนึ่งในการลบแถวที่ซ้ำกันในฐานข้อมูลคือการใช้คำสั่ง MySQL DELETE JOIN อย่างไรก็ตาม แบบสอบถามใช้รหัสเพื่อลบค่าที่ซ้ำกัน
ตัวอย่างเช่น ในการลบค่าที่ซ้ำกันในตารางผู้ใช้ด้านบน เราสามารถป้อน:
ลบ ตารางที่ 1 จาก ตารางผู้ใช้1 ด้านใน เข้าร่วม ผู้ใช้ table2 ที่ไหน table1.id<table2.id และ table1.email=table2.email;เมื่อคุณดำเนินการสืบค้นข้อมูลข้างต้น คุณจะลบค่าที่ซ้ำกันตามที่แสดงในผลลัพธ์ด้านล่าง:
mysql> ลบ ตารางที่ 1 จาก ตารางผู้ใช้1 ด้านใน เข้าร่วม ผู้ใช้ table2 ที่ไหน table1.id<table2.id และ table1.email=table2.email;แบบสอบถามตกลง, 2แถวที่ได้รับผลกระทบ(0.01วินาที)
mysql> เลือก * จาก ผู้ใช้ สั่งโดย ชื่อผู้ใช้;
+ ---- + ------------ + ----------------------- + ------- ---------------- +
|NS|ชื่อผู้ใช้|ชื่อเต็ม|อีเมล|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
| 8 |อาร์ทิมิส|เวสลีย์ ซี. ดิลลาร์ด|[ป้องกันอีเมล]|
| 6 |สสารมืด|Patricia J. Fox|[ป้องกันอีเมล]|
| 2 |กด|ทิฟฟานี่ จี. เบลีย์|[ป้องกันอีเมล]|
| 5 |เรื่อง|Faye H. Hartley|[ป้องกันอีเมล]|
| 7 |จรวด|คริสโตเฟอร์ เอส. เพย์ตัน|[ป้องกันอีเมล]|
| 1 |ราศีกันย์|คลอดด์ เอ็ม. โมริ|[ป้องกันอีเมล]|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
#2 – Row_Number() ฟังก์ชัน
วิธีที่สองที่เราสามารถทำได้คือการใช้ฟังก์ชัน MySQL row_number() ฟังก์ชันนี้รองรับใน MySQL เวอร์ชัน 8 ขึ้นไป
ทำงานโดยการกำหนดค่า int ตามลำดับให้กับแต่ละแถว โดยแถวที่มีค่าที่ซ้ำกันจะได้รับค่าที่สูงกว่า 1
หากต้องการเรียนรู้เพิ่มเติมเกี่ยวกับฟังก์ชันนี้ ให้ใช้แหล่งข้อมูลด้านล่าง:
https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_row-number
พิจารณาข้อความค้นหาด้านล่างที่ส่งคืน id ของแถวที่มีค่าที่ซ้ำกัน:
เลือก NS จาก ( เลือก NS,ROW_NUMBER()เกิน( พาร์ทิชั่น ตามชื่อผู้ใช้ สั่งโดย ชื่อผู้ใช้) เช่น row_var จาก ผู้ใช้)t1 ที่ไหน row_var> 1;เมื่อคุณดำเนินการค้นหาข้างต้น คุณควรได้รับรายการรหัสตามที่แสดงในผลลัพธ์ด้านล่าง:
+ ---- +|NS|
+ ---- +
| 6 |
| 7 |
+ ---- +
2แถว ใน ชุด (0.01วินาที)
หากคุณต้องการลบค่า ให้แทนที่คำสั่ง SELECT ด้วยคำสั่ง DELETE ดังที่แสดงด้านล่าง:
ลบ จาก ผู้ใช้ ที่ไหน NS ใน ( เลือก NS จาก ( เลือก NS,ROW_NUMBER()เกิน( พาร์ทิชั่น ตามชื่อผู้ใช้ สั่งโดย ชื่อผู้ใช้) เช่น row_var จาก ผู้ใช้)t1 ที่ไหน row_var> 1);สุดท้าย คุณสามารถตรวจสอบว่าค่าที่ซ้ำกันจะถูกลบออกโดยใช้คำสั่ง SELECT
mysql> เลือก * จาก ผู้ใช้ สั่งโดย ชื่อผู้ใช้;+ ---- + ------------ + ----------------------- + ------- ---------------- +
|NS|ชื่อผู้ใช้|ชื่อเต็ม|อีเมล|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
| 8 |อาร์ทิมิส|เวสลีย์ ซี. ดิลลาร์ด|[ป้องกันอีเมล]|
| 4 |สสารมืด|Patricia J. Fox|[ป้องกันอีเมล]|
| 2 |กด|ทิฟฟานี่ จี. เบลีย์|[ป้องกันอีเมล]|
| 5 |เรื่อง|Faye H. Hartley|[ป้องกันอีเมล]|
| 3 |จรวด|คริสโตเฟอร์ เอส. เพย์ตัน|[ป้องกันอีเมล]|
| 1 |ราศีกันย์|คลอดด์ เอ็ม. โมริ|[ป้องกันอีเมล]|
+ ---- + ------------ + ----------------------- + ------- ---------------- +
บทสรุป
ในบทช่วยสอนนี้ เราได้พูดถึงสองวิธีในการลบค่าที่ซ้ำกันออกจากฐานข้อมูล ฐานข้อมูลขนาดใหญ่ โดยเฉพาะอย่างยิ่งที่ใช้กันทั่วไป อาจมีค่าที่ซ้ำกันจำนวนมากจากการนำเข้าภายนอกและข้อผิดพลาดอื่นๆ ดังนั้นจึงจำเป็นต้องล้างค่าที่ซ้ำกันอยู่เสมอเพื่อให้แน่ใจว่าแอปพลิเคชันทำงานได้อย่างเต็มประสิทธิภาพ