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

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

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

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

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

ในการป้องกันไม่ให้มีการสร้างระเบียนหลายระเบียนที่มีค่าชื่อและนามสกุลเดียวกันในตารางนี้ให้เพิ่ม 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. หากระเบียนไม่ซ้ำกับระเบียนที่มีอยู่ MySQLi จะแทรกตามปกติ หากเร็กคอร์ดซ้ำกันคีย์เวิร์ด IGNORE จะบอกให้ MySQLi ทิ้งโดยไม่ทำให้เกิดข้อผิดพลาด

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

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 clause ด้วย

• เพิ่มส่วนคำสั่ง 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 หรือ PRIMAY KEY ลงในตารางนั้น แม้ว่าตารางนี้จะมีอยู่แล้ว แต่คุณสามารถใช้เทคนิคนี้เพื่อลบระเบียนที่ซ้ำกันและคุณจะปลอดภัยในอนาคตเช่นกัน

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