SQLite to MySQL Converter: Syntax Guide & Examples

Why Convert SQLite to MySQL?

SQLite is perfect for prototyping, mobile apps, and embedded systems, but it has limitations for production web applications. It does not support concurrent writes, has no user authentication, and runs as a single file on disk. When your application outgrows SQLite, MySQL is a natural upgrade path for web-facing applications.

MySQL handles concurrent connections, offers replication for high availability, provides fine-grained user permissions, and scales to terabytes of data. Most web hosting providers include MySQL, and frameworks like Django, Rails, and Laravel support MySQL out of the box.

The biggest challenge in this migration is SQLite's flexible type system. SQLite uses type affinity, meaning any column can store any type. MySQL enforces strict types. This guide covers how to handle that difference and every other syntax change.

Key Syntax Differences

Feature	SQLite	MySQL
Auto-increment	INTEGER PRIMARY KEY AUTOINCREMENT	INT AUTO_INCREMENT PRIMARY KEY
Type system	Type affinity (flexible)	Strict type enforcement
Boolean	INTEGER (0/1)	TINYINT(1) or BOOLEAN
Date/time	TEXT / REAL / INTEGER	DATE / DATETIME / TIMESTAMP
String concatenation	||	CONCAT()
Current time	datetime('now')	NOW()
UPSERT	INSERT OR REPLACE	REPLACE INTO or ON DUPLICATE KEY
ALTER TABLE	Limited (no DROP COLUMN before 3.35)	Full ALTER TABLE support
VACUUM	VACUUM	OPTIMIZE TABLE
GLOB vs LIKE	GLOB (case-sensitive)	LIKE or LIKE BINARY

Common Conversions with Examples

AUTOINCREMENT to AUTO_INCREMENT

-- SQLite
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL
);

-- MySQL
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

In SQLite, INTEGER PRIMARY KEY without AUTOINCREMENT already auto-increments but can reuse deleted rowids. With AUTOINCREMENT, rowids are never reused. MySQL's AUTO_INCREMENT never reuses values by default.

Date and Time Handling

-- SQLite (dates stored as TEXT)
SELECT datetime('now');
SELECT date('now', '+30 days');
SELECT strftime('%Y-%m', created_at) FROM orders;
SELECT * FROM orders WHERE created_at > datetime('now', '-7 days');

-- MySQL (native date types)
SELECT NOW();
SELECT DATE_ADD(CURDATE(), INTERVAL 30 DAY);
SELECT DATE_FORMAT(created_at, '%Y-%m') FROM orders;
SELECT * FROM orders WHERE created_at > DATE_SUB(NOW(), INTERVAL 7 DAY);

String Concatenation

-- SQLite
SELECT first_name || ' ' || last_name AS full_name FROM users;

-- MySQL
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

Boolean Handling

-- SQLite (no native boolean)
CREATE TABLE tasks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    done INTEGER DEFAULT 0
);
SELECT * FROM tasks WHERE done = 1;

-- MySQL
CREATE TABLE tasks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    done TINYINT(1) DEFAULT 0
);
SELECT * FROM tasks WHERE done = 1;

UPSERT / INSERT OR REPLACE

-- SQLite
INSERT OR REPLACE INTO settings (key, value) VALUES ('theme', 'dark');

INSERT INTO settings (key, value) VALUES ('theme', 'dark')
ON CONFLICT(key) DO UPDATE SET value = excluded.value;

-- MySQL
REPLACE INTO settings (`key`, value) VALUES ('theme', 'dark');

INSERT INTO settings (`key`, value) VALUES ('theme', 'dark')
ON DUPLICATE KEY UPDATE value = VALUES(value);

Random Function

-- SQLite
SELECT * FROM quotes ORDER BY RANDOM() LIMIT 1;

-- MySQL
SELECT * FROM quotes ORDER BY RAND() LIMIT 1;

Data Type Mapping

SQLite Type	MySQL Type	Notes
INTEGER	INT or BIGINT	Check actual data range
REAL	DOUBLE	8-byte float
TEXT	VARCHAR(n) or TEXT	Use VARCHAR if length is known
BLOB	BLOB or LONGBLOB	Same concept
NUMERIC	DECIMAL(10,2)	Specify precision
TEXT (date)	DATE or DATETIME	If storing ISO 8601 dates
INTEGER (timestamp)	DATETIME	Convert with FROM_UNIXTIME()
INTEGER (boolean)	TINYINT(1)	0 and 1 values
VARCHAR(n)	VARCHAR(n)	Same (SQLite ignores length)
BOOLEAN	TINYINT(1)	SQLite stores as INTEGER

Step-by-Step Migration Guide

1. Export the SQLite schema. Run .schema in the SQLite CLI to get all CREATE TABLE statements. Save to a file for editing.
2. Convert the schema. Replace AUTOINCREMENT with AUTO_INCREMENT, add proper MySQL types (VARCHAR with lengths, DATETIME for dates), remove SQLite-specific features like WITHOUT ROWID, and add ENGINE=InnoDB for each table.
3. Export the data. Use .mode insert and .output data.sql in the SQLite CLI to generate INSERT statements. Alternatively, export as CSV with .mode csv and use MySQL's LOAD DATA INFILE.
4. Clean up the SQL. Replace SQLite's datetime('now') with NOW() in any default values. Remove IF NOT EXISTS if not needed. Ensure boolean values are 0/1.
5. Import into MySQL. Create the database in MySQL, run the schema SQL, then import the data. For large databases, disable foreign key checks during import: SET FOREIGN_KEY_CHECKS = 0;
6. Validate the data. Compare row counts between SQLite and MySQL for each table. Spot-check date values and text encoding to ensure correct transfer.

For SQL fundamentals, see What is SQL. See also: SQLite to PostgreSQL.

Convert Automatically with AI2SQL

Paste your SQLite query into the SQL Converter tool and get correct MySQL syntax with proper types, AUTO_INCREMENT, NOW(), and CONCAT(). No need to remember the SQLite-to-MySQL differences.

Try the converter free. Browse all converter tools.

Frequently Asked Questions

How do I convert SQLite AUTOINCREMENT to MySQL?

Replace INTEGER PRIMARY KEY AUTOINCREMENT with INT AUTO_INCREMENT PRIMARY KEY. In SQLite, AUTOINCREMENT is one word and requires INTEGER PRIMARY KEY. In MySQL, AUTO_INCREMENT is two words with an underscore and works with INT, BIGINT, or other integer types.

How does SQLite type affinity affect MySQL migration?

SQLite uses type affinity, meaning any column can store any type of value regardless of the declared type. A TEXT column can hold integers. MySQL enforces strict types. During migration, verify that the actual data matches the declared column types and choose MySQL types based on the real data, not just the SQLite column declaration.

How do I handle SQLite dates in MySQL?

SQLite stores dates as TEXT (ISO 8601 strings), REAL (Julian day numbers), or INTEGER (Unix timestamps). MySQL has native DATE, DATETIME, and TIMESTAMP types. For TEXT dates in ISO format, they insert directly into MySQL. For Unix timestamps, use FROM_UNIXTIME() in MySQL.