MySQL Data Types

Last updated: October 19, 2026Oct 19, 2026

Every column in a MySQL table has a data type. The type decides what values the column accepts, how much space they take, how they sort and compare, and which functions work on them. Choosing well makes your database smaller, faster and safer.

1. Data Type Families

Figure 1: MySQL's data types fall into five families.

Store numbers in numeric types, text in string types and dates in date types. Never keep dates or numbers as text.
Pick the smallest type that safely holds every value you expect.
MySQL runs in strict mode by default, so an invalid or out-of-range value is rejected with an error instead of being silently changed.

2. Integer Types

Type	Bytes	Signed range	Unsigned range	Typical use
`TINYINT`	1	-128 to 127	0 to 255	Ratings, flags, small counters
`SMALLINT`	2	-32,768 to 32,767	0 to 65,535	Year counts, small quantities
`MEDIUMINT`	3	-8,388,608 to 8,388,607	0 to 16,777,215	Medium IDs
`INT`	4	about ±2.1 billion	0 to about 4.29 billion	Most IDs and counts
`BIGINT`	8	about ±9.2 × 10¹⁸	0 to about 1.8 × 10¹⁹	Huge IDs, byte counts

Add UNSIGNED for values that are never negative. Out-of-range values are rejected:

integers.sql

CREATE TABLE reviews (
  review_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  stars     TINYINT UNSIGNED NOT NULL
);
INSERT INTO reviews (stars) VALUES (5), (4);
INSERT INTO reviews (stars) VALUES (300);   -- too big for TINYINT UNSIGNED

Output

Query OK, 0 rows affected
Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0
ERROR 1264 (22003): Out of range value for column 'stars' at row 1

The old display width, as in INT(11), is deprecated and does not limit the range. Just write INT.

3. DECIMAL vs FLOAT and DOUBLE

DECIMAL(p, s) stores exact numbers with p digits in total and s after the point. FLOAT and DOUBLE store approximate binary numbers: faster for science, but tiny rounding errors appear. Always use DECIMAL for money.

decimal.sql

CREATE TABLE amounts (exact DECIMAL(10,2), approx FLOAT, approx2 DOUBLE);
INSERT INTO amounts VALUES (0.10, 0.10, 0.10), (0.20, 0.20, 0.20);

SELECT SUM(exact) AS decimal_sum, SUM(approx) AS float_sum, SUM(approx2) AS double_sum FROM amounts;

Result: 1 row

decimal_sum	float_sum	double_sum
0.30	0.30000000447034836	0.30000000000000004

query.sql

SELECT SUM(exact) = 0.3 AS decimal_equals, SUM(approx2) = 0.3 AS double_equals FROM amounts;

Result: 1 row

decimal_equals	double_equals
1	0

The DOUBLE sum is not exactly 0.3, so the comparison fails. DECIMAL(10,2) holds up to 99,999,999.99; a value with more decimal places is rounded to 2.

4. String Types: CHAR, VARCHAR and TEXT

Type	Max length	Storage	Use for
`CHAR(n)`	255 characters	Always n characters (padded with spaces)	Fixed codes: country `'IN'`, PIN codes
`VARCHAR(n)`	65,535 bytes per row in total	Actual length + 1 or 2 bytes	Names, emails, titles: most text
`TINYTEXT` / `TEXT`	255 B / 64 KB	Stored off the row when large	Comments, descriptions
`MEDIUMTEXT` / `LONGTEXT`	16 MB / 4 GB	Off-row	Articles, logs
`BINARY`, `VARBINARY`, `BLOB`	Like the above, in bytes	Raw bytes, no character set	Hashes, small files

n counts characters, not bytes. A value that is too long is rejected:

strings.sql

CREATE TABLE users (
  country CHAR(2),
  name    VARCHAR(10),
  bio     TEXT
);
INSERT INTO users VALUES ('IN', 'Aditi', 'Loves databases');
INSERT INTO users VALUES ('IN', 'Venkataraghavan', NULL);

Output

Query OK, 0 rows affected
Query OK, 1 row affected
ERROR 1406 (22001): Data too long for column 'name' at row 1

query.sql

SELECT name, LENGTH(name) AS bytes, CHAR_LENGTH(name) AS characters FROM users;

Result: 1 row

name	bytes	characters
Aditi	5	5

Use the utf8mb4 character set (the default in MySQL 8+). It stores every Unicode character, including emoji; the old utf8/utf8mb3 cannot.

5. ENUM and SET

ENUM allows exactly one value from a fixed list; SET allows any combination of listed values.

enum.sql

CREATE TABLE tickets (
  id       INT PRIMARY KEY,
  priority ENUM('low', 'medium', 'high') NOT NULL DEFAULT 'medium',
  tags     SET('bug', 'ui', 'backend', 'urgent')
);
INSERT INTO tickets VALUES (1, 'high', 'bug,urgent'), (2, DEFAULT, 'ui');
INSERT INTO tickets VALUES (3, 'critical', NULL);

Output

Query OK, 0 rows affected
Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0
ERROR 1265 (01000): Data truncated for column 'priority' at row 1

query.sql

SELECT id, priority, tags FROM tickets ORDER BY priority;

Result: 2 rows

id	priority	tags
2	medium	ui
1	high	bug,urgent

ENUM sorts by its position in the list (low, medium, high), not alphabetically. Adding a new value later needs ALTER TABLE; if the list changes often, use a lookup table instead.

6. Date and Time Types

Type	Format	Range	Use for
`DATE`	`YYYY-MM-DD`	1000-01-01 to 9999-12-31	Birthdays, due dates
`TIME`	`HH:MM:SS`	-838:59:59 to 838:59:59	Opening hours, durations
`DATETIME`	`YYYY-MM-DD HH:MM:SS`	1000-01-01 to 9999-12-31	Appointments, events at a local time
`TIMESTAMP`	Same as DATETIME	1970-01-01 to 2038-01-19 (UTC)	Created/updated times; converted with the session time zone
`YEAR`	`YYYY`	1901 to 2155	Model years, batch years

The difference between DATETIME and TIMESTAMP shows when the session time zone changes. TIMESTAMP is stored in UTC and converted; DATETIME is stored as written:

datetime-vs-timestamp.sql

CREATE TABLE events (dt DATETIME, ts TIMESTAMP);
SET time_zone = '+05:30';
INSERT INTO events VALUES ('2026-03-01 10:00:00', '2026-03-01 10:00:00');

SET time_zone = '+00:00';
SELECT dt, ts FROM events;

Result: 1 row

dt	ts
2026-03-01 10:00:00	2026-03-01 04:30:00

Invalid dates are rejected in strict mode:

query.sql

INSERT INTO events (dt) VALUES ('2026-02-30 09:00:00');

Output

ERROR 1292 (22007): Incorrect datetime value: '2026-02-30 09:00:00' for column 'dt' at row 1

Add fractional seconds with DATETIME(3) (milliseconds) or DATETIME(6) (microseconds).

7. JSON

The JSON type checks that values are valid JSON and stores them in an efficient binary format.

json.sql

CREATE TABLE settings (user_id INT PRIMARY KEY, prefs JSON);
INSERT INTO settings VALUES (1, '{"theme": "dark", "fontSize": 16}');
INSERT INTO settings VALUES (2, '{theme: dark}');

Output

Query OK, 0 rows affected
Query OK, 1 row affected
ERROR 3140 (22032): Invalid JSON text: "Missing a name for object member." at position 1 in value for column 'settings.prefs'.

query.sql

SELECT user_id, prefs->>'$.theme' AS theme FROM settings;

Result: 1 row

user_id	theme
1	dark

8. BOOLEAN and BIT

MySQL has no separate boolean storage: BOOLEAN (or BOOL) is a synonym for TINYINT(1). TRUE is 1 and FALSE is 0. BIT(n) stores n bits, which is useful for compact flag sets.

boolean.sql

CREATE TABLE features (name VARCHAR(20), enabled BOOLEAN);
INSERT INTO features VALUES ('dark_mode', TRUE), ('beta_search', FALSE);
SELECT name, enabled, enabled = TRUE AS is_on FROM features;

Result: 2 rows

name	enabled	is_on
dark_mode	1	1
beta_search	0	0

9. How to Choose the Right Type

Data	Recommended type	Avoid
Primary key / ID	`INT UNSIGNED` or `BIGINT UNSIGNED` with AUTO_INCREMENT	`VARCHAR` IDs unless they come from outside
Money	`DECIMAL(12,2)`	`FLOAT`, `DOUBLE`
Person name, email	`VARCHAR(100)`, `VARCHAR(255)`	`CHAR(255)`, `TEXT`
Phone number, PIN code	`VARCHAR(15)` / `CHAR(6)` (leading zeros matter)	`INT`
Yes / no	`BOOLEAN`	`VARCHAR(3)` with 'yes'/'no'
Date of birth	`DATE`	`VARCHAR`, `DATETIME`
Created at / updated at	`TIMESTAMP` or `DATETIME`	Storing epoch numbers as text
Long article	`TEXT` / `MEDIUMTEXT`	`VARCHAR(65000)`

10. Checking a Table's Column Types

query.sql

SELECT column_name, column_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'typesdemo' AND table_name = 'tickets'
ORDER BY ordinal_position;

Result: 3 rows

COLUMN_NAME	COLUMN_TYPE	IS_NULLABLE
id	int	NO
priority	enum('low','medium','high')	NO
tags	set('bug','ui','backend','urgent')	YES

11. Practice Exercises

Exercise 1: Create a table products with an auto-increment ID, a name up to 80 characters, an exact price and a launch date.

solution.sql

CREATE TABLE products (
  id     INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name   VARCHAR(80) NOT NULL,
  price  DECIMAL(10,2) NOT NULL,
  launch DATE
);
DESCRIBE products;

Result: 4 rows

Field	Type	Null	Key
id	int unsigned	NO	PRI
name	varchar(80)	NO
price	decimal(10,2)	NO
launch	date	YES

Exercise 2: What happens if you insert 70000 into a SMALLINT column?

solution.sql

CREATE TABLE t (n SMALLINT);
INSERT INTO t VALUES (70000);

Output

Query OK, 0 rows affected
ERROR 1264 (22003): Out of range value for column 'n' at row 1

12. Summary

Numeric: integer types by size; DECIMAL for exact values (money); FLOAT/DOUBLE for approximate ones.
String: VARCHAR for most text, CHAR for fixed codes, TEXT for long text, ENUM/SET for fixed lists.
Date and time: DATE, TIME, DATETIME (as written) and TIMESTAMP (UTC-converted, ends in 2038).
JSON stores validated documents; BOOLEAN is TINYINT(1).
Strict mode rejects values that don't fit, so the right type also protects your data.

Was this tutorial helpful?

Help Us Improve

MySQL Data Types

1. Data Type Families

2. Integer Types

3. DECIMAL vs FLOAT and DOUBLE

4. String Types: CHAR, VARCHAR and TEXT

5. ENUM and SET

6. Date and Time Types

7. JSON

8. BOOLEAN and BIT

9. How to Choose the Right Type

10. Checking a Table's Column Types

11. Practice Exercises

12. Summary