SQL 如何在 Linux 上比较两个 SQLite 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2093263/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to compare two SQLite databases on Linux
提问by Nicolas Raoul
Using Linux, I want to compare two SQLite databases that have the same schema. There will be just a few differences.
使用 Linux，我想比较两个具有相同架构的 SQLite 数据库。只会有一些差异。
Is there a tool that would output these differences? Preferably output them to the command line, so that I can grep/sed them.
有没有可以输出这些差异的工具？最好将它们输出到命令行，以便我可以对它们进行 grep/sed。
SQLite uses SQL, so a general SQL tool might also do.
SQLite 使用 SQL，所以一般的 SQL 工具也可以。
回答by honk
Please have a look at the SQLite Release 3.8.10which was released on May 7, 2015. This release for the first time contains the sqldiff.exeutility programfor computing the differences between two SQLite database files. Most likely this program will also be part of future releases.
请查看2015 年 5 月 7 日发布的SQLite Release 3.8.10。该版本首次包含用于计算两个 SQLite 数据库文件之间差异的sqldiff.exe实用程序。该程序很可能也将成为未来版本的一部分。
The sqldiff.execommand-line line tool should work for all supported operating systems and offers several switches for altering its output behavior. Example usage:
该sqldiff.exe命令行工具行应为所有支持的操作系统和提供了几个开关的工作用于改变它的输出行为。用法示例：
sqldiff [options] database1.sqlite database2.sqlite
If no options are specified, then the output of sqldiff.exeis SQL statements that will transform database1.sqlite(the "source" database) into database2.sqlite(the "destination" database).
如果未指定任何选项，则sqldiff.exe的输出是将database1.sqlite（“源”数据库）转换为database2.sqlite（“目标”数据库）的SQL 语句。
However, there are also certain limitations. For example, the sqldiff.exeutility (at least currently) does not display differences in TRIGGERs, VIEWs, or virtual tables.
但是，也有一定的局限性。例如，sqldiff.exe实用程序（至少目前）不显示触发器、视图或虚拟表中的差异。
Sample command and output
示例命令和输出
I took a simple key-value store database (db1.sqlite) and made
a copy of it (db2.sqlite). I then inserted one key-value pair into
db2.sqlite. After that I ran the following command:
我使用了一个简单的键值存储数据库 ( db1.sqlite) 并复制了它 ( db2.sqlite)。然后我将一个键值对插入到
db2.sqlite. 之后，我运行了以下命令：
sqldiff db1.sqlite db2.sqlite
and got the following output:
并得到以下输出：
INSERT INTO my_table(rowid,"key",value) VALUES(1,'D:\Test\Test.txt',x'aabbccdd');
UPDATE my_table_size SET counter=1 WHERE rowid=1;
INSERT INTO my_table(rowid,"key",value) VALUES(1,'D:\Test\Test.txt',x'aabbccdd');
更新 my_table_size SET counter=1 WHERE rowid=1;
The table my_table_sizewas automatically updated by a TRIGGER after the key-value pair was inserted to my_table. I then ran sqldiff.exeagain, but this time with with db2.sqliteas first argument and db1.sqliteas second argument:
将my_table_size键值对插入到 后，该表由 TRIGGER 自动更新my_table。然后我再次运行sqldiff.exe，但这次使用db2.sqlite第一个参数和db1.sqlite第二个参数：
sqldiff db2.sqlite db1.sqlite
and got the following output:
并得到以下输出：
DELETE FROM my_table WHERE rowid=1;
UPDATE my_table_size SET counter=0 WHERE rowid=1;
DELETE FROM my_table WHERE rowid=1;
更新 my_table_size SET counter=0 WHERE rowid=1;
sqldiff download links
sqldiff 下载链接
Since SQLite version 3.10.2which was released on January 20, 2016, the 32-bit binaries for sqldiffcan be directly downloaded from the SQLite Download Page. They can be found in the sqlite toolsarchives for the corresponding operating systems (see the Precompiled Binariessections). For example, here are the links to the sqlite toolsarchives of version 3.31.1:
从2016 年 1 月 20 日发布的SQLite 3.10.2 版本开始，可以直接从SQLite 下载页面下载sqldiff的 32 位二进制文件。它们可以在相应操作系统的sqlite 工具存档中找到（请参阅预编译二进制文件部分）。例如，这里是3.31.1 版本的sqlite 工具档案的链接：
For SQLite versions prior to version 3.10.2, the SQLite website hosts 32-bit binaries for sqldiff, but does not link to them. Here are the links to sqldiffof version 3.8.10:
对于 3.10.2 版之前的 SQLite 版本，SQLite 网站托管sqldiff 的32 位二进制文件，但不链接到它们。以下是3.8.10 版sqldiff的链接：
If you need 64-bit binaries, then you have to downloadthe raw sources and compile them by yourself. (The file sqldiff.cis located in the toolsub-directory of the archive containing the sources.)
如果您需要 64 位二进制文件，则必须下载原始源代码并自行编译。（文件sqldiff.c位于包含源的存档的工具子目录中。）
回答by laalto
One possibility is to use the sqlite3command line client to export both databases and then diff the output. For example,
一种可能性是使用sqlite3命令行客户端导出两个数据库，然后比较输出。例如，
sqlite3 first.sqlite .dump >first.dump
sqlite3 second.sqlite .dump >second.dump
diff first.dump second.dump
回答by Matthew Lock
It's for Windows only, but there's a free/open source SQLite database comparison app on Code Project: http://www.codeproject.com/Articles/220018/SQLite-Compare-Utilitywhich works pretty well for me.
它仅适用于 Windows，但代码项目中有一个免费/开源的 SQLite 数据库比较应用程序：http: //www.codeproject.com/Articles/220018/SQLite-Compare-Utility对我来说效果很好。
回答by Julian
Try SQLite Diff.
尝试SQLite 差异。
NOTE: This is a paid product, $25 at the time of this writing, and I'm notin any way related to the authors.
注意：这是一个付费产品，在撰写本文时为 25 美元，我与作者没有任何关系。
回答by Klaas-Z4us-V
Since 20 January 2016 sqldiff (Windows sqldiff.exe) is included in the zip-file at http://sqlite.org/download.html
自 2016 年 1 月 20 日起，sqldiff (Windows sqldiff.exe) 包含在http://sqlite.org/download.html的 zip 文件中
