Compare Data in Two MySQL Tables

dbForge Studio for MySQL provides a sophisticated tool for error-free MySQL data comparison and synchronization. Give it a try and propel your routine database update process to the next level!

  • Compare and synchronize live MySQL databases and scripts folders
  • Deploy changes to MySQL, MariaDB, and Percona
  • Generate data synchronization scripts
  • Support for command-line interface
  • Automate and schedule database synchronization
  • Synchronize databases of any size quickly and flawlessly
  • Generate data comparison reports
  • Compare two databases in one IDE

Introduction to Data Compare

The Data Compare feature of dbForge Studio allows you to quickly compare data between two MySQL or MariaDB databases, swiftly spot differences in MySQL data, and synchronize changes in the blink of an eye. Watch the video to see Data Compare in action!

Handy Data Comparison Wizard for MySQL data

Easily customizable Data Comparison wizard makes it possible to effortlessly perform MySQL data comparison tasks even for non-experienced users. More than that, the dbForge MySQL data compare tool allows configuring the data comparison to fit the project requirements and saving the settings for further use.

Rich comparison options

  • Include or exclude views and tables by mask
  • Ignore views, tables, and\or columns by mask
  • Round float types
  • Ignore leading and trailing spaces
  • Ignore case
... and many, many more.

Compare data between two databases - MySQL comparison wizard

Fine-tune your table comparisons with Custom
Data Mapping

After you select the Source and the Target for MySQL data comparison, dbForge data diff tool automatically maps tables and views having the same owner and name. In some situations, database objects can not be mapped automatically and that's where the Custom Mapping option becomes indispensable.

Mapping Tables and Views
With dbForge Studio for MySQL, you can include in comparison those tables and views that were not automatically mapped by mapping them on the Mapping tab of the Data Comparison wizard.

Mapping Columns
It is also possible to map columns having different names and columns with different data types.

You can unmap views, tables, and\or columns if required.
MySQL compare data in two tables - Custom Mapping

View and manage the comparison results

dbForge MySQL data diff tool provides a comprehensive display of data differences and boasts a set of options for effective management of the comparison results.

  • Filter and sort objects in the result grid
  • Exclude objects from synchronization
  • Search across data
  • Hide identical columns
  • Display the row differences
  • Show the data differences

MySQL synchronization tool - view comparison results

Export the comparison report

After you have run the comparison, you can export the comparison results. You can generate the comparison report in one of the following formats:

  • CSV
  • Excel
  • HTML
The Generate Comparison Report feature may prove useful for producing detailed reports, or for monitoring the database comparison results.

MySQL compare data in two databases - Export the comparison report

MySQL data synchronization

The user-friendly Data Synchronization wizard will walk you through the process of configuring data diffs deployment. The wizard's options are bound to make the process of MySQL data synchronization smooth and error-free.

  • Generate the sync script for deployment or review
  • Limit bulk size
  • Add scripts to be executed before and\or after the deployment
  • Show the action plan
  • Expose synchronization warnings

MySQL data diff tool - MySQL data sync

Schedule and automate synchronization tasks

MySQL data compare tool that comes with dbForge Studio for MySQL supports the command-line interface, making it possible to automate and schedule routine data comparison and synchronization tasks.

  • Save MySQL data comparison and\or synchronization settings as a .bat file
  • Use Command Prompt or PowerShell for automatic comparisons or synchronizations
  • Schedule the .bat file execution with Windows Task Scheduler or another scheduler program

MySQL compare table data - Schedule and automate synchronization tasks

How to compare two tables
in MySQL

Data consistency is the key to database management. Comparing two tables allows you to find discrepancies between them. In dbForge Studio for MySQL, you can easily perform this operation.

For example, imagine that you want to compare the customer table that exists in both the sakila and sakila_prod databases. For this, you need to:

  1. Click Comparison > New Data Comparison.
  2. Specify the required type, connection, and database in the Source and Target sections.
  3. Click Mapping and select the customer checkbox.
  4. Finally, click Compare.
MySQL data diff tool - MySQL data sync

Find matching records

When you have several databases with similar data, identifying matching records can help you find differences and, thus, validate the integrity of the data.

Let's consider the scenario where we need to retrieve data from the sakila_prod.customer table, but only includes records where the customer_id exists in the sakila.customer table, and there is a matching record in terms of both customer_id and first_name. To cope with this, run this query with specific conditions:

FROM sakila_prod.customer AS c
WHERE c.customer_id IN (SELECT
  FROM sakila.customer AS c2)
  FROM sakila.customer AS c2
  WHERE c.customer_id = c2.customer_id
  AND c.first_name = c2.first_name);

As a result, the duplicate rows will be returned.

MySQL compare table data - Schedule and automate synchronization tasks

Find unmatching records

To keep data valid and accurate, you can also check databases for unmatching records. This is especially useful for data analysis and troubleshooting. It helps improve the overall reliability of your database and data management.

In order to detect non-matching records, we're going to use this statement based on specific conditions:

FROM sakila_prod.customer AS c
  FROM sakila.customer
  AS c2
  WHERE c.customer_id = c2.customer_id
  AND c.first_name = c2.first_name);

As you can see, the query has obtained records from sakila_prod.customer where there is no matching record in terms of both customer_id and first_name in the sakila.customer table.

MySQL data diff tool - MySQL data sync

Spot missing records in a source database

Another way to ensure data consistency is to compare two identical tables in different databases and discover missing data in one of them. It enables you to identify potential errors in the data.

For instance, to get records from the sakila.customer table where there is no matching record in the sakila_prod.customer table based on the customer_id column, you can use this statement:

FROM sakila.customer
AS c
  FROM sakila_prod.customer AS c2
  WHERE c.customer_id = c2.customer_id);

This query returns the records from sakila.customer that do not have corresponding matches in the sakila_prod.customer table. In dbForge Studio for MySQL, there is a special tab for reviewing missing record. It's called Only in Source.

MySQL compare table data - Schedule and automate synchronization tasks

Detect absent records in a target table

We have listed enough methods for validating data in databases, but there is an additional one. You can find missing records not only in a source table but also in a target table.

To accomplish this, you can execute the following query with a particular subquery:

FROM sakila_prod.custome
AS c
WHERE c.customer_id NOT IN (SELECT
  FROM sakila.customer);

With the statement, we retrieve records from the sakila_prod.customer table where the customer_id does not appear in the customer_id column of the sakila.customer table. There is the Only in Target tab in dbForge Studio for MySQL for displaying such records. As the target table contains no records, you can see this tab empty and the query has returned nothing.

MySQL data diff tool - MySQL data sync


dbForge Studio for MySQL possesses a bunch of ample features for MySQL databases synchronization, data export and import, code completion and code formatting, generating and analyzing data, designing databases and that’s not the half of it! Fly the Studio before you buy it and get all this functionality all in one.

dbForge Studio for MySQL

The most intelligent tool for MySQL development and management

Availability in the editions of dbForge Studio for MySQL


Data comparison and synchronization tool
Command-line interface for data comparison and synchronization