Convert MySQL to SQLite
Drop a dump from mysqldump, mariadb-dump, mysqlpump, phpMyAdmin or Adminer, as .sql or .sql.gz, and download a script for SQLite with the same tables, keys, indexes and data. Every value SQLite can't hold as it is, and every object it doesn't carry, is listed in the report.
18 rows converted, 8 fixes, none rejected
Tables in shop_2024.sqlMySQL 8.0.46 dump · written by mysqldump · utf8mb4
customers8 rows3
7 columns id INTEGER not null name VARCHAR(100) not null email VARCHAR(150) not null country VARCHAR(2) not null is_active INTEGER not null signed_up DATE created_at TIMESTAMP not null orders10 rows5
9 columns id INTEGER not null customer_id INTEGER not null status TEXT not null total DECIMAL(10,2) not null items INTEGER not null ordered_at DATETIME not null shipped_at DATETIME note VARCHAR(255) updated_at TIMESTAMP not null
-- Written by IntoSQL from a MySQL dump. Load it with: sqlite3 -bail <database> < <file>
PRAGMA foreign_keys = OFF;
PRAGMA synchronous = OFF;
BEGIN;
CREATE TABLE customers ( -- People who bought at least once
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(100) NOT NULL,
email VARCHAR(150) NOT NULL,
country VARCHAR(2) DEFAULT 'US' NOT NULL,
is_active INTEGER DEFAULT 1 NOT NULL,
signed_up DATE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
CONSTRAINT customers_id_check CHECK (id >= 0)
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY AUTOINCREMENT,
customer_id INTEGER NOT NULL,
status TEXT DEFAULT 'pending' NOT NULL,
total DECIMAL(10,2) NOT NULL,
items INTEGER DEFAULT 1 NOT NULL,
ordered_at DATETIME NOT NULL,
shipped_at DATETIME,
note VARCHAR(255),
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
CONSTRAINT orders_id_check CHECK (id >= 0),
CONSTRAINT orders_customer_id_check CHECK (customer_id >= 0),
CONSTRAINT orders_status_check CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled', '')),
CONSTRAINT orders_items_check CHECK (items >= 0),
CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers (id)
);
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'),
(2,'Zoë Müller','zoe@example.de','DE',1,'2023-02-03','2023-02-03 08:11:42'),
(3,'José Peña','jose@example.es','ES',0,'2023-03-21','2023-03-21 19:05:17'),
(4,'李雷','lilei@example.cn','CN',1,'2023-05-09','2023-05-09 02:40:00'),
(5,'Noah O''Brien','noah@example.ie','IE',1,NULL,'2023-06-30 14:00:00'),
(6,'Amélie Laurent','amelie@example.fr','FR',1,'2024-01-02','2024-01-02 09:30:00'),
(7,'Ravi Patel','ravi@example.in','IN',0,'2024-02-29','2024-02-29 23:59:59'),
(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, updated_at) VALUES
(1,1,'shipped',129.90,3,'2024-03-01 10:00:00','2024-03-02 16:30:00',NULL,'2024-03-02 16:30:00'),
(2,2,'paid',54.00,1,'2024-03-02 11:15:00',NULL,'Gift wrap 🎁','2024-03-02 11:20:00'),
(3,1,'cancelled',12.50,1,'2024-03-03 09:45:00',NULL,'Customer changed their mind','2024-03-03 10:00:00'),
(4,4,'shipped',980.00,12,'2024-03-05 02:10:00','2024-03-07 08:00:00','请小心轻放','2024-03-07 08:00:00'),
(5,5,'pending',45.75,2,'2024-03-08 17:20:00',NULL,'Leave at the door; ring twice','2024-03-08 17:20:00'),
(6,6,'shipped',310.40,5,'2024-03-10 13:00:00','2024-03-11 09:00:00','Fragile: "glass"','2024-03-11 09:00:00'),
(7,8,'paid',76.20,2,'2024-03-11 18:45:00',NULL,NULL,'2024-03-11 18:46:00'),
(8,2,'',19.99,1,'2024-03-12 08:00:00',NULL,'Status lost in an old import','2024-03-12 08:00:00'),
(9,3,'shipped',64.00,2,'2024-03-14 15:30:00',NULL,'Shipping date never recorded','2024-03-15 10:00:00'),
(10,7,'paid',1499.00,1,'2024-03-15 21:05:00',NULL,'C:\Users\ravi\invoice.pdf','2024-03-15 21:05:00');
-- Indexes, triggers and counters, after the rows so each index builds once.
CREATE UNIQUE INDEX customers_uq_email ON customers (email);
CREATE INDEX orders_idx_customer ON orders (customer_id);
CREATE INDEX orders_idx_status_date ON orders (status, ordered_at);
UPDATE sqlite_sequence SET seq = max(seq, 1000) WHERE name = 'customers';
INSERT INTO sqlite_sequence (name, seq) SELECT 'customers', 1000 WHERE NOT EXISTS (SELECT 1 FROM sqlite_sequence WHERE name = 'customers');
UPDATE sqlite_sequence SET seq = max(seq, 5000) WHERE name = 'orders';
INSERT INTO sqlite_sequence (name, seq) SELECT 'orders', 5000 WHERE NOT EXISTS (SELECT 1 FROM sqlite_sequence WHERE name = 'orders');
CREATE TRIGGER orders_updated_at_on_update AFTER UPDATE ON orders FOR EACH ROW WHEN NEW.updated_at IS OLD.updated_at AND (NEW.id IS NOT OLD.id OR NEW.customer_id IS NOT OLD.customer_id OR NEW.status IS NOT OLD.status OR NEW.total IS NOT OLD.total OR NEW.items IS NOT OLD.items OR NEW.ordered_at IS NOT OLD.ordered_at OR NEW.shipped_at IS NOT OLD.shipped_at OR NEW.note IS NOT OLD.note)
BEGIN
UPDATE orders SET updated_at = CURRENT_TIMESTAMP WHERE rowid = NEW.rowid;
END;
COMMIT;
-- Rows whose foreign key finds no parent, if any: the load doesn't stop for them.
PRAGMA foreign_key_check;
Fixes
- "id" of customers is unsigned, so it became INTEGER with a check that it isn't negative.
- "signed_up" of customers allows NULL now: some of its values couldn't be stored and are NULL.
- The default of "signed_up" in customers, 0000‑00‑00, isn't a date SQLite accepts, so the column has no default.
- "customer_id" of orders is unsigned, so it became INTEGER with a check that it isn't negative.
- "id" of orders is unsigned, so it became INTEGER with a check that it isn't negative.
- "items" of orders is unsigned, so it became INTEGER with a check that it isn't negative.
- 1 row of "status" in orders holds the empty value MySQL stores for invalid entries; the check of its values allows it.
- "updated_at" of orders changes to the current time on every update; a trigger does that in SQLite.
Needs a look
- 1 value in "signed_up" of customers is a zero or impossible date such as 0000‑00‑00, which SQLite can't store, so it is NULL. If those dates mean something, replace them in the source with real ones before dumping.
- 1 value in "shipped_at" of orders is a zero or impossible date such as 0000‑00‑00 00:00:00, which SQLite can't store, so it is NULL. If those dates mean something, replace them in the source with real ones before dumping.
Your file is converted on this computer and never uploaded.
Other samples:
What changes from MySQL to SQLite
Each row is a rule the converter applies, with the report line it writes when the rule changes something in your data.
TINYINT(1), BOOL
INTEGER with the values as they are
SQLite has no boolean type; true and false are the integers 1 and 0, so every value keeps its meaning.
UNSIGNED integers
INTEGER with a CHECK that it isn't negative; BIGINT UNSIGNED holding values above 2^63-1 is TEXT
SQLite integers are 64-bit and signed. The check keeps negative values out as MySQL did; values past the signed range are kept as text so no digit is lost.
Report line: unsigned_widened
ZEROFILL and display widths
dropped
Zero padding is a way of showing a number, not part of it. The numbers are kept; format them when you read them if the padding matters.
Report line: zerofill_dropped
FLOAT(M,D)
REAL, without the scale
SQLite floats have no fixed scale. The stored values are copied as they are.
Report line: float_scale_dropped
DECIMAL(p,s)
DECIMAL(p,s) up to 15 digits; TEXT beyond
SQLite keeps about 15 significant digits in a number. Wider decimals are kept as text so every digit stays exact.
Report line: decimal_as_text
BIT(1), BIT(n)
INTEGER with the bits' value; BIT(64) as TEXT digits
A bit field is stored as the number its bits make. The top bit of a BIT(64) doesn't fit a signed integer, so its number is kept as text.
zero, partial or impossible dates (0000-00-00, 2024-02-30)
NULL
SQLite's date functions only read real calendar dates. They become NULL and the report counts them, as for every other database.
Report line: invalid_date_null
NOT NULL columns with values that become NULL
NOT NULL dropped
A column whose values had to become NULL can't keep NOT NULL, or the load would stop.
Report line: not_null_relaxed
DEFAULT '0000-00-00'
no default
A default that isn't a real date is left out rather than replaced with a made-up one.
Report line: zero_date_default
TIME
TIME text as MySQL writes it, durations included
SQLite keeps times as text, so durations past a day or below zero are kept exactly.
YEAR
INTEGER with a CHECK of its range
SQLite has no year type; the check keeps the same values MySQL allows.
CHAR(n), VARCHAR(n)
VARCHAR(n), not enforced
SQLite keeps the declared type for tools to read but doesn't limit the length; the values are the ones MySQL returns.
DATETIME
DATETIME as ISO 8601 text
SQLite stores dates as text its date functions read.
TIMESTAMP
TIMESTAMP as ISO 8601 text in UTC; the zone the dump states, UTC when it states none
A TIMESTAMP is a moment, written in UTC. Dumps state the zone they wrote it in (mysqldump writes UTC); without that, the values are read as UTC and the report says so.
Report line: timestamp_zone_assumed
ENUM
TEXT with a CHECK of its values
Text with a check keeps the same values.
ENUM holding '' (MySQL's error value)
the empty value allowed by the check
MySQL stores an empty string when an invalid value goes into an ENUM in a lenient SQL mode. Those rows are kept and the check allows them.
Report line: enum_invalid_value
ENUM holding values outside its list
no CHECK
Values the list doesn't name would fail the check, so the check isn't created.
Report line: enum_check_skipped
SET
TEXT as MySQL writes it ('a,b')
SQLite has no arrays; the text is the one MySQL returns.
JSON
TEXT
SQLite's JSON functions read JSON from text. A column declared JSON would turn "123" into a number, so it is TEXT.
NUL bytes in text
kept
SQLite text can hold a NUL byte, so the value is copied as it is.
text that isn't valid in its character set
the bad bytes as the replacement character
SQLite text is UTF-8; its functions misread bytes that aren't.
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
AUTO_INCREMENT
INTEGER PRIMARY KEY AUTOINCREMENT, with the counter set past the copied ids
SQLite counts a whole integer primary key. AUTOINCREMENT keeps ids from being reused, as MySQL does, and the counter continues after the highest id or the dump's AUTO_INCREMENT, whichever is higher.
AUTO_INCREMENT on part of a key or outside one
INTEGER without a counter
SQLite only counts a column that is the whole primary key, so the column keeps its values and new rows need theirs.
Report line: identity_without_counter
ON UPDATE CURRENT_TIMESTAMP
an AFTER UPDATE trigger
The trigger sets the column to the current time when a row changes and the update didn't set it, as MySQL does.
Report line: on_update_trigger
generated columns
GENERATED ALWAYS AS, VIRTUAL or STORED as in the source
SQLite 3.31 and later compute generated columns the same two ways.
generated columns with functions SQLite lacks
a plain column
A formula that can't be written for SQLite leaves a plain column and a report line.
Report line: generated_not_translated
INVISIBLE columns
regular columns
SQLite has no invisible columns.
Report line: invisible_column
CHECK constraints
translated, inline in CREATE TABLE
SQLite only takes checks in CREATE TABLE.
CHECK constraints with functions SQLite lacks
not created
A check that can't be written for SQLite is left out and reported.
Report line: check_skipped
CHECK ... NOT ENFORCED
not created
MySQL keeps these checks without applying them, so the rows may break them.
Report line: check_not_enforced
defaults with functions SQLite lacks
no default
A default that can't be written for SQLite is left out and reported.
Report line: default_not_translated
keys and indexes on a prefix, c(20)
an index on substr(c, 1, 20)
SQLite indexes expressions, so the same prefix is indexed and a unique prefix stays unique.
Report line: prefix_unique
FULLTEXT indexes
not created
SQLite searches text with separate FTS5 tables, which need their own queries.
Report line: fulltext_not_created
SPATIAL indexes
not created
SQLite has no spatial index on a column.
Report line: spatial_index_not_created
functional indexes SQLite can't write
not created
An index on an expression that can't be written for SQLite is left out and reported.
Report line: index_skipped
foreign keys
in CREATE TABLE, loaded unchecked, then listed by PRAGMA foreign_key_check
SQLite takes foreign keys only in CREATE TABLE and checks them only with PRAGMA foreign_keys on. The rows load unchecked, as from MySQL, and the script ends listing any row without a parent.
foreign keys to columns without a unique key
not created
SQLite needs a unique key on the referenced columns; InnoDB doesn't.
Report line: foreign_key_skipped
table, index and column names
as written; renamed when they differ only in case or an index name repeats
SQLite names ignore case, and index names share one namespace per database.
Report line: identifier_renamed
GEOMETRY, POINT, POLYGON...
WKB in a BLOB
The standard well-known binary keeps every geometry exactly.
Report line: geometry_as_blob
INET4, INET6, UUID (MariaDB)
TEXT
SQLite has no such types; the text is the one MariaDB returns.
BINARY, VARBINARY, BLOB
BLOB
Bytes stay bytes.
PARTITION BY
one table
The rows are the same.
Report line: partitions_dropped
WITH SYSTEM VERSIONING (MariaDB)
a table with the current rows
A dump holds the current rows only.
Report line: system_versioning_dropped
MyISAM, MEMORY and other engines
a regular table
SQLite has one table engine.
Report line: engine_dropped
dumps of several databases
one file, each table named after its database first
A SQLite file is one database, so the database name keeps tables with the same name apart.
Report line: databases_merged
views, triggers, procedures, functions and events
listed in the report
Their code is written in MySQL's language, so they are listed for you to recreate.
Report line: not_converted_objects
dumps without CREATE TABLE
types read from the values
A dump made with --no-create-info only has rows, so the types come from the values.
Report line: no_schema
geometries in a dump without CREATE TABLE
WKB in a BLOB
MySQL's geometry format is recognised from the values and stored as standard WKB.
Report line: geometry_as_blob
_ci collations
exact comparison
SQLite compares text exactly unless told otherwise, and its NOCASE only folds ASCII letters.
Report line: case_insensitive_collation
SET NAMES utf8 from utf8mb4 tables
converted, with a warning
MySQL 5.x mysqldump writes 3-byte utf8 by default and replaces every emoji with a question mark before any converter sees the file.
Report line: dump_lost_four_byte_chars
dumps without SET NAMES whose text isn't UTF-8
read as Latin-1 (Windows-1252)
MySQL's latin1 is Windows-1252, the usual encoding of such dumps.
Report line: charset_guessed
sjis, cp932, gbk, big5 dumps
read as UTF-8, with a warning
These encodings are not decoded yet.
Report line: unsupported_charset
UNHEX('..'), CONVERT(b'..', UNSIGNED), CONVERT(".." USING x), ST_GeomFromText('..') in rows
evaluated
Some dump tools write values as these calls; they are read as the values they produce, with geographic WKT turned to longitude first.
other expressions in rows
NULL
A converter that doesn't run a database can't evaluate them.
Report line: expression_value
DEFAULT in VALUES
the column's default value
A literal default is copied into the row.
Report line: default_keyword_filled
DEFAULT in VALUES without a literal default
NULL
There is no value to copy.
Report line: default_keyword_null
REPLACE and INSERT IGNORE
plain rows
A dump has one row per key, so the rows are the same.
Report line: replace_as_insert
types IntoSQL doesn't know
TEXT
Text holds any value; change the type after loading.
Report line: unknown_type
values that don't fit their column
NULL
A damaged or hand-written dump can hold values its column can't; they become NULL so the rest loads.
Report line: invalid_value_null
unreadable geometries
NULL
Bytes that aren't a geometry can't be stored as one.
Report line: bad_geometry
CREATE SEQUENCE (MariaDB)
not created
SQLite has no sequences.
Report line: sequence_not_converted
VECTOR(n) (MariaDB 11.7+)
TEXT with the floats as a JSON array
SQLite has no vector type; a JSON array keeps every float.
Report line: type_replaced
| In MySQL | In SQLite | Report line |
|---|---|---|
| TINYINT(1), BOOL | INTEGER with the values as they areSQLite has no boolean type; true and false are the integers 1 and 0, so every value keeps its meaning. | |
| UNSIGNED integers | INTEGER with a CHECK that it isn't negative; BIGINT UNSIGNED holding values above 2^63-1 is TEXTSQLite integers are 64-bit and signed. The check keeps negative values out as MySQL did; values past the signed range are kept as text so no digit is lost. | unsigned_widened |
| ZEROFILL and display widths | droppedZero padding is a way of showing a number, not part of it. The numbers are kept; format them when you read them if the padding matters. | zerofill_dropped |
| FLOAT(M,D) | REAL, without the scaleSQLite floats have no fixed scale. The stored values are copied as they are. | float_scale_dropped |
| DECIMAL(p,s) | DECIMAL(p,s) up to 15 digits; TEXT beyondSQLite keeps about 15 significant digits in a number. Wider decimals are kept as text so every digit stays exact. | decimal_as_text |
| BIT(1), BIT(n) | INTEGER with the bits' value; BIT(64) as TEXT digitsA bit field is stored as the number its bits make. The top bit of a BIT(64) doesn't fit a signed integer, so its number is kept as text. | |
| zero, partial or impossible dates (0000-00-00, 2024-02-30) | NULLSQLite's date functions only read real calendar dates. They become NULL and the report counts them, as for every other database. | invalid_date_null |
| NOT NULL columns with values that become NULL | NOT NULL droppedA column whose values had to become NULL can't keep NOT NULL, or the load would stop. | not_null_relaxed |
| DEFAULT '0000-00-00' | no defaultA default that isn't a real date is left out rather than replaced with a made-up one. | zero_date_default |
| TIME | TIME text as MySQL writes it, durations includedSQLite keeps times as text, so durations past a day or below zero are kept exactly. | |
| YEAR | INTEGER with a CHECK of its rangeSQLite has no year type; the check keeps the same values MySQL allows. | |
| CHAR(n), VARCHAR(n) | VARCHAR(n), not enforcedSQLite keeps the declared type for tools to read but doesn't limit the length; the values are the ones MySQL returns. | |
| DATETIME | DATETIME as ISO 8601 textSQLite stores dates as text its date functions read. | |
| TIMESTAMP | TIMESTAMP as ISO 8601 text in UTC; the zone the dump states, UTC when it states noneA TIMESTAMP is a moment, written in UTC. Dumps state the zone they wrote it in (mysqldump writes UTC); without that, the values are read as UTC and the report says so. | timestamp_zone_assumed |
| ENUM | TEXT with a CHECK of its valuesText with a check keeps the same values. | |
| ENUM holding '' (MySQL's error value) | the empty value allowed by the checkMySQL stores an empty string when an invalid value goes into an ENUM in a lenient SQL mode. Those rows are kept and the check allows them. | enum_invalid_value |
| ENUM holding values outside its list | no CHECKValues the list doesn't name would fail the check, so the check isn't created. | enum_check_skipped |
| SET | TEXT as MySQL writes it ('a,b')SQLite has no arrays; the text is the one MySQL returns. | |
| JSON | TEXTSQLite's JSON functions read JSON from text. A column declared JSON would turn "123" into a number, so it is TEXT. | |
| NUL bytes in text | keptSQLite text can hold a NUL byte, so the value is copied as it is. | |
| text that isn't valid in its character set | the bad bytes as the replacement characterSQLite text is UTF-8; its functions misread bytes that aren't. | 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 |
| AUTO_INCREMENT | INTEGER PRIMARY KEY AUTOINCREMENT, with the counter set past the copied idsSQLite counts a whole integer primary key. AUTOINCREMENT keeps ids from being reused, as MySQL does, and the counter continues after the highest id or the dump's AUTO_INCREMENT, whichever is higher. | |
| AUTO_INCREMENT on part of a key or outside one | INTEGER without a counterSQLite only counts a column that is the whole primary key, so the column keeps its values and new rows need theirs. | identity_without_counter |
| ON UPDATE CURRENT_TIMESTAMP | an AFTER UPDATE triggerThe trigger sets the column to the current time when a row changes and the update didn't set it, as MySQL does. | on_update_trigger |
| generated columns | GENERATED ALWAYS AS, VIRTUAL or STORED as in the sourceSQLite 3.31 and later compute generated columns the same two ways. | |
| generated columns with functions SQLite lacks | a plain columnA formula that can't be written for SQLite leaves a plain column and a report line. | generated_not_translated |
| INVISIBLE columns | regular columnsSQLite has no invisible columns. | invisible_column |
| CHECK constraints | translated, inline in CREATE TABLESQLite only takes checks in CREATE TABLE. | |
| CHECK constraints with functions SQLite lacks | not createdA check that can't be written for SQLite is left out and reported. | check_skipped |
| CHECK ... NOT ENFORCED | not createdMySQL keeps these checks without applying them, so the rows may break them. | check_not_enforced |
| defaults with functions SQLite lacks | no defaultA default that can't be written for SQLite is left out and reported. | default_not_translated |
| keys and indexes on a prefix, c(20) | an index on substr(c, 1, 20)SQLite indexes expressions, so the same prefix is indexed and a unique prefix stays unique. | prefix_unique |
| FULLTEXT indexes | not createdSQLite searches text with separate FTS5 tables, which need their own queries. | fulltext_not_created |
| SPATIAL indexes | not createdSQLite has no spatial index on a column. | spatial_index_not_created |
| functional indexes SQLite can't write | not createdAn index on an expression that can't be written for SQLite is left out and reported. | index_skipped |
| foreign keys | in CREATE TABLE, loaded unchecked, then listed by PRAGMA foreign_key_checkSQLite takes foreign keys only in CREATE TABLE and checks them only with PRAGMA foreign_keys on. The rows load unchecked, as from MySQL, and the script ends listing any row without a parent. | |
| foreign keys to columns without a unique key | not createdSQLite needs a unique key on the referenced columns; InnoDB doesn't. | foreign_key_skipped |
| table, index and column names | as written; renamed when they differ only in case or an index name repeatsSQLite names ignore case, and index names share one namespace per database. | identifier_renamed |
| GEOMETRY, POINT, POLYGON... | WKB in a BLOBThe standard well-known binary keeps every geometry exactly. | geometry_as_blob |
| INET4, INET6, UUID (MariaDB) | TEXTSQLite has no such types; the text is the one MariaDB returns. | |
| BINARY, VARBINARY, BLOB | BLOBBytes stay bytes. | |
| PARTITION BY | one tableThe rows are the same. | partitions_dropped |
| WITH SYSTEM VERSIONING (MariaDB) | a table with the current rowsA dump holds the current rows only. | system_versioning_dropped |
| MyISAM, MEMORY and other engines | a regular tableSQLite has one table engine. | engine_dropped |
| dumps of several databases | one file, each table named after its database firstA SQLite file is one database, so the database name keeps tables with the same name apart. | databases_merged |
| views, triggers, procedures, functions and events | listed in the reportTheir code is written in MySQL's language, so they are listed for you to recreate. | not_converted_objects |
| dumps without CREATE TABLE | types read from the valuesA dump made with --no-create-info only has rows, so the types come from the values. | no_schema |
| geometries in a dump without CREATE TABLE | WKB in a BLOBMySQL's geometry format is recognised from the values and stored as standard WKB. | geometry_as_blob |
| _ci collations | exact comparisonSQLite compares text exactly unless told otherwise, and its NOCASE only folds ASCII letters. | case_insensitive_collation |
| SET NAMES utf8 from utf8mb4 tables | converted, with a warningMySQL 5.x mysqldump writes 3-byte utf8 by default and replaces every emoji with a question mark before any converter sees the file. | dump_lost_four_byte_chars |
| dumps without SET NAMES whose text isn't UTF-8 | read as Latin-1 (Windows-1252)MySQL's latin1 is Windows-1252, the usual encoding of such dumps. | charset_guessed |
| sjis, cp932, gbk, big5 dumps | read as UTF-8, with a warningThese encodings are not decoded yet. | unsupported_charset |
| UNHEX('..'), CONVERT(b'..', UNSIGNED), CONVERT(".." USING x), ST_GeomFromText('..') in rows | evaluatedSome dump tools write values as these calls; they are read as the values they produce, with geographic WKT turned to longitude first. | |
| other expressions in rows | NULLA converter that doesn't run a database can't evaluate them. | expression_value |
| DEFAULT in VALUES | the column's default valueA literal default is copied into the row. | default_keyword_filled |
| DEFAULT in VALUES without a literal default | NULLThere is no value to copy. | default_keyword_null |
| REPLACE and INSERT IGNORE | plain rowsA dump has one row per key, so the rows are the same. | replace_as_insert |
| types IntoSQL doesn't know | TEXTText holds any value; change the type after loading. | unknown_type |
| values that don't fit their column | NULLA damaged or hand-written dump can hold values its column can't; they become NULL so the rest loads. | invalid_value_null |
| unreadable geometries | NULLBytes that aren't a geometry can't be stored as one. | bad_geometry |
| CREATE SEQUENCE (MariaDB) | not createdSQLite has no sequences. | sequence_not_converted |
| VECTOR(n) (MariaDB 11.7+) | TEXT with the floats as a JSON arraySQLite has no vector type; a JSON array keeps every float. | type_replaced |
Load the script into SQLite
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.
sqlite3 -bail mydb.sqlite < mysql.sqlite.sqlForeign keys are declared in each table and the rows load with foreign key checks off. The script then lists any row that points to a row that doesn't exist, instead of failing half way.
For a large dump, load into a new file on a local disk. The script loads everything in one transaction with syncing off, so it runs at disk speed, and a load that stops half way leaves no partial tables.
The command line loads the file straight into a SQLite database, without writing a script. It's free.
Dump MySQL for the best result
The converter reads what the dump holds. These options keep more of your data in it:
- --default-character-set=utf8mb4 keeps emoji and other 4-byte characters, which a utf8 dump loses before the converter sees it.
- --hex-blob writes binary columns as hexadecimal, which survives copying between systems.
- --routines --triggers --events add those objects to the dump, so the report can list them.
What isn't converted
Views, triggers, stored procedures, functions and events are written in each database's own language. They are listed in the report by name, never dropped silently, so you can rewrite them for SQLite.
Questions about MySQL to SQLite
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.
Does it read MariaDB dumps?
Yes. Dumps from mariadb-dump and from MariaDB 10.3 to 12 convert the same way, including MariaDB's INET6, INET4 and UUID columns.
What happens to 0000-00-00 dates?
They become NULL: SQLite would store the text, but nothing reads 0000-00-00 as a date. The report says how many there were in each column.
Are AUTO_INCREMENT values kept?
Yes. An AUTO_INCREMENT column that is the table's whole primary key becomes INTEGER PRIMARY KEY AUTOINCREMENT, every row keeps its id, and the next id continues after the highest one. Any other AUTO_INCREMENT column keeps its values without a counter, and the report says so.
Does TINYINT(1) become boolean?
No. SQLite has no boolean type, so TINYINT(1) is an INTEGER and every value stays as it is.
What about stored procedures and triggers?
They are listed in the report by name and not converted, because each database writes them in its own language. ON UPDATE CURRENT_TIMESTAMP is the exception: it becomes a SQLite trigger.
Which SQLite versions does the script load on?
SQLite 3.31 and later. Every test dump loads on 3.31 and 3.54, stopping at the first error if anything goes wrong.