Some methods to process various duplicates in MySQL _ MySQL

This article mainly introduces some methods to process various duplicates in MySQL, including repeated processing of tables and query results, for more information, see tables or result sets that contain duplicate records. Sometimes it is allowed, but sometimes it needs to stop repeated records. Sometimes it needs to identify duplicate records to be deleted from the table. This chapter describes how to prevent duplicate records from being deleted in a table.
Prevent duplicate tables from occurring:

You can use the corresponding fields of the primary key or UNIQUE index table to prevent repeated records. Let's take an example. The following table does not contain such an index or primary key, so it will allow repeated records of first_name and last_name.

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

You can use the corresponding fields of the primary key or UNIQUE index to prevent repeated records. Let's take an example. The following table does not contain such an index or primary key, so it will allow repeated records of first_name and last_name.

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

The existence of a unique index in a table usually leads to errors. if a record is inserted in the table, the existing record in the column or column of the index is repeatedly defined.
Use insert ignore instead of INSERT. If the record does not reproduce the record, MySQL inserts it as usual. If the record is repeated with the IGNORE keyword, MySQL will discard it quietly without generating an error.

In the following example, there are no errors and duplicate records are not inserted.

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)

Use replace instead of INSERT. If the record is new, INSERT it. If it is a duplicate, the new record will replace the old one:

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)

REPLACE instead of INSERT. If the record is new, INSERT it. If it is a duplicate, the new record will replace the old one:

Another way to force uniqueness is to add a UNIQUE index instead of a primary key table.

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

Duplicate computing and confirmation:

The following lists the repeated records in the first_name and last_name tables.

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

This query returns repeated records in all person_tbl tables in the list. To identify duplicate values, perform the following operations:

• Determine which column contains values that may be repeated.
• Select the columns listed with COUNT (*) in the column list.
• And columns listed in the group by clause.
• The newly added HAVING clause eliminates the number of unique value groups greater than 1.

Duplicate query results eliminated:

You can use the DISTINCT and SELECT statements to find the unique record in the table.

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

DISTINCT is to add a column named in a group by clause, and select another method. This has the effect of removing the values of the specified column in the unique combination that is repeated and only selected:

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

Delete duplicate table replace:

If there is a duplicate record in a table, you want to delete all duplicate records from the table. let's take a look at the example of the following program.

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;

An easy way to delete duplicate records from a table is to add keys, table indexes, or PRIMAY. If the table is already available, use this method to delete duplicate records.

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