MySQL handles duplicate data

MySQL handles duplicate data

There may be duplicate records in some MySQL data tables, and in some cases we allow duplicate data, but sometimes we also need to delete the duplicated data.

In this section we will show you how to prevent data tables from repeating data and how to delete duplicate data from a data table.

Prevent duplicate data from appearing in a table

You can set the specified field in the MySQL data table PRIMARY key (primary key) or unique (unique) index to guarantee the uniqueness of the data.

Let's try an example: there are no indexes and primary keys in the following table, so the table allows multiple duplicate records.

CREATE TABLE person_tbl(    first_name char, last_name char),  Sex CHAR(ten));             

If you want to set the table field First_name,last_name data cannot be duplicated, you can set the double primary key mode to set the uniqueness of the data, if you set a double primary key, then the default value of the key cannot be null, can be set to NOT NULL. As shown below:

CREATE TABLE person_tbl(   first_name char() not NULL, last_name char(  ) not NULL, sex CHAR(last_name, first_name)  ); 

If we set a unique index, the SQL statement will fail to execute successfully and throw an error when inserting duplicate data.

The difference between insert IGNORE into and insert into is that insert IGNORE ignores data that already exists in the database, inserts new data if the database has no data, and skips the data if there is data. This preserves the data that already exists in the database for the purpose of inserting data into the gap.

The following instance uses insert IGNORE into, does not make an error after execution, and does not insert duplicate data into the data table:

Mysql>INSERT IGNORE into Person_tbl(Last_Name,First_Name) -VALUES( ' Jay ', ' Thomas ');QueryOk, 1Row affected (0.00 Secmysql> INSERT IGNORE into person_tbl  (last_name< Span class= "pun" >, First_name)  -> Values (  ' Jay '   ' Thomas ' ); query Ok, 0  rows affected  (0.00 Sec )  

Insert IGNORE into when data is inserted, after the uniqueness of the record is set, if you insert duplicate data, no error is returned, only as a warning. Replace into into if there are primary or unique records, delete them first. Insert a new record again.

Another unique way to set data is to add a unique index, as follows:

CREATE TABLE person_tbl(   first_name char() not NULL, last_name char(  ) not NULL, sex CHAR(last_name, first_name)) ; 

Statistical data duplication

Below we will count the number of duplicate records for First_Name and last_name in the statistics:

MySQL> SELECT COUNT(*) as repetitions, last_name, GROUP by Last_Name,>1;            

The above query statement returns the number of records that were duplicated in the Person_tbl table. In general, query for duplicate values, do the following:

• Determine which column contains values that may be duplicated.
• In the column selection list, use the columns listed by COUNT (*).
• The columns listed in the GROUP BY clause.
• The HAVING clause sets the number of repetitions greater than 1.

Filter duplicate data

If you need to read the data that is not duplicated, you can use the DISTINCT keyword in the SELECT statement to filter the duplicate data.

MySQL> SELECT DISTINCT last_name, first_name,    ORDER by last_name; 

You can also use GROUP by to read data that is not duplicated in the data table:

MySQL> SELECT last_name, first_name,    last_name, first_ Name);          

Delete duplicate data

If you want to delete duplicate data from a data table, you can use the following SQL statement:

MySQL> CREATE TABLE tmp SELECT last_name, first_name,from person_tbl;  (last_name, first_name);  MySQL> DROP TABLE person_tbl;  MySQL> ALTER TABLE tmp RENAME to Person_tbl;         

Of course you can also add index (index) and Primay key (primary key) in the data table to remove duplicate records from the table. Here's how:

MySQL> ALTER IGNORE TABLE person_tbl    -(last_name, first_name);  

MySQL handles duplicate data