Skip to content
IntoSQL home
Convert a file

Data types

MySQL to SQLite data types

Every MySQL data type that SQLite stores differently, what the converter writes for it, when, and the report line.

View as Markdown

Each row is a type from MySQL that SQLite stores differently: what the SQLite script gets, why, and the line the report writes about it. The rows come from the converter's own rules, so this page matches what it does.

Convert in the browser: MySQL to SQLite opens the converter set up for this pair. The MySQL to SQLite migration guide covers dumping, loading and the rules for keys, indexes and names.

A report line is a fix when the converter changed something and lists it, or a problem when a value or object is worth a look.

Numbers

In MySQL In SQLite Why Report line
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. unsigned_widened (fix)
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. zerofill_dropped (fix)
FLOAT(M,D) REAL, without the scale SQLite floats have no fixed scale. The stored values are copied as they are. float_scale_dropped (fix)
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. decimal_as_text (fix)

Booleans and bits

In MySQL In SQLite Why Report line
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. None
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. None

Text

In MySQL In SQLite Why Report line
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. None
NUL bytes in text kept SQLite text can hold a NUL byte, so the value is copied as it is. None
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. invalid_utf8_replaced (problem)

Enums and sets

In MySQL In SQLite Why Report line
ENUM TEXT with a CHECK of its values Text with a check keeps the same values. None
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. enum_invalid_value (fix)
ENUM holding values outside its list no CHECK Values the list doesn't name would fail the check, so the check isn't created. enum_check_skipped (problem)
SET TEXT as MySQL writes it ('a,b') SQLite has no arrays; the text is the one MySQL returns. None

Dates and times

In MySQL In SQLite Why Report line
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. invalid_date_null (problem)
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. zero_date_default (fix)
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. None
YEAR INTEGER with a CHECK of its range SQLite has no year type; the check keeps the same values MySQL allows. None
DATETIME DATETIME as ISO 8601 text SQLite stores dates as text its date functions read. None
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. timestamp_zone_assumed (problem)

Binary

In MySQL In SQLite Why Report line
BINARY, VARBINARY, BLOB BLOB Bytes stay bytes. None

JSON, spatial and other types

In MySQL In SQLite Why Report line
JSON TEXT SQLite's JSON functions read JSON from text. A column declared JSON would turn "123" into a number, so it is TEXT. None
GEOMETRY, POINT, POLYGON... WKB in a BLOB The standard well-known binary keeps every geometry exactly. geometry_as_blob (fix)
INET4, INET6, UUID (MariaDB) TEXT SQLite has no such types; the text is the one MariaDB returns. None
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. geometry_as_blob (fix)
types IntoSQL doesn't know TEXT Text holds any value; change the type after loading. unknown_type (problem)
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. invalid_value_null (problem)
unreadable geometries NULL Bytes that aren't a geometry can't be stored as one. bad_geometry (problem)
VECTOR(n) (MariaDB 11.7+) TEXT with the floats as a JSON array SQLite has no vector type; a JSON array keeps every float. type_replaced (fix)

Counters and identities

In MySQL In SQLite Why Report line
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. None
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. identity_without_counter (problem)

Other pairs