Differences between MySQL and SQLite in SQL

Question

I am writing a java application that uses SQLite and MySQL using JDBC.

Are there any differences in SQL for these databases? Can I use the same queries for SQLite and MySQL , or is there any specific db stuff that doesn't work on another?

As far as I only worked with MySQL , so I really don't know much about SQLite .

Answer 1

If you stick to ANSI SQL92 , everything will be fine.

There are some SQL92 functions that are missing in both MySQL and SQLite (for example, FULL OUTER JOIN). MySQL has both the right join and LEFT JOIN, SQLite only LEFT JOIN. SQLite does not support FOREIGN KEY constraints, or MySQL with MyISAM tables. SQLite, of course, does not have GRANT / REVOKE, since the permission system is based on the basic permissions of the OS file.

Answer 2

I'm doing something like that. There are several differences in addition to the ones I talked about:

• in newer versions of SQLite3 with the Xerial JDBC driver, foreign keys are indeed supported. SQLite supports the built-in definition of a foreign key constraint:
  CREATE TABLE Blah (foreignId Integer LINKS OtherTable (id));
  
  MySQL (with InnoDB) will accept the same syntax, but will not actually enforce the restriction unless you use a separate FOREIGN KEY clause that explicitly names the external table and key columns (columns):
  CREATE TABLE Blah (foreign INTEGER, FOREIGN KEY foreignId LINKS OtherTable (id));

• older versions of SQLite JDBC drivers do not support Statement.RETURN_GENERATED_KEYS; fixed in new Xerial drivers.

• The syntax for keys with automatic increment is different; 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 (Ax = By AND By = Cz);
  
  MySQL does not work; the following works in both cases:
  SELECT * FROM IN INER JOIN B ON Ax = By INNER JOIN C ON By = Cz;

• Regarding type differences, the annoyance associated with Jite SQLite drivers is that the same column can create different types through ResultSet.getObject (.); for example, Integer or Long, depending on the size of the number contained.

• auto-increment keys in SQLite MUST be declared as an INTEGER type; MySQL works with any numeric integer type.

Answer 3

The big difference is the type system . SQLite allows you to put any type of data in any column. However, it provides data for the declared column type, if possible.