MySQL compatibility

What tDB translates from MySQL to SQLite, what it refuses by name, and the limits worth knowing before you port a schema.

tDB stores data in SQLite and translates MySQL SQL on the way in. Anything it cannot translate raises an error naming the construct.

Translated for you

MySQL	Becomes
`ON DUPLICATE KEY UPDATE`	`ON CONFLICT (...) DO UPDATE`
`AUTO_INCREMENT`, `ENGINE=`, `DEFAULT CHARSET=`	dropped
inline `KEY` / `UNIQUE KEY` in `CREATE TABLE`	separate `CREATE INDEX` statements
`ON UPDATE CURRENT_TIMESTAMP`	a trigger
`DELETE ... LIMIT`, `LIMIT offset, count`	SQLite's forms
backticks, `IF()`, `IFNULL()`, `CONCAT()`, `NOW()`, `UNIX_TIMESTAMP()`	SQLite equivalents
`MATCH (a, b) AGAINST (? IN BOOLEAN MODE)`	a substring search — see the approximation
`ALTER TABLE … MODIFY`/`CHANGE COLUMN`, `ADD FOREIGN KEY`	the table is rebuilt
`SET @v := (SELECT IF(…))` + `PREPARE`/`EXECUTE`	the condition is evaluated and the chosen branch runs
`CREATE PROCEDURE … IF NOT EXISTS … END IF` + `CALL`	the same
`SIGNAL SQLSTATE '…' SET MESSAGE_TEXT = 'x'` (in a trigger)	`SELECT RAISE(ABORT, 'x')`
`UPDATE a JOIN b ON … SET …`, multi-table `DELETE`	an `EXISTS` subquery
`IN (?)` with an array, `VALUES ?` with rows	expanded into a list
`DATABASE()`, `SCHEMA()`	`'main'`
`VERSION()`	`sqlite_version()`, so a script gating on MariaDB takes its not-MariaDB branch
`INT UNSIGNED ... AUTO_INCREMENT`	`INTEGER ... AUTOINCREMENT`
`AUTO_INCREMENT` on a column of a composite `PRIMARY KEY`	the key narrows to that column; the wider key becomes a separate index
`IF c THEN a; ELSE b; END IF;` in a trigger	two triggers with `WHEN` guards
`DECLARE v; SELECT e INTO v; ... v ...`	a correlated subquery, when the variable is assigned once and read
`UPDATE t JOIN u ON c SET a` in a trigger	`UPDATE t SET a FROM u WHERE c`
`CREATE TRIGGER IF NOT EXISTS`	kept verbatim

Full-text search is approximated

MATCH … AGAINST becomes a substring search across the same columns — a LIKE '%'||?||'%' OR b LIKE …. This is an approximation, not an equivalence:

No relevance ranking. A MATCH with no ORDER BY of its own comes back in an arbitrary order.
BOOLEAN MODE operators are taken literally. +john looks for the text +john, and john* for john*.
It is more permissive than MySQL. MySQL applies a minimum token length (innodb_ft_min_token_size, 3 by default), so a two-character search returns nothing there and does return rows here.

Case-insensitive text

tDB creates text columns COLLATE NOCASE, so comparisons ignore case as they do in MySQL:

sql

-- finds a row stored as 'license:ab12'
SELECT * FROM players WHERE license = 'LICENSE:AB12'

A UNIQUE key likewise rejects the same value in another case. A column declared BINARY, or with a _bin or _cs collation, stays byte-exact.

Refused by name

These raise rather than being approximated:

DATE_ADD, DATE_SUB, INTERVAL, STRAIGHT_JOIN, SQL_CALC_FOUND_ROWS, GROUP_CONCAT ... SEPARATOR, INFORMATION_SCHEMA, ALTER TABLE ... DROP FOREIGN KEY, DECLARE, SELECT ... INTO, UPDATE ... JOIN

Rewrite the statement, or move it to raw SQL and write SQLite directly. Replacements for the ones that come up most:

Refused	Write instead
`INFORMATION_SCHEMA`	`sqlite_master` and the `pragma_*` table functions
`DROP FOREIGN KEY`	rebuild the table without the constraint
`DECLARE`, `SELECT ... INTO`	a scalar subquery
`UPDATE ... JOIN`	`UPDATE t SET a FROM u WHERE c`, or `WHERE ... IN (SELECT ...)`

Most of these maintain a counter, so check your rewrite's numbers against the original.

Known limits

COLLATE NOCASE folds ASCII only. Ä and ä still compare as different; MySQL's general_ci folds them.
Collation is applied when a table is created. A table predating tDB keeps binary collation until it is recreated.
RETURNING is not supported through the query path.
SQLite type affinity is looser than MySQL's. A column declared INT accepts a string, so statements relying on MySQL rejecting bad input will not be rejected here.

tDB

Still stuck? Support can answer it directly.

tDB on the store Contact support

Was this page helpful?