Shulou(Shulou.com)05/31 Report--

How to deal with duplicate data in MySQL, aiming at this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

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 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 be duplicated.

L use those columns listed in COUNT (*) in the column selection list.

L columns listed in the GROUP BY clause.

L 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); this is the answer to how to deal with duplicate data in MySQL. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

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.