sqlite
mysql

Migrate SQLite to MySQL

Certified — live-tested path

The 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.

Start SQLite → MySQL Migration

Free tier · no credit card

SQLite vs MySQL

sqlite

SQLite

the embedded database that ships inside everything.

mysql

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.

SQLiteMySQL
INTEGERbigint
REALdouble
TEXTLONGTEXT
BLOBLONGBLOB
NUMERICdouble
BOOLEANtinyint(1)
DATETIMEdatetime
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

Ready to move SQLite to MySQL?

Set up in under two minutes. Validation and rollback included.

Start Free Migration