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 blog post 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 (    char    ),char(+    ), 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 (   char(notNULL   ,Char(  -  not NULL ,    CHAR (ten),    PRIMARY KEY (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> INSERTIGNORE intoperson_tbl (last_name, first_name) - VALUES('Jay','Thomas'); Query OK,1Row affected (0.00sec) MySQL> INSERTIGNORE intoperson_tbl (last_name, first_name) - VALUES('Jay','Thomas'); Query OK,0Rows Affected (0.00Sec

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 (   char(notNULL   ,Char(  -  not NULL ,    CHAR (ten)    UNIQUE (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>SELECTCOUNT(* as repetitions, last_name, first_name      from person_tbl    ,GROUP by  last_name, first_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>SELECTDISTINCT  last_name, first_name    -from  person_tbl    ,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    from  person_tbl      - GROUP  by (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, Sex  ->  from   Person_tbl;  ->  group  by   (last_name, first_name); MySQL  >  drop  table   Person_tbl;mysql  >  alter  Span style= "color: #0000ff;" >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>ALTERTABLE  person_tbl    ,ADDPRIMARY  KEY (last_name, first_name);

MySQL handles duplicate data