MySQL Data Types

Choosing the right data type keeps your database small, fast and correct.

Numeric

Type	Storage	Signed range
TINYINT	1 byte	−128 to 127
SMALLINT	2 bytes	−32,768 to 32,767
MEDIUMINT	3 bytes	±8.3 million
INT	4 bytes	±2.14 billion
BIGINT	8 bytes	±9.2 quintillion
DECIMAL(p,s)	varies	Exact, for money
FLOAT / DOUBLE	4 / 8 bytes	Approximate

Add UNSIGNED to double the positive range: INT UNSIGNED goes up to 4.29 billion. Use BOOLEAN (an alias for TINYINT(1)) for true/false.

Never store money in FLOAT. 0.1 + 0.2 is not 0.3 in binary floating point. Use DECIMAL(10,2).

String

Type	Notes
CHAR(n)	Fixed length, padded; fast for codes like BD, MD5
VARCHAR(n)	Variable length up to 65,535 bytes per row
TEXT family	TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT (up to 4 GB)
BINARY/VARBINARY	Raw bytes
BLOB family	Binary large objects (files, images)
ENUM('a','b')	One value from a fixed list
SET('a','b')	Zero or more values from a list

Date and time

Type	Format	Range
DATE	YYYY-MM-DD	1000–9999
TIME	HH:MM:SS	−838:59:59 to 838:59:59
DATETIME	YYYY-MM-DD HH:MM:SS	1000–9999, no timezone conversion
TIMESTAMP	same	1970–2038, converted to/from UTC
YEAR	YYYY	1901–2155

JSON

MySQL 5.7+ has a native JSON type with validation and indexable generated columns.

CREATE TABLE settings (
  user_id INT PRIMARY KEY,
  prefs   JSON
);
INSERT INTO settings VALUES (1, '{"theme":"dark","lang":"bn"}');
SELECT prefs->>'$.theme' AS theme FROM settings;

Practical rules

• Pick the smallest type that fits — smaller rows mean more rows per page and faster scans.
• Use VARCHAR(255) for emails/names, not TEXT — TEXT cannot be fully indexed.
• Store timestamps in UTC and format in the application layer.
• Use utf8mb4 charset so emojis and full Bengali text work correctly.