Comprehensive MySQL processing of duplicate data code and mysql DATA code

Comprehensive MySQL processing of duplicate data code and mysql DATA code

Some MySQL Data Tables may have duplicate records. In some cases, we allow the existence of duplicate data, but sometimes we also need to delete these duplicate data.

This section describes how to prevent duplicate data in a data table and how to delete duplicate data in a data table.

I. prevent duplicate data in the table
You can set the specified field in the MySQL DATA table as the primary key (primary key) or UNIQUE (UNIQUE) index to ensure data uniqueness.

Let's try an example: The following table does not have an index or a primary key, so this 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 to be unique, you can set the dual primary key mode to set the data uniqueness. If you have set the dual primary key, the default value of that key cannot be NULL. It 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 have set a unique index, the SQL statement cannot be executed successfully when repeated data is inserted, and an error is thrown.

The difference between insert ignore into and insert into is that insert ignore ignores existing data in the database. If there is no data in the database, INSERT new data. If there is data, skip this data. In this way, the existing data in the database can be retained to insert data in the gap.

The following example uses insert ignore into. After execution, no error occurs and duplicate data is not 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 you INSERT data, if you INSERT duplicate data after the uniqueness of the record is set, no error is returned and only returned as a warning. Replace into into deletes a record with the same primary or unique. Insert a new record.

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

Ii. counting duplicate data
The number of repeated records of first_name and last_name in the statistical table is as follows:

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

The preceding query statement returns the number of repeated records in the person_tbl table. To query duplicate values, perform the following operations:

1. Determine which column contains duplicate values.
2. In the column selection list, use COUNT (*) to list those columns.
3. columns listed in the group by clause.
4. Set the HAVING clause to be greater than 1.

Iii. Filtering duplicate data
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  -> ORDER BY last_name;

You can also use group by to read non-repeated data in the data table:

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

Iv. Delete duplicate data
To delete duplicate data from a data table, 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 TABLE tmp RENAME TO person_tbl;

Of course, you can also add INDEX and primay 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);

The above is all about how MySQL processes repeated data. I hope it will be helpful for your learning.