Convert SQLite to MySQL

Drop a SQLite database file (.db, .sqlite, .sqlite3) or the SQL that sqlite3's .dump writes, and download a script for MySQL with the same tables, keys, indexes and data. Each column gets the type its values fit, and every value MySQL can't hold as it is is listed in the report.

SQLite to MySQL migration guide SQLite to MySQL data types

Your file

sqlshop_2024_sqlite.sqlSample file, 3.4 kB

18 rows converted, no fixes, none rejected

TablesSQLReport

Tables in shop_2024_sqlite.sqlSQLite dump · written by sqlite3 · utf-8

customers8 rows
7 columns
id bigint not null
name text not null
email varchar(768) not null
country varchar(768) not null
is_active tinyint(1) not null
signed_up date
created_at datetime not null
orders10 rows
9 columns
id bigint not null
customer_id bigint not null
status varchar(768) not null
total decimal(10,2) not null
items int not null
ordered_at datetime not null
shipped_at datetime
note text
receipt blob

7 columns
id	bigint not null
name	text not null
email	varchar(768) not null
country	varchar(768) not null
is_active	tinyint(1) not null
signed_up	date
created_at	datetime not null

9 columns
id	bigint not null
customer_id	bigint not null
status	varchar(768) not null
total	decimal(10,2) not null
items	int not null
ordered_at	datetime not null
shipped_at	datetime
note	text
receipt	blob

Not converted

View order_totals
Trigger orders_shipped

Each database writes these in its own language. Rewrite them for MySQL.

-- Written by IntoSQL from a SQLite dump. Load it with: mysql --default-character-set=utf8mb4 <database> < <file>
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO,ALLOW_INVALID_DATES';

CREATE TABLE `customers` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` text NOT NULL,
  `email` varchar(768) NOT NULL,
  `country` varchar(768) NOT NULL DEFAULT 'US',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `signed_up` date,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  CONSTRAINT `check1` CHECK (char_length(`country`) = 2)
) AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE `orders` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `customer_id` bigint NOT NULL,
  `status` varchar(768) NOT NULL DEFAULT 'pending',
  `total` decimal(10,2) NOT NULL,
  `items` int NOT NULL DEFAULT 1,
  `ordered_at` datetime NOT NULL,
  `shipped_at` datetime,
  `note` text,
  `receipt` blob,
  PRIMARY KEY (`id`),
  KEY `idx_orders_customer` (`customer_id`),
  KEY `idx_orders_status_date` (`status`,`ordered_at`),
  CONSTRAINT `check1` CHECK (`status` IN ('pending', 'paid', 'shipped', 'cancelled')),
  FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE
) AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

INSERT INTO `customers` (`id`,`name`,`email`,`country`,`is_active`,`signed_up`,`created_at`) VALUES (1,'Ana Martínez','ana@example.com','MX',1,'2023-01-15','2023-01-15 10:24:00');
INSERT INTO `customers` (`id`,`name`,`email`,`country`,`is_active`,`signed_up`,`created_at`) VALUES (2,'Zoë Müller','zoe@example.de','DE',1,'2023-02-03','2023-02-03 08:11:42');
INSERT INTO `customers` (`id`,`name`,`email`,`country`,`is_active`,`signed_up`,`created_at`) VALUES (3,'José Peña','jose@example.es','ES',0,'2023-03-21','2023-03-21 19:05:17');
INSERT INTO `customers` (`id`,`name`,`email`,`country`,`is_active`,`signed_up`,`created_at`) VALUES (4,'李雷','lilei@example.cn','CN',1,'2023-05-09','2023-05-09 02:40:00');
INSERT INTO `customers` (`id`,`name`,`email`,`country`,`is_active`,`signed_up`,`created_at`) VALUES (5,'Noah O\'Brien','noah@example.ie','IE',1,NULL,'2023-06-30 14:00:00');
INSERT INTO `customers` (`id`,`name`,`email`,`country`,`is_active`,`signed_up`,`created_at`) VALUES (6,'Amélie Laurent','amelie@example.fr','FR',1,'2024-01-02','2024-01-02 09:30:00');
INSERT INTO `customers` (`id`,`name`,`email`,`country`,`is_active`,`signed_up`,`created_at`) VALUES (7,'Ravi Patel','ravi@example.in','IN',0,'2024-02-29','2024-02-29 23:59:59');
INSERT INTO `customers` (`id`,`name`,`email`,`country`,`is_active`,`signed_up`,`created_at`) VALUES (8,'Sofia Rossi 🌻','sofia@example.it','IT',1,'2024-04-12','2024-04-12 12:12:12');
INSERT INTO `orders` (`id`,`customer_id`,`status`,`total`,`items`,`ordered_at`,`shipped_at`,`note`,`receipt`) VALUES (1,1,'shipped',129.9,3,'2024-03-01 10:00:00','2024-03-02 16:30:00',NULL,NULL);
INSERT INTO `orders` (`id`,`customer_id`,`status`,`total`,`items`,`ordered_at`,`shipped_at`,`note`,`receipt`) VALUES (2,2,'paid',54,1,'2024-03-02 11:15:00',NULL,'Gift wrap 🎁',NULL);
INSERT INTO `orders` (`id`,`customer_id`,`status`,`total`,`items`,`ordered_at`,`shipped_at`,`note`,`receipt`) VALUES (3,1,'cancelled',12.5,1,'2024-03-03 09:45:00',NULL,'Customer changed their mind',NULL);
INSERT INTO `orders` (`id`,`customer_id`,`status`,`total`,`items`,`ordered_at`,`shipped_at`,`note`,`receipt`) VALUES (4,4,'shipped',980,12,'2024-03-05 02:10:00','2024-03-07 08:00:00','请小心轻放',X'25504446');
INSERT INTO `orders` (`id`,`customer_id`,`status`,`total`,`items`,`ordered_at`,`shipped_at`,`note`,`receipt`) VALUES (5,5,'pending',45.75,2,'2024-03-08 17:20:00',NULL,'Leave at the door;\nring twice',NULL);
INSERT INTO `orders` (`id`,`customer_id`,`status`,`total`,`items`,`ordered_at`,`shipped_at`,`note`,`receipt`) VALUES (6,6,'shipped',310.4,5,'2024-03-10 13:00:00','2024-03-11 09:00:00','Fragile: "glass"',NULL);
INSERT INTO `orders` (`id`,`customer_id`,`status`,`total`,`items`,`ordered_at`,`shipped_at`,`note`,`receipt`) VALUES (7,8,'paid',76.2,2,'2024-03-11 18:45:00',NULL,NULL,NULL);
INSERT INTO `orders` (`id`,`customer_id`,`status`,`total`,`items`,`ordered_at`,`shipped_at`,`note`,`receipt`) VALUES (8,2,'paid',19.99,1,'2024-03-12 08:00:00',NULL,'Paid at the counter',NULL);
INSERT INTO `orders` (`id`,`customer_id`,`status`,`total`,`items`,`ordered_at`,`shipped_at`,`note`,`receipt`) VALUES (9,3,'shipped',64,2,'2024-03-14 15:30:00',NULL,'Shipping date never recorded',NULL);
INSERT INTO `orders` (`id`,`customer_id`,`status`,`total`,`items`,`ordered_at`,`shipped_at`,`note`,`receipt`) VALUES (10,7,'paid',1499,1,'2024-03-15 21:05:00',NULL,'C:\\Users\\ravi\\invoice.pdf',NULL);

SET UNIQUE_CHECKS = 1;
SET FOREIGN_KEY_CHECKS = 1;

Fixes

Nothing needed fixing.

Needs a look

1 text column compares ignoring case in the source; MySQL compares exactly, so unique keys and searches may behave differently. Use a case-insensitive collation or column type on the columns that need it.
2 views, triggers, routines or events aren't converted; the report lists each one. Recreate them by hand in MySQL.

Your file is converted on this computer and never uploaded.

Other samples:

What changes from SQLite to MySQL

Each row is a rule the converter applies, with the report line it writes when the rule changes something in your data.

declared types (any text: VARCHAR(255), DATETIME, BOOLEAN, JSON or none)
the type every value in the column fits
SQLite keeps any value in any column, so the declared type is a hint: each column gets the type all of its values fit, and a column holding mixed values keeps their text.
BOOLEAN, BOOL
tinyint(1)
When every value is 0 or 1; a column that also holds other values keeps them as numbers or text.
TEXT, VARCHAR(n), CHAR(n), CLOB
varchar(n) up to 1024 characters, text types beyond
SQLite never enforces a length; a declared length holds only when every value fits it. Text in a key, or with a default, is varchar(768) when its values fit, which MySQL 5.7 can index and give a default.
DECIMAL(p,s), NUMERIC
the exact type when every value fits it, else a double
SQLite stores these as integers or doubles; a declared precision holds only when the values fit it.
BLOB, and columns holding bytes
blob types
Bytes stay bytes; text and numbers in the same column are kept as their bytes.
DATE, DATETIME, TIMESTAMP holding ISO 8601 text
date, datetime(p)
When every value is a date or a date and time; epoch numbers and other text keep their values as they are.
JSON holding JSON text
json
When every value is a JSON object or array.
Infinity in REAL columns
NULL
MySQL's doubles have no infinity, so those values become NULL and the report counts them.
Report line: invalid_value_null
INTEGER PRIMARY KEY, AUTOINCREMENT
AUTO_INCREMENT, continuing after the highest id or sqlite_sequence's
Only a column declared exactly INTEGER PRIMARY KEY is the rowid; every row keeps its id, and the counter starts after the highest id ever handed out.
COLLATE NOCASE
exact comparison (utf8mb4_bin)
Tables use utf8mb4_bin so text compares exactly, as SQLite's does by default; NOCASE columns lose ignoring case.
Report line: case_insensitive_collation
generated columns
generated columns, STORED or VIRTUAL as in the source
MySQL computes them the same way.
generated columns with functions IntoSQL doesn't write, and the || operator
a plain column
A formula that can't be written leaves a plain column and a report line; the dump has no values for generated columns.
Report line: generated_not_translated
defaults with functions IntoSQL doesn't write (strftime, randomblob)
no default
A default that can't be written is left out and reported; CURRENT_TIMESTAMP and datetime('now') are written.
Report line: default_not_translated
CHECK constraints
translated
MySQL 5.7 keeps them without checking; the others test them as each row loads.
CHECK constraints with functions IntoSQL doesn't write
not created
A check that can't be written is left out and reported.
Report line: check_skipped
partial and expression indexes
not created
IntoSQL writes indexes on whole columns; create the others again after loading.
Report line: index_skipped
foreign keys (unenforced unless PRAGMA foreign_keys=ON)
foreign keys, loaded with FOREIGN_KEY_CHECKS=0
SQLite checks foreign keys only when told to, so rows without a parent are common: they load, and the key is left unchecked with a warning instead of stopping the load.
foreign keys to tables the dump doesn't create
not created
A key can't name a table that isn't there.
Report line: foreign_key_skipped
primary keys holding NULL, and NOT NULL columns with values that become NULL
NOT NULL dropped
SQLite lets a primary key other than the rowid hold NULL; the column can't be NOT NULL in the target, or the load would stop.
Report line: not_null_relaxed
views, triggers and virtual tables (FTS, R*Tree)
listed in the report
Their code is written in SQLite's own language, and virtual tables need SQLite's modules; they are listed for you to recreate. Their internal tables aren't copied.
Report line: not_converted_objects
dumps without CREATE TABLE (--data-only)
types read from the values
A data-only dump has only rows, so the types come from the values and the columns are numbered.
Report line: no_schema
expressions in INSERT values other than the text functions dumps write
NULL
unistr(), replace(), char() and || are evaluated as the shell writes them; other expressions can't be without a database.
Report line: expression_value
NUL characters in text
kept
The sqlite3 shell and Python cut text at a NUL character when they dump it, so only what precedes it is in the dump.
text that isn't valid UTF-8
the bad bytes as the replacement character
Dumps write such text as raw bytes; the script is UTF-8.
Report line: invalid_utf8_replaced
text decoded twice (Ã±)
copied as it is
The dump holds exactly these characters, so they are copied; the report points them out.
Report line: mojibake_suspected

In SQLite	In MySQL	Report line
declared types (any text: VARCHAR(255), DATETIME, BOOLEAN, JSON or none)	the type every value in the column fitsSQLite keeps any value in any column, so the declared type is a hint: each column gets the type all of its values fit, and a column holding mixed values keeps their text.
BOOLEAN, BOOL	tinyint(1)When every value is 0 or 1; a column that also holds other values keeps them as numbers or text.
TEXT, VARCHAR(n), CHAR(n), CLOB	varchar(n) up to 1024 characters, text types beyondSQLite never enforces a length; a declared length holds only when every value fits it. Text in a key, or with a default, is varchar(768) when its values fit, which MySQL 5.7 can index and give a default.
DECIMAL(p,s), NUMERIC	the exact type when every value fits it, else a doubleSQLite stores these as integers or doubles; a declared precision holds only when the values fit it.
BLOB, and columns holding bytes	blob typesBytes stay bytes; text and numbers in the same column are kept as their bytes.
DATE, DATETIME, TIMESTAMP holding ISO 8601 text	date, datetime(p)When every value is a date or a date and time; epoch numbers and other text keep their values as they are.
JSON holding JSON text	jsonWhen every value is a JSON object or array.
Infinity in REAL columns	NULLMySQL's doubles have no infinity, so those values become NULL and the report counts them.	invalid_value_null
INTEGER PRIMARY KEY, AUTOINCREMENT	AUTO_INCREMENT, continuing after the highest id or sqlite_sequence'sOnly a column declared exactly INTEGER PRIMARY KEY is the rowid; every row keeps its id, and the counter starts after the highest id ever handed out.
COLLATE NOCASE	exact comparison (utf8mb4_bin)Tables use utf8mb4_bin so text compares exactly, as SQLite's does by default; NOCASE columns lose ignoring case.	case_insensitive_collation
generated columns	generated columns, STORED or VIRTUAL as in the sourceMySQL computes them the same way.
generated columns with functions IntoSQL doesn't write, and the \|\| operator	a plain columnA formula that can't be written leaves a plain column and a report line; the dump has no values for generated columns.	generated_not_translated
defaults with functions IntoSQL doesn't write (strftime, randomblob)	no defaultA default that can't be written is left out and reported; CURRENT_TIMESTAMP and datetime('now') are written.	default_not_translated
CHECK constraints	translatedMySQL 5.7 keeps them without checking; the others test them as each row loads.
CHECK constraints with functions IntoSQL doesn't write	not createdA check that can't be written is left out and reported.	check_skipped
partial and expression indexes	not createdIntoSQL writes indexes on whole columns; create the others again after loading.	index_skipped
foreign keys (unenforced unless PRAGMA foreign_keys=ON)	foreign keys, loaded with FOREIGN_KEY_CHECKS=0SQLite checks foreign keys only when told to, so rows without a parent are common: they load, and the key is left unchecked with a warning instead of stopping the load.
foreign keys to tables the dump doesn't create	not createdA key can't name a table that isn't there.	foreign_key_skipped
primary keys holding NULL, and NOT NULL columns with values that become NULL	NOT NULL droppedSQLite lets a primary key other than the rowid hold NULL; the column can't be NOT NULL in the target, or the load would stop.	not_null_relaxed
views, triggers and virtual tables (FTS, R*Tree)	listed in the reportTheir code is written in SQLite's own language, and virtual tables need SQLite's modules; they are listed for you to recreate. Their internal tables aren't copied.	not_converted_objects
dumps without CREATE TABLE (--data-only)	types read from the valuesA data-only dump has only rows, so the types come from the values and the columns are numbered.	no_schema
expressions in INSERT values other than the text functions dumps write	NULLunistr(), replace(), char() and \|\| are evaluated as the shell writes them; other expressions can't be without a database.	expression_value
NUL characters in text	keptThe sqlite3 shell and Python cut text at a NUL character when they dump it, so only what precedes it is in the dump.
text that isn't valid UTF-8	the bad bytes as the replacement characterDumps write such text as raw bytes; the script is UTF-8.	invalid_utf8_replaced
text decoded twice (Ã±)	copied as it isThe dump holds exactly these characters, so they are copied; the report points them out.	mojibake_suspected

Load the script into MySQL

The script creates the tables, copies the rows, then adds keys, indexes and foreign keys, so it loads fast and stops at the first error.

$mysql --default-character-set=utf8mb4 mydb < sqlite.mysql.sql

Foreign keys are created as the dump declares them, with FOREIGN_KEY_CHECKS=0 while the rows load, so a row that points to a missing row doesn't stop the load.

For a large dump, run the client on the same machine as the server, and raise max_allowed_packet if a single row is very large.

IntoSQL Pro loads the file straight into MySQL from the desktop app or the command line, without writing a script.

Dump SQLite for the best result

The converter reads what the dump holds. These options keep more of your data in it:

The database file itself converts as it is, read page by page in your browser. Close the app that writes to it first, so no change is left behind in its -wal file.
sqlite3 app.db .dump > app.sql writes the same database as SQL, which converts too, as .sql or .sql.gz.
DB Browser for SQLite writes that SQL with File, Export, Database to SQL file, and so does Python's iterdump().

What isn't converted

Views and triggers are written in SQLite's own language. They are listed in the report by name, never dropped silently, so you can rewrite them for MySQL. Virtual tables, such as FTS5 and R*Tree, are listed too, and their shadow tables are left out.

Questions about SQLite to MySQL

Not answered here? Write to us from the contact page and a person replies. Contact page

Is my dump uploaded?

No. The converter runs in your browser and reads the dump from your disk in pieces, so it never leaves your computer. The desktop app and the command line convert on your computer too.

How large can the dump be?

The converter never holds the whole dump in memory: it reads the file twice, by ranges, and writes the script as it goes. Multi-gigabyte dumps convert in the browser, in the desktop app and with the command line.

Can I convert the .db file itself?

Yes. Drop the .db, .sqlite or .sqlite3 file: it is read in your browser and never uploaded. Text with NUL characters and every digit of a double arrive as stored, which a .dump can cut short.

Which SQLite versions can the dump come from?

Database files from SQLite 3.0 on, in UTF-8 or UTF-16, and dumps from the sqlite3 shell's .dump or Python's iterdump(). Every test file and dump comes from sqlite3 3.31 to 3.54, STRICT and WITHOUT ROWID tables included, and is compared with its source cell by cell.

How are column types decided when SQLite doesn't enforce them?

The declared type is a hint and the values decide: a column becomes an integer, decimal, double, date, datetime, tinyint(1), blob or text type when every value fits it. Text in a key or with a default is varchar(768), so MySQL 5.7 can index it.

Are INTEGER PRIMARY KEY and AUTOINCREMENT kept?

Yes. An INTEGER PRIMARY KEY becomes AUTO_INCREMENT, every row keeps its id, and the next id continues after the highest one, or after sqlite_sequence's value with AUTOINCREMENT.

What about views and triggers?

They are listed in the report by name and not converted, because each database writes them in its own language.

Which MySQL versions does the script load on?

MySQL 5.7 and later. Every test dump loads on 5.7 and 9, stopping at the first error if anything goes wrong.