SQLite syntax incompatible with MySQL?

Question

I use PDO and try to make my application support MySQL and SQLite, but in sqlite I get this error when trying to import my database schema:

SQLSTATE[HY000]: General error: 1 near "AUTO_INCREMENT": syntax error

The request is as follows:

 CREATE TABLE events ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(32) NOT NULL, title VARCHAR(64) NOT NULL, description LONGTEXT, starttime DATETIME DEFAULT '0000-00-00 00:00:00', PRIMARY KEY(id), KEY name(name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 

(and it works in a MySQL database.)

I do not understand what the problem is? Should both database systems be compatible?

Answer 1

http://www.sqlite.org/autoinc.html

In SQLite, it is called AUTOINCREMENT, not AUTO_INCREMENT

Answer 2

AUTO_INCREMENT is MySQL dependent. Apparently, SQLite has a similar thing, AUTOINCREMENT .

Answer 3

They must be compatible with ANSI SQL standards, and all SQL databases must adhere to this. However, AutoIncrement is not part of this standard, but is an additional feature implemented by some databases (including MySQL). Not all databases provide this function or may provide it in a different way or with different syntax.

Answer 4

Unfortunately, although SQL must be standard, each database implementation is different and has its own characteristics, so you need to organize your query so that it runs on SQLite.

Answer 5

No, they support a completely different set of functions. The most significant difference is that SQLite uses dynamic data types , while MySQL uses static data types , but there are many other differences.

However, they support a common subset of SQL, so you can write some simple SQL statements that will work on both systems.