MariaDB manages duplication

As discussed in the previous lesson, MariaDB allows duplicate records and tables in some cases. S ome of these repetitions are not actually duplicates due to different data or object types, or as a result of the unique life or storage of the operational object. T hese copies are usually no problem.

In some cases, repetition does cause problems, and they often occur as a result of implicit actions or the looser tactics of MariaDB commands. T here are several ways to control this problem, find duplicates, remove duplicates, and prevent duplicate creation.

Policies and tools

There are four key ways to manage duplication -

• Use JOIN associations and delete them with temporary tables.

• Using INSERT ... D UPLICATE ON KEY UPDATE is updated when duplicates are found.

• Use DISTINCT to trim the results of the SELECT statement and remove duplicates.

• Use INSERT IGNORE to stop inserting duplicates.

Use a temporary table to connect

Simply perform a semi-connection like an internal join, and then remove the duplicates found using the temporary table.

Use INSERT

When INSERT ... O N DUPLICATE KEY UPDATE performs an update when it finds a duplicate unique or primary key. W hen more than one unique key is found, it updates only the first one. T herefore, do not use it on tables with multiple unique indexes.

Look at the following example, which shows what happens in a table containing index values when inserted into a fill field -

INSERT INTO add_dupl VALUES (1,'Apple');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

Note - If no keys are found, INSERT ... T HE DUPLICATE KEY UPDATE statement is executed in a similar way to a normal insert statement.

Use DISTINCT

The DISTINCT clause removes duplicates from the result. T he general syntax of the DISTINCT clause is as follows -

SELECT DISTINCT fields
FROM table
[WHERE conditions];

Note - Results of statements with DISTINCT clauses -

• When an expression is used, it returns a unique value for it.

• When more than one expression is used, it returns a unique combination.

• It does not ignore NULL values; T herefore, the result also contains NULL as the unique value.

Use the DISTINCT clause of a single expression to view the following statement -

SELECT DISTINCT product_id
FROM products
WHERE product_name = 'DustBlaster 5000';

Use multiple expressions to see the following example -

SELECT DISTINCT product_name, product_id
FROM products
WHERE product_id < 30

Use INSERT IGNORE

The INSERT IGNORE statement instructs MariaDB to de-insert duplicate records when they are found. C heck out the examples of usage given below -

mysql> INSERT IGNORE INTO customer_tbl (LN, FN)
   VALUES( 'Lex', 'Luther');

Also, pay attention to the logic of repetition. S ome tables need to be duplicated based on the nature of the table data. M eet your needs in a policy that manages duplicate records.