Creating SQL that's compatible with both SQLite and mysql with AUTO_INCREMENT

Question

I have a mysql .sql file, and am trying to use sqlite in-memory database for testing purposes, with the same file.

CREATE TABLE IF NOT EXISTS users ( user_id INTEGER PRIMARY KEY AUTO_INCREMENT,

When importing I get an error

• SQLITE_ERROR: near "AUTO_INCREMENT": syntax error

which is documented here:

In SQLite a column declared INTEGER PRIMARY KEY will autoincrement by itself. Remove the AUTOINCREMENT keyword and then try.

I can't remove the AUTOINCREMENT because it's required for mysql. Any advice?

Answer 1

I have a solution for you.

MySQL supports a special kind of comment syntax, for when you want to use syntax that works in a more recent version of MySQL but not in an earlier version. Maybe you've seen this sort of thing in mysqldump output:

CREATE TABLE `products` (
  `product_id` int NOT NULL,
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

Notice the character set statement in the comment, with a funny string !40101 which means "if the version of MySQL is less that 4.1.1, then ignore this string as a comment, otherwise execute it as part of an SQL statement."

Whereas in SQLite, the interior of the comment is always just a comment.

So you can define your table like this:

CREATE TABLE IF NOT EXISTS users ( 
  user_id INTEGER PRIMARY KEY /*!40101 AUTO_INCREMENT */,
   . . .

SQLite will ignore the AUTO_INCREMENT, but MySQL will use it, assuming you use version 4.1.1 or later (which is all but certain).

I just tested this with SQLite 3.8.5 and MySQL 8.0.0-dmr and the create table works in both cases.

INTEGER is okay because it's a synonym for INT in MySQL. Don't use INT in SQLite, because the primary key column won't be auto-incrementing unless you literally use the type INTEGER.

Never worry about the argument to INT in MySQL, like INT(11), that number doesn't mean anything important.