MySQL Data Types: Numeric, String, Date, JSON, and Binary
MySQL column types determine the values a column can hold, its storage requirements, and how values are compared. Start with the kind of data you need to represent, then check its range, precision, length, and indexing requirements.
Choose a MySQL data type
| Data to store | Common choices | Key difference |
|---|---|---|
| Whole numbers | TINYINT, INT, BIGINT | Different integer ranges and storage sizes; UNSIGNED changes the nonnegative range. |
| Exact decimal values | DECIMAL | Fixed decimal precision and scale, useful for currency. |
| Approximate measurements | FLOAT, DOUBLE | Binary floating-point values are approximate, not exact decimals. |
| Short or variable-length text | CHAR, VARCHAR | Fixed-width versus variable-length strings; character set and row-size limits matter. |
| Long text or binary data | TEXT, BLOB | TEXT uses a character set; BLOB stores bytes. |
| Dates and times | DATE, DATETIME, TIMESTAMP | DATE has no time component; TIMESTAMP applies session time-zone conversion, unlike DATETIME. |
| Structured documents | JSON | Validates JSON and stores a binary representation; index frequently queried paths deliberately. |
| A predefined set of labels | ENUM, SET | ENUM selects one declared member; SET permits a combination of members. |
Common decisions
INT or BIGINT? Choose the smallest integer type whose signed or unsigned range covers realistic future values. A MySQL BOOLEAN declaration is an alias for TINYINT(1), not a two-value constraint; see BOOLEAN.
DECIMAL or FLOAT? Use DECIMAL(p,s) for exact base-10 quantities such as prices. Use FLOAT or DOUBLE only when rounding error is acceptable.
VARCHAR or TEXT? Use VARCHAR(n) when you can define a meaningful maximum character length. Use the TEXT family for longer content; limits are measured in bytes, so character encoding matters.
DATETIME or TIMESTAMP? Use DATETIME for a date and clock time that should not change when the session time zone changes. TIMESTAMP converts between the session time zone and UTC and has a narrower supported range.
For type ranges, version-specific rules, and storage details, consult the official MySQL data types reference and storage requirements. The individual guides above include examples and constraints.
-
BIGINT
BIGINTis a commonly used data type in MySQL for storing large integer values. -
BINARY
BINARYis a commonly used data type in MySQL for storing binary data. -
BIT
BITis a common data type used in MySQL for storing bit data. -
BLOB
Compare MySQL TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB byte limits, and learn why packet settings can restrict transfers. -
BOOLEAN
MySQL BOOLEAN and BOOL are TINYINT(1) aliases, not 0/1 restrictions. Learn TRUE/FALSE storage, truth tests, and how to enforce two values. -
CHAR
Learn MySQL CHAR length limits, space padding and retrieval behavior, and when a fixed-length string fits better than VARCHAR. -
DATE
In MySQL,DATEis a date data type used for storing year, month, and day values. -
DATETIME
Learn MySQL DATETIME range, 5–8 byte storage, fractional-second precision, time-zone behavior, and safe date-range queries with SQL examples. -
DECIMAL
Learn MySQL DECIMAL precision and scale, the range of DECIMAL(10,2), exact-value storage, and when to use DECIMAL instead of FLOAT. -
DOUBLE
Learn MySQL DOUBLE storage, the DOUBLE PRECISION synonym, floating-point comparison limits, and when DECIMAL is a better choice. -
ENUM
TheENUMdata type is a special data type in MySQL used to represent a set of predefined values. -
FLOAT
Learn MySQL FLOAT storage, FLOAT(p) behavior, deprecated M,D syntax, and why approximate values should not represent exact prices. -
GEOMETRY
GEOMETRYis one of the data types in MySQL used for storing spatial data. -
INT
The MySQLINTdata type can store integer values ranging from -2147483648 to 2147483647. -
INTEGER
MySQLINTEGERis a commonly used data type for storing integer data. -
JSON
MySQL’s native JSON type validates documents and stores them in an optimized binary format. Learn its defaults, indexing options, and practical use. -
LONGTEXT
Learn MySQL LONGTEXT’s 4 GiB byte limit, character capacity, DEFAULT expression rules in MySQL 8.0.13+, and packet constraints. -
MEDIUMINT
MEDIUMINTis a type of integer in MySQL. It occupies 3 bytes of storage and can store signed integers ranging from -8388608 to 8388607. -
MEDIUMTEXT
Learn MySQL MEDIUMTEXT’s 16,777,215-byte limit, why the character count depends on charset, and how it compares with VARCHAR and TEXT. -
SET
TheSETdata type in MySQL is a type of data type used to store multiple values in a single field, allowing us to store up to 64 different values. -
SMALLINT
MySQLSMALLINTdata type is used for storing integer data that ranges from -32768 to 32767. -
TEXT
Learn MySQL TEXT’s 65,535-byte limit, character capacity, expression DEFAULT syntax in MySQL 8.0.13+, and prefix-index rules. -
TIME
MySQL TIME stores a time-of-day value or an elapsed duration, including negative values and durations longer than 24 hours. -
TIMESTAMP
TheTIMESTAMPdata type in MySQL is used to store dates and times. -
TINYINT
See signed and unsigned TINYINT ranges, storage size, the BOOLEAN alias, and why TINYINT(1) does not limit values to 0 and 1. -
TINYTEXT
Learn MySQL TINYTEXT’s 255-byte capacity, multibyte character limits, and DEFAULT expression syntax in MySQL 8.0.13+. -
VARBINARY
MySQLVARBINARYdata type is used to store binary data. -
VARCHAR
Learn the maximum MySQL VARCHAR length, how utf8mb4 affects bytes and storage, and why VARCHAR(255) is not the limit. -
YEAR
Learn the MySQL YEAR data type, its 1901–2155 range, zero and two-digit input rules, deprecated YEAR(4) syntax, and how it differs from YEAR(date).