Mariadb 简明教程

MariaDB - Managing Duplicates

如前文所讨论的，MariaDB 在某些情况下允许重复记录和表。由于不同的数据或对象类型，或者由于操作对象的独特寿命或存储，其中一些重复实际上并不是重复的。这些重复通常也不会有问题。

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

在某些情况下，重复确实会造成问题，并且它们经常是由隐式操作或 MariaDB 命令的宽松策略引起的。有方法可以控制此问题，查找重复项，删除重复项并阻止创建重复项。

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

Strategies and Tools

有四种关键方法可以管理重复项 -

There are four key ways to manage duplicates −

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

  2. Use INSERT…​ON DUPLICATE KEY UPDATE to update on discovery of a duplicate.

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

  4. Use INSERT IGNORE to stop insertion of duplicates.

Using Join with a Temporary Table

只需执行类似于内联连接的半连接，然后使用临时表删除找到的重复项。

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

Using INSERT

当 INSERT…​ON DUPLICATE KEY UPDATE 发现重复唯一或主键时，它会执行更新。当发现多个唯一键时，它只会更新第一个键。因此，不要在具有多个唯一索引的表上使用它。

When INSERT…​ON DUPLICATE KEY UPDATE discovers a duplicate 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): Duplicate entry '1' for key 'PRIMARY'

Note - 如果它找不到键，则 INSERT…​ON DUPLICATE KEY UPDATE 语句将像普通插入语句一样执行。

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

Using DISTINCT

DISTINCT 子句从结果中删除重复项。DISTINCT 子句的一般语法如下 -

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

SELECT DISTINCT fields
FROM table
[WHERE conditions];

Note - 带有 DISTINCT 子句的语句的结果 -

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

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

  2. When using multiple expressions, it returns unique combinations.

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

使用 DISTINT 从句对单一表达式查看以下语句 -

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

ON DUPLICATE KEY IGNORE 语句指示 MariaDB 在发现重复记录时取消插入。查看下面给出的示例 -

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

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

同样，注意重复背后的逻辑。某些表基于该表数据的特性需要重复记录。在管理重复记录的策略中满足该需求。

Also, note the logic behind duplicates. Some tables require duplicates based on the nature of that table data. Accommodate that need in your strategy for managing duplicate records.