Some methods of dealing with various repetitions in Mysql _mysql

Sometimes a table or result set contains duplicate records. Sometimes it is allowed, but sometimes it needs to stop repeating records. Sometimes it needs to recognize that duplicate records are removed from the table. This chapter describes how to prevent duplicate records from occurring in one table from deleting duplicates that already exist.
prevent occurrences in a repeating table:

You can prevent duplicate records by using the corresponding fields on the primary key or a unique index table. Let's cite an example where the following table does not contain such an index or a primary key, so it allows you to record first_name and last_name duplicates

CREATE TABLE person_tbl
(
  first_name char (),
  last_name char (),
  sex char)
;

You can prevent duplicate records by using the corresponding fields on the primary key or a unique index table. Let's cite an example where the following table does not contain such an index or primary key, so it allows duplicate first_name and last_name Records

CREATE TABLE person_tbl
(
  first_name char () NOT NULL,
  last_name char (=) NOT null,
  sex char (10) C13/>primary KEY (last_name, first_name)
);

The existence of a unique index in a table usually results in an error, and if a record is inserted in the table, the existing record in the column or column of the index is defined repeatedly.
Use Insert ignore instead of insert. If the record does not duplicate an existing record, MySQL will insert it as usual. If the record is repeated ignore the keyword tells MySQL to silently discard it without generating an error.

There are no errors in the following example, and duplicate records are not inserted.

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)

Use substitution instead of insert. If the record is new it inserts the insert. If it is a duplicate, the new record will replace the old one:

Mysql> REPLACE into Person_tbl (last_name, first_name)
  -> VALUES (' Ajay ', ' Kumar ');
Query OK, 1 row Affected (0.00 sec)
mysql> REPLACE into Person_tbl (last_name, first_name)
  -> VALUES (' Aja Y ', ' Kumar ');
Query OK, 2 rows Affected (0.00 sec)

Use replace instead of insert. If the record is new it inserts the insert. If it is a duplicate, the new record will replace the old one:

Another way to enforce uniqueness is to add a unique index instead of a primary key table.

CREATE TABLE person_tbl
(
  first_name char () NOT NULL,
  last_name char (=) NOT null,
  sex char
  UNIQUE (last_name, first_name)
);

Calculation and determination of duplicates:

The following are duplicate records in the query number first_name and last_name tables.

Mysql> SELECT COUNT (*) as repetitions, last_name, first_name
  -> from Person_tbl->
  GROUP by last_name, First_Name
  -> having repetitions > 1;

This query returns duplicate records from all PERSON_TBL tables in a list. In general, duplicate values are recognized, do the following:

• Determine which column contains values that may be duplicated.
• Those columns listed with COUNT (*) in the column selection list.
• And the columns listed in the GROUP BY clause.
• The new HAVING clause eliminates the requirement that a unique value is greater than 1 of the number of groups.

To eliminate duplicate query results:

You can use distinct with the SELECT statement to find the only record in the table.

Mysql> SELECT DISTINCT last_name, first_name-> from Person_tbl-> order by
  last_name;

Distinct is an alternative method of adding a named column in a GROUP BY clause. This has the effect of removing the value of the specified column in a unique combination of duplicates and only selections:

Mysql> SELECT last_name, first_name
  -> from Person_tbl
  -> GROUP by (last_name, first_name);

Remove duplicate use table replacement:

If you have duplicate records in a table, you want to remove all duplicate records from the table, and look at the following examples of programs.

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;

A simple way to remove duplicate records from a table is to add a key, table index, or Primay. If the table is already available, use this method to delete duplicate records.

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