Navicat usage tutorial: how to locate and delete repeating values in MySQL

Download the latest version of Navicat Premium

Navicat Premium is a management tool that can connect to multiple databases. It allows you to connect to MySQL, Oracle and PostgreSQL databases simultaneously with a single program, making it more convenient to manage different types of databases.

One of the biggest problems with DBA is dealing with duplicate data. No matter how much we guard against it, duplicate data will always try to get into the table. Duplicate data is a big problem because it affects the application view (each item should be unique), distorts statistics, and, in severe cases, increases server overhead.

In this tip, we will learn how to identify duplicate data in MySQL and how to delete them without deleting valuable valid data.

Repetition type

Most of the duplicate records you will encounter are one of two different types: repetitive meaning and non-unique keys. In this article, we will deal with repetitive meanings; in the next article, we will deal with non-unique keys.

When the copy is not a copy

Repetitive meaning is the most common type of repetition. In this case, the contents of two or more fields are different, but they have the same meaning. You can think of it as semantic duplicate data.

Consider the following table excerpt:

one