MySQL Table Options Like ENGINE and CHARSET Fail on PostgreSQL and SQLite

A MySQL schema ends its CREATE TABLE statements with options such as ENGINE=InnoDB DEFAULT CHARSET=utf8mb4. SQLite and PostgreSQL refuse the statement at the first option.

Dumps made with mysqldump carry these options on every table. SQLite’s own options, WITHOUT ROWID and STRICT, fail on the other three engines in the same way.

CREATE TABLE t (x INTEGER) ENGINE=InnoDB; SELECT COUNT(*) FROM t, run on four engines: SQLite 3.45.1 error: near "=": syntax error; PostgreSQL 16.15 error: syntax error at or near "ENGINE"; MySQL 8.0.46 0; MariaDB 10.11.14 0.

What Each Engine Answered

The example

CREATE TABLE t (x INTEGER) ENGINE=InnoDB;
SELECT COUNT(*) FROM t;

gave these answers:

Engine	Answer
SQLite 3.45.1	error: `near "=": syntax error`
PostgreSQL 16.15	error: `syntax error at or near "ENGINE"`
MySQL 8.0.46	`0`
MariaDB 10.11.14	`0`

Every Form BareSQL Checks

BareSQL Portable checks 11 forms of this, and each was run on all four engines. yes means the engine ran it and the engines that ran it agreed, no means the engine refused it, and a value is what the engine answered where they disagreed.

Form	SQLite	PostgreSQL	MySQL	MariaDB	What to write instead
`ENGINE`	no	no	yes	yes
`WITHOUT ROWID`	yes	no	no	no
`STRICT`	yes	no	no	no
`WITH (...)`	no	yes	no	no
`INHERITS`	no	yes	no	no
`PARTITION BY`	no	yes	yes	yes
`index inside CREATE TABLE`	no	no	yes	yes	Create the index with a separate CREATE INDEX statement.
`CHARACTER SET`	no	no	yes	yes
`COMMENT`	yes	no	yes	yes
`ON UPDATE`	no	no	yes	yes	Set the column in the UPDATE statements, or with a trigger per engine.
`COLLATE`	no	no	yes	yes

What to Write Instead

This form answers the same on every engine:

CREATE TABLE t (x INTEGER);
SELECT COUNT(*) FROM t;

Engine	Answer
SQLite 3.45.1	`0`
PostgreSQL 16.15	`0`
MySQL 8.0.46	`0`
MariaDB 10.11.14	`0`

Leave the options out of schemas meant for every engine, and create indexes with separate CREATE INDEX statements.

What BareSQL Portable Reports

Saved as example.sql, the example gets this from baresql lint --all:

$ baresql lint --all example.sql
example.sql:1:28: error: The table option ENGINE works on MySQL and MariaDB; SQLite and PostgreSQL refuse it. [table-options]
    ENGINE
    Leave the options out of schemas meant for every engine, and create indexes with separate CREATE INDEX statements.

1 problem: 1 error.

baresql rules table-options prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.