MySQL handles duplicate data some of the 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. In this section we will show you how to prevent data tables from repeating data and how to delete duplicate data from a data table. Prevent duplicate data from appearing in tables you can set the specified field in the MySQL data table primary key (primary key) or unique (unique) Index to ensure data uniqueness. 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, last_name char), sex char (10)); If you want to set a field in a table first_name,last_name data cannot be duplicated, You can set the dual primary key mode to set the uniqueness of the data, if you set a double primary key, then the default value of the key cannot be null, can be set to NOT NULL. As follows: CREATE TABLE person_tbl (first_name char) not NULL, last_name char (a) not NULL, sex CHAR (Ten), PRIMARY KEY (last_ Name, first_name)); If we set a unique index, the SQL statement will fail to execute successfully and throw an error when inserting duplicate data. 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 the database has no data, and skips the data if there is data. This preserves the data that already exists in the database for the purpose of inserting data into the gap. The following instance uses insert IGNORE into, does not make an error after execution, and does not 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 data is inserted, after the uniqueness of the record is set, if you insert duplicate data, no error is returned, only as a warning. Replace into if there are primary or unique records, delete them first. Insert a new record again. Another way to set data uniqueness is to add a unique index, as follows: CREATE TABLE person_tbl (first_name char) not NULL, last_name char (a) not NULL, sex C HAR (last_name, first_name)); Query duplicate record Select User_name,count (*) as Count from User_table Group by user_name Havin G count>1; SELECT * from people where Peopleid in (select Peopleid from People GROUP by Peopleid have count (Peopleid) > 1) Statistics repeating data below we will count the number of duplicate records in first_name and last_name:mysql> SELECT COUNT (*) as repetitions, last_name, first_name- From Person_tbl-GROUP by last_name, first_name, have repetitions > 1; The above query statement returns the number of records that were duplicated in the Person_tbl table. In general, query for duplicate values, do the following:
- Determine which column contains values that may be duplicated.
- In the column selection list, use the columns listed by COUNT (*).
- The columns listed in the GROUP BY clause.
- The HAVING clause sets the number of repetitions 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, ORDER by last_name; You can also use GROUP by to read non-duplicates in the data table Data:mysql> SELECT last_name, first_name-Person_tbl, GROUP by (last_name, first_name); Delete duplicate data if you want to remove the heavy Complex data, 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); mysql> DROP TABLE person_tbl; mysql> ALTER table tmp RENAME to person_tbl; Of course you can also add index and Primay key (primary key) in the datasheet to remove duplicate records from the table. The method is as follows:mysql> ALTER IGNORE TABLE person_tbl, ADD PRIMARY KEY (last_name, first_name);
MySQL handles duplicate data
