将两个表相互比较并获得差异打印输出

I have two tables in my MySQL that are exactly the same in terms of columns, we can call them id, name, path, date. Now I would like to get a comparison of these two tables in order to get how table 1 differs to table 2 and how table 2 differs to table 1. I would simply like to print it in my Java program, for now a simple System.out.print of the difference would be perfect.

我的MySQL中有两个表在列方面完全相同，我们可以将它们称为id，name，path，date。现在我想对这两个表进行比较，以获得表1与表2的不同之处以及表2与表1的不同之处。我只想在我的Java程序中打印它，现在是一个简单的系统。 out.print的差异将是完美的。

I only want to check the difference of entries in the name column in the both tables. Any help is super appreciated!

我只想检查两个表中名称列中条目的差异。任何帮助都非常感谢！

4 个解决方案

#1

If you just want to see a list of names that exist in table1 but not in table2, try the following:

如果您只想查看table1中存在但不在table2中的名称列表，请尝试以下操作：

SELECT a.name
FROM table1 a
LEFT JOIN table2 b ON a.name = b.name
WHERE table2.name IS NULL

Swap table1 and table2 in that query to get the unmatched names in table2.

在该查询中交换table1和table2以获取table2中不匹配的名称。

#2

The Answer by reaanb is correct and should be accepted.

reaanb的回答是正确的，应该被接受。

Here is example Java JDBC code showing his SQL code in action. I do not use MySQL (as in the Question). Instead this example uses the H2 Database Engine, creating a temporary in-memory database.

下面是示例Java JDBC代码，显示了他的SQL代码。我不使用MySQL（如问题中所述）。相反，此示例使用H2数据库引擎，创建临时内存数据库。

try {
    Class.forName ( "org.h2.Driver" );
} catch ( final ClassNotFoundException e ) {
    e.printStackTrace ( );
}
try ( Connection conn = DriverManager.getConnection ( "jdbc:h2:mem:trash_me_db" ) ;
      Statement stmt = conn.createStatement ( ) ) {
    String sql = null;

    // Table 1
    sql = "CREATE TABLE t1_ (\n" +
            "  id_ INT IDENTITY ,\n" +
            "  name_ VARCHAR \n" +
            ");";
    stmt.executeUpdate ( sql );
    stmt.execute ( "INSERT INTO t1_ ( name_ ) VALUES ('Jesse');" );
    stmt.execute ( "INSERT INTO t1_ ( name_ ) VALUES ('Wendy');" );
    stmt.execute ( "INSERT INTO t1_ ( name_ ) VALUES ('Lisa');" );
    stmt.execute ( "INSERT INTO t1_ ( name_ ) VALUES ('Susan');" );
    stmt.execute ( "INSERT INTO t1_ ( name_ ) VALUES ('Jimmy');" );
    stmt.execute ( "INSERT INTO t1_ ( name_ ) VALUES ('Wine');" );

    // Dump
    System.out.println ( "\nDump table t1_" );
    try ( ResultSet rs = stmt.executeQuery ( "SELECT * FROM t1_ ;" ) ) {
        while ( rs.next ( ) ) {
            final int id = rs.getInt ( "id_" );    //Retrieve by column name
            final String name = rs.getString ( "name_" );
            System.out.println ( "id: " + id + " | name: " + name );
        }
    }

    // Table 2
    sql = "CREATE TABLE t2_ (\n" +
            "  id_ INT IDENTITY ,\n" +
            "  name_ VARCHAR \n" +
            ");";
    stmt.executeUpdate ( sql );
    stmt.execute ( "INSERT INTO t2_ ( name_ ) VALUES ('Jesse');" );
    stmt.execute ( "INSERT INTO t2_ ( name_ ) VALUES ('Wendy');" );
    stmt.execute ( "INSERT INTO t2_ ( name_ ) VALUES ('Lisa');" );
    stmt.execute ( "INSERT INTO t2_ ( name_ ) VALUES ('Susan');" );
    stmt.execute ( "INSERT INTO t2_ ( name_ ) VALUES ('Jimmy');" );
    stmt.execute ( "INSERT INTO t2_ ( name_ ) VALUES ('Beer');" );

    // Dump
    System.out.println ( "\nDump table t2_" );
    try ( ResultSet rs = stmt.executeQuery ( "SELECT * FROM t2_ ;" ) ) {
        while ( rs.next ( ) ) {
            final int id = rs.getInt ( "id_" );    //Retrieve by column name
            final String name = rs.getString ( "name_" );
            System.out.println ( "id: " + id + " | name: " + name );
        }
    }

    // Difference, left.
    System.out.println ( "\nShow names that exist in t1_ but not in t2_." );
    sql = "SELECT *\n" +
            "FROM t1_ \n" +
            "LEFT JOIN t2_ ON t1_.name_ = t2_.name_\n" +
            "WHERE t2_.name_ IS NULL";
    try ( ResultSet rs = stmt.executeQuery ( sql ) ) {
        while ( rs.next ( ) ) {
            final int id = rs.getInt ( "id_" );    //Retrieve by column name
            final String name = rs.getString ( "name_" );
            System.out.println ( "id: " + id + " | name: " + name );
        }
    }

    // Difference, right.
    System.out.println ( "\nShow names that exist in t2_ but not in t1_." );
    sql = "SELECT *\n" +
            "FROM t2_ \n" +
            "LEFT JOIN t1_ ON t2_.name_ = t1_.name_\n" +
            "WHERE t1_.name_ IS NULL";
    try ( ResultSet rs = stmt.executeQuery ( sql ) ) {
        while ( rs.next ( ) ) {
            final int id = rs.getInt ( "id_" );    //Retrieve by column name
            final String name = rs.getString ( "name_" );
            System.out.println ( "id: " + id + " | name: " + name );
        }
    }


} catch ( final SQLException e ) {
    e.printStackTrace ( );
}

When run.

跑步的时候。

Dump table t1_
id: 1 | name: Jesse
id: 2 | name: Wendy
id: 3 | name: Lisa
id: 4 | name: Susan
id: 5 | name: Jimmy
id: 6 | name: Wine

Dump table t2_
id: 1 | name: Jesse
id: 2 | name: Wendy
id: 3 | name: Lisa
id: 4 | name: Susan
id: 5 | name: Jimmy
id: 6 | name: Beer

Show names that exist in t1_ but not in t2_.
id: 6 | name: Wine

Show names that exist in t2_ but not in t1_.
id: 6 | name: Beer

#3

This will join 2 tables on id and show you rows where name is different:

这将在id上连接2个表，并显示名称不同的行：

select a.name, b.name 
from table1 a 
join table2 b on a.id = b.id
where a.name <> b.name

#4

I know for sure that you could diff two databases and see differences between them like (test db and prod db)

我肯定知道您可以区分两个数据库并查看它们之间的差异，如（test db和prod db）

here is the documentation

这是文档

https://dev.mysql.com/doc/mysql-utilities/1.5/en/mysqldbcompare.html

#1