แบบสอบถาม MySQL จัดการกับข้อมูลที่ซ้ำกันอย่างไรกรองเพื่อลบคำสั่งข้อมูลที่ซ้ำกัน

MySQLคิวรีจัดการกับข้อมูลที่ซ้ำกันอย่างไรกรองเพื่อลบคำสั่งข้อมูลที่ซ้ำกัน

MySQL การจัดการข้อมูลที่ซ้ำกัน

อาจมีบันทึกที่ซ้ำกันในตารางข้อมูล MySQL บางตาราง ในบางกรณี เราอนุญาตให้มีข้อมูลที่ซ้ำกัน แต่บางครั้ง เราจำเป็นต้องลบข้อมูลที่ซ้ำกันเหล่านี้ด้วย

ในบทนี้ เราจะแนะนำวิธีป้องกันข้อมูลซ้ำซ้อนในตารางข้อมูล และวิธีลบข้อมูลที่ซ้ำกันในตารางข้อมูล

ป้องกันข้อมูลซ้ำซ้อนในตาราง

คุณสามารถตั้งค่าฟิลด์ที่ระบุในตารางข้อมูล MySQL เป็น คีย์หลัก หรือ ไม่ซ้ำใคร (ไม่ซ้ำกัน) จัดทำดัชนีเพื่อความเป็นเอกลักษณ์ของข้อมูล

ลองมาดูตัวอย่างกัน: ไม่มีดัชนีและคีย์หลักในตารางด้านล่าง ดังนั้นตารางจึงอนุญาตให้มีระเบียนที่ซ้ำกันหลายรายการ

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

หากคุณต้องการตั้งค่าฟิลด์ first_name และ last_name ในตาราง ข้อมูลไม่สามารถทำซ้ำได้ คุณสามารถตั้งค่าโหมด double primary key เพื่อตั้งค่าความเป็นเอกลักษณ์ของข้อมูล หากคุณตั้งค่า double primary key ค่าเริ่มต้นของคีย์นั้น ไม่สามารถเป็น NULL ได้ แต่สามารถตั้งค่าเป็น NOT 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)
);

หากเราตั้งค่าดัชนีเฉพาะ เมื่อแทรกข้อมูลที่ซ้ำกัน คำสั่ง SQL จะไม่สามารถดำเนินการได้สำเร็จและเกิดข้อผิดพลาด

ข้อแตกต่างระหว่าง INSERT IGNORE INTO และ INSERT INTO คือ INSERT IGNORE จะไม่สนใจข้อมูลที่มีอยู่แล้วในฐานข้อมูล ถ้าไม่มีข้อมูลในฐานข้อมูล ก็จะแทรกข้อมูลใหม่ ถ้ามีข้อมูล จะข้ามข้อมูลนี้ไปด้วยวิธีนี้ ข้อมูลที่มีอยู่ในฐานข้อมูลสามารถรักษาไว้ได้ และสามารถบรรลุวัตถุประสงค์ของการแทรกข้อมูลในช่องว่างได้

ตัวอย่างต่อไปนี้ใช้ INSERT IGNORE INTO ซึ่งดำเนินการโดยไม่มีข้อผิดพลาดและไม่แทรกข้อมูลที่ซ้ำกันลงในตารางข้อมูล:

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)

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

อีกวิธีหนึ่งในการตั้งค่าเอกลักษณ์ของข้อมูลของคุณคือการเพิ่มดัชนี 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(*) ในคอลัมน์ select list เพื่อแสดงรายการคอลัมน์เหล่านั้น
• คอลัมน์ที่แสดงอยู่ในอนุประโยค GROUP BY
• ประโยค HAVING กำหนดจำนวนการทำซ้ำที่มากกว่า 1

กรองข้อมูลที่ซ้ำกัน

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

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

คุณยังสามารถใช้ GROUP BY เพื่ออ่านข้อมูลที่ไม่ซ้ำจากตาราง:

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

ข้อมูลซ้ำซ้อน

หากคุณต้องการลบข้อมูลที่ซ้ำกันในตารางข้อมูล คุณสามารถใช้คำสั่ง SQL ต่อไปนี้:

mysql> CREATE TABLE tmp SELECT last_name, first_name, sex
    ->                  FROM person_tbl;
    ->                  GROUP BY (last_name, first_name, sex);
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);