MariaDB - Managing Duppcates

MariaDB, as discussed in earper lessons, allows duppcate records and tables in some situations. Some of these duppcates are not in fact duppcates due to distinct data or object types, or as a result of unique pfespan or storage of the operation object. These duppcates also typically pose no problems.

In some situations, duppcates do cause problems, and they often appear due to imppcit actions or the lenient popcy of a MariaDB command. There are ways to control this issue, find duppcates, delete duppcates, and prevent duppcate creation.

Strategies and Tools

There are four key ways to manage duppcates −

Fish for them with JOIN, and delete them with a temporary table.

Use INSERT...ON DUPLICATE KEY UPDATE to update on discovery of a duppcate.

Use DISTINCT to prune the results of a SELECT statement and remove duppcates.

Use INSERT IGNORE to stop insertion of duppcates.

Using Join with a Temporary Table

Simply perform a semi-join pke an inner join, and then remove the duppcates found with a temporary table.

Using INSERT

When INSERT...ON DUPLICATE KEY UPDATE discovers a duppcate unique or primary key, it performs an update. On discovery of multiple unique keys, it updates only the first. Hence, do not use it on tables with multiple unique indices.

Review the following example, which reveals what happens in a table containing indexed values on insertion into a populated field −

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

Note − If it finds no key, an INSERT...ON DUPLICATE KEY UPDATE statement executes pke a normal insert statement.

Using DISTINCT

DISTINCT clauses remove duppcates from results. The general syntax for a DISTINCT clause is as follows −

SELECT DISTINCT fields
FROM table
[WHERE conditions];

Note − The results of a statement with a DISTINCT clause −

When using one expression, it returns unique values for it.

When using multiple expressions, it returns unique combinations.

It does not ignore NULL values; thus, results also contain NULLs as unique values.

Review the following statement using a DISTINCT clause for a single expression −

SELECT DISTINCT product_id
FROM products
WHERE product_name =  DustBlaster 5000 ;

Review the following example using multiple expressions −

SELECT DISTINCT product_name, product_id
FROM products
WHERE product_id < 30

Using INSERT IGNORE

An INSERT IGNORE statement instructs MariaDB to cancel insertion on discovery of a duppcate record. Review an example of its use given below −

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