When creating a table, each column requires a data type that defines the type of data it can hold, such as numbers, text, dates, or binary data. MySQL data types fall into three main categories: String, Numeric, and Date and Time.
1. String Data Types
| Data Type |
Description |
| CHAR(size) |
Fixed-length string. size specifies column length (0 to 255). Default is 1. |
| VARCHAR(size) |
Variable-length string. size specifies max column length (0 to 65535). |
| BINARY(size) |
Fixed-length binary data. size specifies length in bytes. Default is 1. |
| VARBINARY(size) |
Variable-length binary data. size specifies max length in bytes. |
| TINYBLOB |
Binary large object. Max size: 255 bytes. |
| TINYTEXT |
String with max length of 255 characters. |
| TEXT(size) |
String with max length of 65,535 bytes. |
| BLOB(size) |
Binary large object, holding up to 65,535 bytes. |
| MEDIUMTEXT |
String with max length of 16,777,215 characters. |
| MEDIUMBLOB |
Binary large object, holding up to 16,777,215 bytes. |
| LONGTEXT |
String with max length of 4,294,967,295 characters. |
| LONGBLOB |
Binary large object, holding up to 4,294,967,295 bytes. |
| ENUM(val1, val2, ...) |
String object with one value chosen from a list of up to 65,535 values. If invalid value inserted, defaults to a blank. |
| SET(val1, val2, ...) |
String object that can store multiple values from a list of up to 64 values. |
2. Numeric Data Types
| Data Type |
Description |
| BIT(size) |
Bit-value type (1 to 64 bits). Default size is 1. |
| TINYINT(size) |
Very small integer. Signed: -128 to 127. Unsigned: 0 to 255. |
| BOOL / BOOLEAN |
Treated as TINYINT(1). Zero is false; nonzero is true. |
| SMALLINT(size) |
Small integer. Signed: -32,768 to 32,767. Unsigned: 0 to 65,535. |
| MEDIUMINT(size) |
Medium integer. Signed: -8,388,608 to 8,388,607. Unsigned: 0 to 16,777,215. |
| INT(size) / INTEGER |
Standard integer. Signed: -2,147,483,648 to 2,147,483,647. Unsigned: 0 to 4,294,967,295. |
| BIGINT(size) |
Large integer. Signed: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. Unsigned: 0 to 18,446,744,073,709,551,615. |
| FLOAT(size, d) |
Floating-point number. size is total digits; d is decimal places. Deprecated in MySQL 8.0.17. |
| FLOAT(p) |
Uses p to determine if FLOAT or DOUBLE is used. p ≤ 24 → FLOAT, p > 24 → DOUBLE. |
| DOUBLE(size, d) |
Normal-size floating-point number. |
| DECIMAL(size, d) / DEC |
Exact fixed-point number. size is total digits (default: 10), d is decimal places (default: 0). |
Note: Numeric types can include UNSIGNED (disallows negatives) and ZEROFILL (pads with leading zeros).
3. Date and Time Data Types
| Data Type |
Description |
| DATE |
Date in YYYY-MM-DD format. Range: 1000-01-01 to 9999-12-31. |
| DATETIME(fsp) |
Date and time in YYYY-MM-DD HH:MI:SS format. Range: 1000-01-01 00:00:00 to 9999-12-31 23:59:59. Supports fractions. |
| TIMESTAMP(fsp) |
Timestamp stored as seconds since Unix epoch (1970-01-01 00:00:00 UTC). Supports fractions. |
| TIME(fsp) |
Time in HH:MM:SS format. Range: -838:59:59 to 838:59:59. |
| YEAR |
Year in YYYY format. Range: 1901 to 2155 and 0000. |
Choosing the Right Data Type
- Use appropriate types to save storage and optimize performance.
- For text data, prefer
VARCHAR over TEXT when the size is predictable.
- Use UNSIGNED for numeric fields that will only store positive values.
- Leverage date/time types like
TIMESTAMP for timezone-specific applications.