MySQL Handling Duplicate Data

Some MySQL data tables may contain duplicate records. In some cases we allow duplicate data to exist, but sometimes we also need to delete these duplicate data.

In this chapter, we will introduce how to prevent duplicate data from appearing in data tables and how to delete duplicate data from data tables.

Preventing Duplicate Data in Tables

You can set specified fields in a MySQL data table asPRIMARY KEYorUNIQUEindex to ensure data uniqueness.

Let's try an example: The following table has no index or primary key, so the table allows multiple duplicate records.

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

If you want to set the fields first_name and last_name in the table so that data cannot be duplicated, you can use dual primary key mode to set data uniqueness. If you set dual primary keys, the default value of that key cannot be NULL; it can be set to NOT NULL. As shown below:

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

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

The difference between INSERT IGNORE INTO and INSERT INTO is that INSERT IGNORE INTO ignores data that already exists in the database. If the database has no such data, it inserts new data; if it has data, it skips that record. This preserves existing data in the database and achieves the purpose of inserting data in gaps.

The following example uses INSERT IGNORE INTO. After execution, no error occurs, and no duplicate data is inserted into the data table:

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: When inserting data, after record uniqueness is set, if duplicate data is inserted, no error is returned; only a warning is returned. REPLACE INTO: If a record with the same primary key or unique key exists, it is deleted first, then the new record is inserted.

Another way to set data uniqueness is to add a UNIQUE index, as shown below:

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

Counting Duplicate Data

Below we will count the number of duplicate records of first_name and last_name in the table:

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

The above query statement will return the number of duplicate records in the person_tbl table. In general, to query duplicate values, do the following:

  • Determine which column contains values that may be duplicated.
  • List those columns using COUNT(*) in the column selection list.
  • List the columns in the GROUP BY clause.
  • Use the HAVING clause to set the number of duplicates greater than 1.

Filtering Duplicate Data

If you need to read non-duplicate data, you can use the DISTINCT keyword in the SELECT statement to filter duplicate data.

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

You can also use GROUP BY to read non-duplicate data from the data table:

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

Deleting Duplicate Data

If you want to delete duplicate data from the 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, sex);
mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;

Of course, you can also use the simple method of adding INDEX (index) and PRIMARY KEY (primary key) to the data table to delete duplicate records in the table. The method is as follows:

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