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);