MySQL - การจัดการรายการที่ซ้ำกัน

โดยทั่วไปตารางหรือชุดผลลัพธ์บางครั้งจะมีระเบียนที่ซ้ำกัน ส่วนใหญ่จะอนุญาต แต่บางครั้งก็จำเป็นต้องหยุดบันทึกที่ซ้ำกัน จำเป็นต้องระบุระเบียนที่ซ้ำกันและลบออกจากตาราง บทนี้จะอธิบายถึงวิธีการป้องกันไม่ให้เกิดระเบียนที่ซ้ำกันในตารางและวิธีลบระเบียนที่ซ้ำกันที่มีอยู่แล้ว

การป้องกันไม่ให้รายการที่ซ้ำกันเกิดขึ้นในตาราง

คุณสามารถใช้ไฟล์ PRIMARY KEY หรือก UNIQUE จัดทำดัชนีบนตารางที่มีเขตข้อมูลที่เหมาะสมเพื่อหยุดการบันทึกซ้ำ

ให้เรายกตัวอย่าง - ตารางต่อไปนี้ไม่มีดัชนีหรือคีย์หลักดังกล่าวดังนั้นจึงอนุญาตให้มีระเบียนที่ซ้ำกันสำหรับ first_name และ last_name.

CREATE TABLE person_tbl (
   first_name CHAR(20),
   last_name CHAR(20),
   sex CHAR(10)
);

ในการป้องกันไม่ให้หลายระเบียนที่มีค่าชื่อและนามสกุลเดียวกันถูกสร้างขึ้นในตารางนี้ให้เพิ่มไฟล์ PRIMARY KEYตามความหมายของมัน เมื่อคุณทำเช่นนี้คุณจำเป็นต้องประกาศคอลัมน์ที่จัดทำดัชนีไว้ด้วยNOT NULLเพราะก PRIMARY KEY ไม่อนุญาต NULL ค่า -

CREATE TABLE person_tbl (
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   PRIMARY KEY (last_name, first_name)
);

การมีดัชนีที่ไม่ซ้ำกันในตารางโดยปกติจะทำให้เกิดข้อผิดพลาดหากคุณแทรกระเบียนลงในตารางที่ทำซ้ำระเบียนที่มีอยู่ในคอลัมน์หรือคอลัมน์ที่กำหนดดัชนี

ใช้ INSERT IGNORE มากกว่าคำสั่ง INSERTคำสั่ง หากระเบียนไม่ซ้ำกับระเบียนที่มีอยู่ MySQL จะแทรกตามปกติ หากเรกคอร์ดซ้ำกันแสดงว่าไฟล์IGNORE คีย์เวิร์ดบอกให้ MySQL ทิ้งโดยไม่ทำให้เกิดข้อผิดพลาด

ตัวอย่างต่อไปนี้ไม่มีข้อผิดพลาดและในเวลาเดียวกันก็จะไม่แทรกระเบียนที่ซ้ำกันด้วย

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
   -> VALUES( 'Jay', 'Thomas');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
   -> VALUES( 'Jay', 'Thomas');
Query OK, 0 rows affected (0.00 sec)

ใช้ REPLACEคำสั่งแทนที่จะเป็นคำสั่ง INSERT หากเร็กคอร์ดใหม่จะถูกแทรกเช่นเดียวกับ INSERT หากเป็นรายการที่ซ้ำกันระเบียนใหม่จะแทนที่ระเบียนเก่า

mysql> REPLACE INTO person_tbl (last_name, first_name)
   -> VALUES( 'Ajay', 'Kumar');
Query OK, 1 row affected (0.00 sec)

mysql> REPLACE INTO person_tbl (last_name, first_name)
   -> VALUES( 'Ajay', 'Kumar');
Query OK, 2 rows affected (0.00 sec)

ควรเลือกคำสั่ง INSERT IGNORE และ REPLACE ตามพฤติกรรมการจัดการซ้ำที่คุณต้องการให้มีผล คำสั่ง INSERT IGNORE เก็บชุดแรกของระเบียนที่ซ้ำกันและทิ้งส่วนที่เหลือ คำสั่ง REPLACE จะเก็บชุดสุดท้ายของรายการที่ซ้ำกันและลบรายการก่อนหน้านี้ออก

อีกวิธีหนึ่งในการบังคับใช้ความเป็นเอกลักษณ์คือการเพิ่มไฟล์ UNIQUE ดัชนีแทนที่จะเป็นคีย์หลักไปยังตาราง

CREATE TABLE person_tbl (
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10)
   UNIQUE (last_name, first_name)
);

การนับและการระบุรายการที่ซ้ำกัน

ต่อไปนี้เป็นแบบสอบถามเพื่อนับระเบียนที่ซ้ำกันด้วย first_name และ last_name ในตาราง

mysql> SELECT COUNT(*) as repetitions, last_name, first_name
   -> FROM person_tbl
   -> GROUP BY last_name, first_name
   -> HAVING repetitions > 1;

แบบสอบถามนี้จะแสดงรายการของระเบียนที่ซ้ำกันทั้งหมดในตาราง person_tbl โดยทั่วไปในการระบุชุดของค่าที่ซ้ำกันให้ทำตามขั้นตอนด้านล่าง

  • พิจารณาว่าคอลัมน์ใดมีค่าที่อาจซ้ำกันได้

  • แสดงรายการคอลัมน์เหล่านั้นในรายการการเลือกคอลัมน์พร้อมกับ COUNT(*).

  • แสดงรายการคอลัมน์ในไฟล์ GROUP BY อนุประโยคเช่นกัน

  • เพิ่ม HAVING ประโยคที่กำจัดค่าที่ไม่ซ้ำกันโดยกำหนดให้จำนวนกลุ่มมากกว่าหนึ่ง

การกำจัดรายการที่ซ้ำกันออกจากผลการสืบค้น

คุณสามารถใช้ไฟล์ DISTINCT คำสั่งพร้อมกับคำสั่ง SELECT เพื่อค้นหาระเบียนเฉพาะที่มีอยู่ในตาราง

mysql> SELECT DISTINCT last_name, first_name
   -> FROM person_tbl
   -> ORDER BY last_name;

อีกทางเลือกหนึ่งของคำสั่ง DISTINCT คือการเพิ่ม GROUP BY clause ที่ตั้งชื่อคอลัมน์ที่คุณกำลังเลือก สิ่งนี้มีผลในการลบรายการที่ซ้ำกันและเลือกเฉพาะชุดค่าผสมที่ไม่ซ้ำกันในคอลัมน์ที่ระบุ

mysql> SELECT last_name, first_name
   -> FROM person_tbl
   -> GROUP BY (last_name, first_name);

การลบรายการที่ซ้ำกันโดยใช้การเปลี่ยนตาราง

หากคุณมีระเบียนที่ซ้ำกันในตารางและคุณต้องการลบระเบียนที่ซ้ำกันทั้งหมดออกจากตารางนั้นให้ทำตามขั้นตอนที่ระบุด้านล่าง

mysql> CREATE TABLE tmp SELECT last_name, first_name, sex
   -> FROM person_tbl;
   -> GROUP BY (last_name, first_name);

mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;

วิธีง่ายๆในการลบระเบียนที่ซ้ำกันออกจากตารางคือการเพิ่ม INDEX หรือ PRIMARY KEY ลงในตารางนั้น แม้ว่าตารางนี้จะมีอยู่แล้ว แต่คุณสามารถใช้เทคนิคนี้เพื่อลบระเบียนที่ซ้ำกันและคุณจะปลอดภัยในอนาคตเช่นกัน

mysql> ALTER IGNORE TABLE person_tbl
   -> ADD PRIMARY KEY (last_name, first_name);