Data types · MySQL | FA Dev Tools

MySQL

Compose a statement from a template, format one you already have, or look something up: every data type and what it costs, the built-in functions, everything a JSON column can do — including rewriting a condition for a column that has become one — and the SQL syntax itself. Everything is held in the browser and ends at the clipboard — no connection, no query, nothing sent anywhere.

Generate Format Data types Functions JSON Basics

40 of 40 data types

Data type	Storage	Range / max	What it is for
Integer7 typesWhole numbers. Pick the narrowest one the values will never outgrow.MySQL manual
	1 byte	−128 … 127 · unsigned 0 … 255	The smallest integer — a flag, a small enum, a percentage.
	2 bytes	−32,768 … 32,767 · unsigned 0 … 65,535	Two bytes — a year count, a port number, a small lookup id.
	3 bytes	−8,388,608 … 8,388,607 · unsigned 0 … 16,777,215	Three bytes — MySQL's own, and worth it on a very wide table you never migrate.
	4 bytes	−2,147,483,648 … 2,147,483,647 · unsigned 0 … 4,294,967,295	The default integer — row ids, counts, anything inside ten digits.
	8 bytes	±9.22 × 10¹⁸ · unsigned 0 … 18,446,744,073,709,551,615	For ids and counters that outgrow INT, and for millisecond epochs.
	(M + 7) / 8 bytes	M is 1 … 64 bits	A bit field — a set of flags packed into as few bytes as they fit.
	8 bytes	unsigned 0 … 18,446,744,073,709,551,615	One word that expands to a whole surrogate-key definition.
Decimal & float3 typesFractions — exact for money, approximate for measurements.MySQL manual
	≈ 4 bytes per 9 digits	M ≤ 65 total digits, D ≤ 30 after the point	Exact fixed-point arithmetic — the only type to hold money in.
	4 bytes	≈ 7 significant decimal digits	Single-precision binary floating point — for measurements, never for money.
	8 bytes	≈ 15 significant decimal digits	Double-precision floating point — latitude and longitude, sensor readings, scores.
Boolean1 typeTrue and false, which MySQL spells TINYINT(1).MySQL manual
	1 byte	TRUE is 1, FALSE is 0 — but any TINYINT value fits	Not a real type — a synonym for TINYINT(1) that MySQL rewrites on the spot.
String9 typesText, and how it is encoded and compared.MySQL manual
	M × charset max bytes against the row limit	M is 0 … 255 characters	Fixed width — worth it only when every value really is the same length.
	data + 1 byte (2 if M > 255)	M ≤ 65,535, and the whole row must fit in 65,535 bytes	The default text type — stores what you put in, plus a length prefix.
	data + 1 byte	255 bytes	The smallest TEXT — almost always the wrong choice next to VARCHAR(255).
	data + 2 bytes	65,535 bytes (64 KB)	Long text held outside the row once it outgrows it — bodies, notes, dumps.
	data + 3 bytes	16,777,215 bytes (16 MB)	16 MB of text — a rendered document, a log, a scraped page.
	data + 4 bytes	4,294,967,295 bytes (4 GB)	The largest text column. In practice the ceiling is your packet size, not the type.
	1 byte (2 if more than 255 members)	up to 65,535 members	A closed list of strings, stored as the member's position rather than its text.
	1, 2, 3, 4 or 8 bytes, by member count	up to 64 members	Several members of a closed list in one column — a bitmask wearing strings.
	decides how many bytes a character costs	utf8mb4 is up to 4 bytes per character	Which characters a string column can hold, and how two of them compare.
Binary3 typesRaw bytes with no character set — hashes, UUIDs, files.MySQL manual
	M bytes, always	M is 0 … 255 bytes	Fixed-length raw bytes — a hash, a UUID, a key. No charset, no collation.
	data + 1 byte (2 if M > 255)	same 65,535-byte row limit as VARCHAR	Variable-length raw bytes — VARCHAR without a character set.
	data + 1 / 2 / 3 / 4 bytes	255 B · 64 KB · 16 MB · 4 GB	The TEXT family with no character set — images, PDFs, anything opaque.
Date & time6 typesDays, clock times, instants, and how much of a second they keep.MySQL manual
	3 bytes	1000-01-01 … 9999-12-31	A calendar day, with no time and no zone.
	3 bytes + fsp	−838:59:59 … 838:59:59	A duration as much as a time of day — it runs past 24 hours, and goes negative.
	5 bytes + fsp	1000-01-01 00:00:00 … 9999-12-31 23:59:59	A wall-clock timestamp, stored and returned exactly as written.
	4 bytes + fsp	1970-01-01 00:00:01 UTC … 2038-01-19 03:14:07 UTC	An instant — stored as UTC, shown in the session's time zone.
	1 byte	1901 … 2155, plus 0000	A year on its own. Rarely worth the byte it saves over SMALLINT.
	0 bytes (fsp 0) · 1 (1–2) · 2 (3–4) · 3 (5–6)	fsp is 0 … 6 digits	How much of a second the column keeps. Zero by default, which is a lossy default.
JSON1 typeA validated document in a column.MySQL manual
	like LONGBLOB — data + 4 bytes	4 GB by the type, `max_allowed_packet` in practice	A parsed document stored in a binary form, so a key is reachable without decoding all of it.
Spatial6 typesPoints, lines and areas, with an SRID.MySQL manual
	4 bytes SRID + WKB	any of the geometry types below	The base spatial type — a column that will take a point, a line or a polygon.
	25 bytes	one coordinate pair	A single location — the spatial type you actually use.
	variable	an ordered run of points	A path — a route, a road, a boundary segment.
	variable	a closed ring, plus any holes	An area — a suburb, a delivery zone, a catchment.
	variable	many of one geometry type	Several geometries of one kind held as a single value.
	variable	many geometries of mixed types	A bag of geometries of different kinds in one value.
Attributes4 typesNot types — the modifiers you write next to one.MySQL manual
	no change	doubles the positive range, removes the negative	Spends the sign bit on range instead — for anything that cannot be negative.
	no change	one per table, and it must be indexed	The server picks the next value — the standard surrogate key.
	a nullable column costs 1 bit in the row's null bitmap	NULL means 'unknown', not 'empty'	Whether the column may hold no value at all, and what it holds when unsaid.
	VIRTUAL costs nothing; STORED is written like a real column	the expression must be deterministic	A column computed from the others — the usual way to index inside a JSON document.

Click a data type for its syntax, an example and the gotchas · sizes are MySQL 8.0 on InnoDB · every link goes to the official MySQL 8.0 reference manual.

Instructions

A MySQL crib sheet with a query builder attached — for the statement you write once a month and have to look up every time.

Generate — pick a template on the left, fill in the table and field, and the statement builds as you type. A box you have not filled in yet shows as <table>, so the shape stays readable. Copy SQL takes it away.
Format — paste a statement, or several separated by ;, and it comes back laid out, one clause per line. The options above the box choose the layout: indent by 2, 3, 4 or 8 spaces or by a tab; UPPER, lower or as-typed keywords, functions and data types (keywords UPPER, the other two as typed, by default); standard or tabular layout; AND / OR at the start or end of the line; how long a ( … ) may get before it is broken up; blank lines between statements; and a=1 vs a = 1.
Data types — every column type in one table, grouped into integer, decimal, boolean, string, binary, date & time, JSON, spatial and the attributes you write beside them. Read down the Storage and Range columns to choose between them; click a row for the syntax, an example and the gotchas, or the ↗ beside a name for that type's page in the MySQL manual.
Functions — search MySQL's built-ins by name or by what you want (epoch, latest per group). Click one for its signature, the type it returns and a runnable example.
JSON — two halves of the one subject. Reference is everything a JSON column can do: path syntax, -> and ->>, every JSON function, how to index inside a document, and the traps. Convert takes a WHERE fragment written when the column was an ordinary one and gives back the version a JSON column needs: `Meta`.URLSegment = '…' becomes `Meta`->>'$.URLSegment' = '…', and Meta = '{…}' becomes a CAST(… AS JSON). Name the column, or leave the box empty and it is guessed from what you pasted.
JSON → Filter builds the lines from scratch: name the JSON column (and the table, if the query joins), then add a row per condition — the key path (URLSegment, a.b, items[0].name), its data type, and the check. A string can be =, contains (like '%…%'), starts / ends with, your own like pattern, in, empty or null; a number takes =, >=, between and in; a boolean is true or false. Rows are joined with AND, one per line.
Convert understands four checks beyond the comparisons: IS NULL, IS NOT NULL, and — as shorthand it accepts on the way in — IS EMPTY and IS NOT EMPTY, meaning absent, JSON null, or the empty string. Each expands to the real test, which is longer than what you typed for a reason: a JSON null is not SQL NULL, so the obvious `Meta`->>'$.k' IS NULL reads a stored null as a value.
Basics — the SQL itself: group by, distinct, left join, explain. Each entry ends with the gotchas.

Tips

Nothing on this page runs a query. There is no connection and no server call — every button ends at your clipboard, so paste the statement into a client you trust and read it before you run it.
Format changes whitespace and letter case, nothing else — string literals, backticked names, comments and ->> come back exactly as typed. Functions and data types are matched by name, so re-casing them also re-cases a table called log or text, and on a case-sensitive server that is a different table — which is why both default to as typed. SQL it cannot parse — an unclosed bracket or quote — gets the line and column of the problem instead of a guess, and a script with DELIMITER lines is refused rather than broken.
update-safe lifts SQL_SAFE_UPDATES for one connection, which is why it is copy-only: it has to run in the same session as the UPDATE that needs it.
Values you type are quoted before they reach the statement — a table or column goes in backtick-quoted, and a search value is escaped for a '…' literal. A name that cannot be used says which rule it broke instead of being pasted in.
Convert changes the accesses and nothing else — the one exception being the four IS … checks, which have to replace their operator to be right. Your value, your line breaks and your casing all come back as you typed them, and anything inside a string literal or a -- comment is left alone, so a like '%…%' stays one, now on the key rather than on the whole document. What it cannot decide for you it says underneath: comparing to a number, and when a literal is not a document at all.
Each subject has one tab and one home, so nothing is stated twice: column types are on Data types, JSON is on JSON, and Basics is the syntax around them.
Every group heading and every data type links out to its page in the official MySQL 8.0 reference manual on dev.mysql.com. This tab is a way to choose between types, not the reference of record — when a number here matters, follow the ↗ and read the manual's wording.
Each tab has its own address (/database/mysql/functions), so you can bookmark or share one. The template, your boxes and every search are kept in this browser, so a reload comes back where you left it. Nothing is shared with anyone else.
Hit Example and click a highlighted tab — each one loads its own sample, already open; on Format it pastes a one-line query and lays it out with your current options. On a reference tab the search box is highlighted too, and inside JSON Reference, Convert and Filter each carry their own sample, so the JSON tab shows you whichever view you are on.