比较MariaDB/MySQL的EXCEPT的替代方案 [英] Alternative for EXCEPT for MariaDB/MySQL comparing all columns

问题描述

我知道MariaDB和MySQL不支持EXCEPT. 我想找到类似这样的替代方法:

I know MariaDB and MySQL don't support EXCEPT. I would like to find an alternative to something like this:

SELECT * FROM table
EXCEPT
SELECT * FROM backup_table

表和backup_table具有相同的架构.

Where the table and backup_table have the same schema.

我看过的所有帖子都建议我使用"WHERE column IN(...)"来比较单个列. 我的问题是，我需要为每个表比较两个表之间的所有列.我希望将其编写为遍历所有表的过程或函数，以查找数据库中的任何更改.基本上，我想找出所有已更新或插入到我所有表中的记录.

All the posts I've seen suggests that I compare a single column using "WHERE column IN (...)". The problem in my case is that I need to compare all the columns between the two tables for every table. I'm hoping to write this as procedure or function looping through all the tables, looking for any changes in the database. Basically, I want to find out all the records that have been updated or inserted in all my tables.

推荐答案

如果我遇到该任务，我将使用反联接模式.这是一个外部联接，用于返回当前表中的所有行以及备份表中的匹配"行.然后，在WHERE子句中，我们排除所有具有完全匹配项的行.返回不匹配的行.

If I was faced with that task, I'd use an anti-join pattern. That's an outer join, to return all rows from the current table, along with "matching" rows from the backup table. Then in the WHERE clause, we exclude all rows that had an exact match. Returning rows that don't match.

  SELECT t.*
    FROM mytable t
    LEFT
    JOIN backup_mytable s
      ON s.id        <=> t.id
     AND s.col_two   <=> t.col_two
     AND s.col_three <=> t.col_three
     AND ... 
 WHERE s.id IS NULL

这假定列id被保证为非NULL. PRIMARY KEY列(或作为表的PRIMARY KEY一部分的任何列，或具有NOT NULL约束的任何列).

This assumes that the column id is guaranteed to be non-NULL. The PRIMARY KEY column (or any column that is part of the PRIMARY KEY of the table, or any column that has a NOT NULL constraint would serve.)

此查询仅返回与备份表中的行不匹配的行.它不表示其行是否不存在，或是否更改了列的值.

This query only returns the rows that don't match a row in backup table. It doesn't indicate whether its row that doesn't exist, or whether a value of a column was changed.

要获取原始表中与备份表中的行不匹配的行，只需交换表名.

And to get rows in the original table that don't match rows in the backup table, just swap the table names.

对于表中所有列均定义为NOT NULL的表的特殊情况，我们可以在连接谓词上采用捷径.

For the special case of a table with all columns defined as NOT NULL, we could take a shortcut on the join predicates.

    FROM mytable t
 NATURAL
    LEFT
    JOIN backup_mytable s
   WHERE s.id IS NULL

这等效于在两个表中都命名为相同的所有列的LEFT JOIN带有USING子句.

That's equivalent to a LEFT JOIN with a USING clause of all columns that are named the same in both tables.

    FROM mytable t
    LEFT
    JOIN backup_mytable s
   USING (id, col_two, col_three, ...)
  WHERE s.id IS NULL

这等同于在每列上指定相等性比较(如果两个表具有相同的列)

That's equivalent to specifying an equality comparison on every column (if both tables have the same columns)

    FROM mytable t
    LEFT
    JOIN backup_mytable s
      ON s.id        = t.id
     AND s.col_two   = t.col_two
     AND s.col_three = t.col_three

在任何列中任何出现的NULL值都会使相等比较陷入困境，并返回NULL.

Any occurrences of NULL values in any of the columns are going to screw with the equality comparison, and return NULL.

这就是为什么第一个查询使用null安全比较<=>(太空飞船)运算符的原因. NULL <=> NULL将返回TRUE，而NULL = NULL将返回NULL.

And that's why the first query uses the null-safe comparison <=> (spaceship) operator. NULL <=> NULL will return TRUE, where NULL = NULL will return NULL.

对于第一个查询模式，我不会使用繁琐的输入每一列的所有比较，而是使用SQL来帮助我生成所需的SQL.

For that first query pattern, rather than tediously typing out all of those comparisons of every column, I would use SQL to help me generate the SQL I need.

 SELECT CONCAT('   AND s.`',c.column_name,'` <=> t.`',c.column_name,'`') AS `-- stmt`
   FROM information_schema.columns c
  WHERE c.table_schema = 'mydatabase'
    AND c.table_name = 'mytable'
  ORDER BY c.ordinal_position

我要获取该查询返回的行，并将其粘贴到

I'd take the rows returned by that query, and paste that in

SELECT t.*
  FROM ... t
  JOIN ... s
    ON 1=1
    -- paste here --
 WHERE s.id IS NULL
ORDER BY t.id

---

如果我需要仅与id列匹配的查询，并且需要确定哪些列已更改，则可以在SELECT列表中使用表达式.例如:

---

If I needed query that matched on just the id column, and needed to identify which columns had changed, I'd use expressions in the SELECT list. For example:

 SELECT s.`id`        <=> t.`id`         AS `match_id`
      , s.`col_one`   <=> t.`col_one`    AS `match_col_one`
      , s.`col_three` <=> t.`col_three`  AS `match_col_three`
  FROM mytable t
  JOIN backup_mytable s
    ON s.id = t.id
HAVING NOT match_col_one

在HAVING子句中引用SELECT列表中的列别名，以排除具有相同col_one值的行；返回col_one不同的行.

Here referencing the column alias in the SELECT list in a HAVING clause, to exclude rows that have the same value of col_one; returning rows where col_one is different.

同样，我将对information_schema.columns使用SQL来帮助加快查询编写过程.

Again, I would use SQL against information_schema.columns to help speed up the query writing process.

这篇关于比较MariaDB/MySQL的EXCEPT的替代方案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！