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

This article introduces you how to deal with duplicate data in mysql database, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Method 1: prevent duplicate data

In other words, when we re-design the table, we should set a UNIQUE index on the data, so that it can be unique at the time of insertion, and there will be no duplicate data. Of course, you can also set it directly to PRIMARY KEY (primary key). The effect is the same.

Let's look at a case: there are no indexes and primary keys in the following table, so the table allows multiple duplicate records.

CREATE TABLE student

(

First_name CHAR (20)

Last_name CHAR (20)

Sex CHAR (10)

);

Currently, first_name,last_name can be repeated, and if you don't want to repeat it, here are two solutions:

1. Set the double primary key mode

CREATE TABLE student

(

First_name CHAR (20) NOT NULL

Last_name CHAR (20) NOT NULL

Sex CHAR (10)

PRIMARY KEY (last_name, first_name)

);

Duplicate data cannot be inserted now.

2. Add unique index

CREATE TABLE student

(

First_name CHAR (20) NOT NULL

Last_name CHAR (20) NOT NULL

Sex CHAR (10)

UNIQUE (last_name, first_name)

);

The two forms seem to be a little different, but they can have the same effect. At this point, we can insert two duplicate pieces of data and we will find an error.

Of course, we can also verify it in the database:

SELECT COUNT (*) as repetitions, last_name, first_name

FROM student

GROUP BY last_name, first_name

HAVING repetitions > 1

Here we count the number of repeated records of first_name and last_name, which has been set in two ways, so it must be 0.

Method 2: specify whether duplicate data can be inserted at insert time

Here we are using the Insert ignore into and Insert into instructions.

(1) Insert ignore into will ignore the data that already exists in the database, insert new data if there is no data in the database, and skip this data 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.

(2) Insert into, on the other hand, directly inserts data, regardless of whether there is duplicate data in the database.

Let's give an example:

Insert ignore into student (last_name, first_name) values ('Zhang San','Li Si')

/ / result

Query OK, 1 rows affected (0.00 sec)

Insert ignore into student (last_name, first_name) values ('Zhang San','Li Si')

/ / result

Query OK, 0 rows affected (0.00 sec)

Now we can see that, that is to say, when the first insert operation is performed, if you see the database, a new record is inserted directly, so a row of records are affected, but at the second insert, the database already has the same entry, so it will not be inserted, and row 0 will be affected.

Of course, there is another instruction that can perform a similar function to insert ignore into, and that is replace into. What he means is that if there is a record with the same primary or unique, delete it first. And insert a new record.

Method 3: 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.

SELECT DISTINCT last_name, first_name

FROM student

ORDER BY last_name

You can also use GROUP BY to read non-duplicated data in the data table:

SELECT last_name, first_name

FROM student

GROUP BY (last_name, first_name)

Method 4: delete duplicate data

This situation is actually equivalent to solving it at the end of the water. Look at the following sql statement:

/ / create a temporary table based on student and filter duplicate data using group by

CREATE TABLE tmp SELECT last_name, first_name, sex

FROM student

GROUP BY (last_name, first_name)

/ / delete the original student table

DROP TABLE student

/ / rename this temporary table

ALTER TABLE tmp RENAME TO stu

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:

ALTER IGNORE TABLE student

ADD PRIMARY KEY (last_name, first_name)

On how to deal with duplicate data in the mysql database to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.