Migrate SQLite to MySQL
Certified — live-tested pathThe converter maps 8 SQLite column types to MySQL. One of them changes kind on the way (NUMERIC becomes double); the table below lists every one. DBShifts converts the schema, copies the rows in parallel batches and checks the result with row counts and checksums.
Free tier · no credit card
SQLite vs MySQL
SQLite
the embedded database that ships inside everything.
MySQL
the world's most deployed open-source relational database.
SQLite to MySQL data type mapping
Pulled directly from DBShifts's own conversion rules — not a general reference table, this is what actually runs.
| SQLite | MySQL |
|---|---|
| INTEGER | bigint |
| REAL | double |
| TEXT | LONGTEXT |
| BLOB | LONGBLOB |
| NUMERIC | double |
| BOOLEAN | tinyint(1) |
| DATETIME | datetime |
| VARCHAR(255) | varchar(255) |
Example: SQLite to MySQL schema conversion
SQLite
CREATE TABLE orders ( id INTEGER PRIMARY KEY AUTOINCREMENT, customer_id INTEGER NOT NULL, total NUMERIC NOT NULL, is_gift BOOLEAN NOT NULL DEFAULT 0, notes TEXT, placed_at DATETIME NOT NULL );
MySQL
CREATE TABLE IF NOT EXISTS `orders` (
`id` bigint NOT NULL AUTO_INCREMENT,
`customer_id` bigint NOT NULL,
`total` double NOT NULL,
`is_gift` tinyint(1) NOT NULL DEFAULT 0,
`notes` LONGTEXT,
`placed_at` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;Check what each column really holds
SQLite stores any value in any column, so a column declared INTEGER can hold 'N/A', and one declared DATETIME can hold a number. MySQL's default strict mode rejects those rows with Incorrect integer value or Incorrect datetime value. DBShifts infers mixed columns, writes them safely and flags them, but the decision about what the column means stays with you.
Run SELECT typeof(col), count(*) FROM t GROUP BY 1 on the columns that matter. One storage class per column is the result you want; anything else is the list of rows to look at before the load.
NUMERIC becomes DOUBLE
SQLite NUMERIC columns map to MySQL DOUBLE, as the table shows. SQLite had already stored those values as 8-byte floats, or as integers, so DOUBLE loses nothing that was there, with one exception: integers above 2^53, which SQLite kept exact as INTEGER and a DOUBLE cannot.
DOUBLE keeps the rounding, though. If the column holds money, override it to DECIMAL(12,2) so MySQL does exact arithmetic from here on. The migration is the cheapest point to make that change.
INTEGER PRIMARY KEY is the rowid
In SQLite, an INTEGER PRIMARY KEY is the rowid. Without AUTOINCREMENT, SQLite may reuse the highest id after that row is deleted. It becomes BIGINT AUTO_INCREMENT in MySQL, and the counter is reseeded to MAX(id) + 1 after the load.
Anything that stored ids outside the database, in logs, URLs or another system, may point at a reused id from the SQLite era. In MySQL 8 ids are not reused: InnoDB persists its counter across restarts.
SQLite → MySQL conversion notes
Engine-specific rules baked into the conversion and transfer pipeline — verified by live certification of this exact pair.
- INTEGER columns are 64-bit — DBShifts maps them to BIGINT so large IDs don't overflow 32-bit INT targets.
- Typeless and mixed-affinity columns (SQLite's dynamic typing) are inferred, stringified safely, and flagged.
- NUMERIC affinity values are read exactly, not through float round-trips.
- AUTO_INCREMENT counters are reseeded to MAX(id)+1 after bulk load, so the first production insert doesn't collide.
- Oversized VARCHAR definitions from other engines are rewritten to TEXT/LONGTEXT to respect MySQL's row-size limits.
- Foreign keys are applied after data load with FOREIGN_KEY_CHECKS deferred for speed and ordering safety.
Frequently asked questions
Is SQLite to MySQL migration production-ready in DBShifts?
Yes. SQLite to MySQL is a certified pair: it passes the live certification suite on real SQLite and MySQL servers, which checks schema fidelity, row counts, type-aware checksums and paging across batches.
What happens to SQLite types that MySQL doesn't have?
Each gets the nearest MySQL type the converter treats as safe: NUMERIC becomes double. Every such decision is in the migration plan before anything runs, and any mapping can be overridden per column.
Can I keep SQLite and MySQL in sync after the initial migration?
Not continuously: SQLite keeps no change log to read. Incremental sync re-runs copy only the rows that changed, using a per-table sync key.
Go deeper
How We Live-Certified All 49 Migration Paths
The certification harness behind this page's claims.
Zero-Downtime Migration with Change Data Capture
Snapshot, stream the changes made meanwhile, and stay in sync until cutover.
Reference
Compared with other tools
Errors you may hit
Ready to move SQLite to MySQL?
Set up in under two minutes. Validation and rollback included.
Start Free Migration