Shulou(Shulou.com)06/01 Report--

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 duplicate data.

In this section, we will show you how to prevent duplicate data in the data table and how to delete the duplicate data in the data table.

Prevent duplicate data in the table

You can set the specified field to PRIMARY KEY (primary key) or UNIQUE (unique) index in the MySQL data table to ensure 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 (20), last_name CHAR (20), sex CHAR (10))

If you want to set the table field first_name,last_name data can not be repeated, you can set the double primary key mode to set the data uniqueness, if you set a double primary key, then the default value of that key can not be NULL, can be set to NOT NULL. As follows:

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, the SQL statement will not execute successfully and throw an error when the duplicate data is inserted.

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 there is no data in the database, and skips it if there is any data. In this way, the data that already exists in the database can be retained to achieve the purpose of inserting data in the gap.

The following example uses INSERT IGNORE INTO and does not make an error or insert duplicate data 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 setting the uniqueness of the record, if you insert duplicate data, no error will be returned, only as a warning. In REPLACE INTO, if there is a record with the same primary or unique, it will be deleted first. And insert a new record.

Another way to set the uniqueness of the data is to add a UNIQUE index, as follows:

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

Statistical 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 returns 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 duplicate. Use the columns listed in COUNT (*) in the column selection list. The columns listed in the GROUP BY clause. The HAVING clause sets the number of repeats to be greater than 1.

Filter duplicate data

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

Mysql > SELECT DISTINCT last_name, first_name-> FROM person_tbl

You can also use GROUP BY to read non-duplicated data 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 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 add INDEX (index) and PRIMAY KEY (primary key) to the data table to delete duplicate records in the table. The methods are as follows:

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

These are the details of the learning notes of MySQL dealing with duplicate data. For more information about MySQL dealing with duplicate data, please pay attention to other related articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.