java SQL 中的 MySQL 和 SQLite 差异
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/751160/
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
MySQL and SQLite differences in SQL
提问by Jakub Arnold
I'm writing java application that is using both SQLiteand MySQLusing JDBC.
我正在编写使用JDBC同时使用SQLite和MySQL 的Java 应用程序。
Are there any differences in SQLfor those databases? Can I use same queries for both SQLiteand MySQL, or is there any db specific stuff, that doesn't work on the other one?
这些数据库的SQL有什么不同吗？我可以对SQLite和MySQL使用相同的查询，还是有任何特定于数据库的东西，在另一个上不起作用？
As far I've worked only with MySQL, so I don't really know much about SQLite.
到目前为止，我只使用MySQL，所以我对SQLite不太了解。
采纳答案by vartec
If you stick to ANSI SQL92, you'll should be fine.
如果你坚持使用ANSI SQL92，你应该没问题。
There are some SQL92 features missing from both MySQL and SQLite (e.g. FULL OUTER JOIN). MySQL has both RIGHT JOIN, and LEFT JOIN, SQLite only the LEFT JOIN. SQLite doesn't support FOREIGN KEY constraints, neither does MySQL with MyISAM tables. SQLite of course doesn't have GRANT/REVOKE, as permission system is based on underlying OS's file permissions.
MySQL 和SQLite 都缺少一些SQL92 特性（例如FULL OUTER JOIN）。MySQL 有 RIGHT JOIN 和 LEFT JOIN，SQLite 只有 LEFT JOIN。SQLite 不支持 FOREIGN KEY 约束，带有 MyISAM 表的 MySQL 也不支持。SQLite 当然没有 GRANT/REVOKE，因为权限系统基于底层操作系统的文件权限。
回答by chris
I'm doing something similar. There are a few differences in addition to the ones mentioned that I ran into:
我正在做类似的事情。除了我遇到的那些之外，还有一些差异：
in the newer versions of SQLite3 with the Xerial JDBC driver, foreign keys are indeed supported. SQLite supports inline foreign key constraint definition:
CREATE TABLE Blah (foreignId Integer REFERENCES OtherTable (id));
MySQL (with InnoDB) will acceptthe same syntax, but won't actually enforce the constraint unless you use a separate FOREIGN KEY clause which explicitly names the foreign table and key column(s):
CREATE TABLE Blah (foreignId INTEGER, FOREIGN KEY foreignId REFERENCES OtherTable (id));old versions of the SQLite JDBC driver don't support Statement.RETURN_GENERATED_KEYS; fixed in newer Xerial drivers.
the syntax for auto-incrementing keys differs; SQLite: (id INTEGER PRIMARY KEY ASC, ...); MySQL: (id INTEGER PRIMARY KEY AUTO_INCREMENT, ...)
SQLite accepts n-way comma-delimited joins:
SELECT * FROM A, B, C ON (A.x = B.y AND B.y = C.z);
MySQL does not; the following works in both:
SELECT * FROM A INNER JOIN B ON A.x = B.y INNER JOIN C ON B.y = C.z;With respect to the type differences, a related annoyance with SQLite's JDBC drivers is that the same column can produce different types via ResultSet.getObject(.); for example, an Integer or a Long depending on the magnitude of the number contained.
auto-incrementing keys in SQLite MUST be declared type INTEGER; in MySQL any numeric integer type works.
在带有 Xerial JDBC 驱动程序的较新版本的 SQLite3 中，确实支持外键。SQLite 支持内联外键约束定义：
CREATE TABLE Blah (foreignId Integer REFERENCES OtherTable (id));
MySQL（带有 InnoDB）将接受相同的语法，但实际上不会强制执行约束，除非您使用单独的 FOREIGN KEY 子句显式命名外部表和键列：
CREATE TABLE Blah (foreignId INTEGER, FOREIGN KEY foreignId参考其他表（id））；旧版本的 SQLite JDBC 驱动程序不支持 Statement.RETURN_GENERATED_KEYS；在较新的 Xerial 驱动程序中修复。
自动递增键的语法不同；SQLite: (id INTEGER PRIMARY KEY ASC, ...); MySQL: (id INTEGER PRIMARY KEY AUTO_INCREMENT, ...)
SQLite 接受 n 路逗号分隔连接：
SELECT * FROM A, B, C ON (Ax = By AND By = Cz);
MySQL 没有；以下两者都适用：
SELECT * FROM A INNER JOIN B ON Ax = By INNER JOIN C ON By = Cz;关于类型差异，SQLite 的 JDBC 驱动程序的一个相关烦恼是同一列可以通过 ResultSet.getObject(.); 产生不同的类型；例如，一个 Integer 或一个 Long 取决于包含的数字的大小。
SQLite 中的自增键必须声明为 INTEGER 类型；在 MySQL 中，任何数字整数类型都有效。
回答by dan04
A big difference is the type system. SQLite lets you put any type of data in any column. It does, however, cast data to the declared type of the column if possible.
一个很大的区别是类型系统。SQLite 允许您将任何类型的数据放在任何列中。但是，如果可能，它确实将数据转换为列的声明类型。
