MySQL Data Types
In MySQL, data types define the data type that can be stored in a column. Choosing the right data type ensures efficient storage, better performance, and data integrity.
Categories of MySQL Data Types
MySQL data types are broadly classified into the following categories:
- Numeric Types
- Date and Time Types
- String (Character) Types
- Spatial Types
- JSON Type
1. Numeric Data Types
Integer Types
| Type | Storage (Bytes) | Minimum Value | Maximum Value | Unsigned Maximum Value |
|---|
TINYINT | 1 | -128 | 127 | 0 to 255 |
SMALLINT | 2 | -32,768 | 32,767 | 0 to 65,535 |
MEDIUMINT | 3 | -8,388,608 | 8,388,607 | 0 to 16,777,215 |
INT/INTEGER | 4 | -2,147,483,648 | 2,147,483,647 | 0 to 4,294,967,295 |
BIGINT | 8 | -9,223,372,036,854,775,808 | 9,223,372,036,854,775,807 | 0 to 18,446,744,073,709,551,615 |
Floating-Point Types
| Type | Storage (Bytes) | Precision | Example Values |
|---|
FLOAT(p) | 4 or 8 | Approx. 7 digits | 3.14, -2.71 |
DOUBLE/REAL | 8 | Approx. 16 digits | 2.718281828459 |
Fixed-Point Types
| Type | Description | Example |
|---|
DECIMAL(p,s) or NUMERIC(p,s) | Precision (p) and scale (s) | DECIMAL(10,2) for 12345678.90 |
2. Date and Time Data Types
| Type | Storage (Bytes) | Format | Description |
|---|
DATE | 3 | YYYY-MM-DD | Stores date values. |
DATETIME | 8 | YYYY-MM-DD HH:MM:SS | Stores date and time. |
TIMESTAMP | 4 | YYYY-MM-DD HH:MM:SS | Stores UTC-based date and time. |
TIME | 3 | HH:MM:SS | Stores time values. |
YEAR | 1 | YYYY | Stores 4-digit year values. |
3. String (Character) Data Types
Character Types
| Type | Storage (Bytes) | Description |
|---|
CHAR(n) | Fixed length (1-255) | Fixed-length string. E.g., CHAR(10) |
VARCHAR(n) | Variable length (1-65,535) | Variable-length string. E.g., VARCHAR(50) |
Text Types
| Type | Storage (Bytes) | Maximum Length | Description |
|---|
TINYTEXT | 1 + L | 255 characters | Small text. |
TEXT | 2 + L | 65,535 characters | Large text. |
MEDIUMTEXT | 3 + L | 16,777,215 characters | Medium-sized text. |
LONGTEXT | 4 + L | 4,294,967,295 characters | Very large text. |
Binary Data Types
| Type | Storage (Bytes) | Description |
|---|
BINARY(n) | Fixed length (1-255) | Fixed-length binary data. |
VARBINARY(n) | Variable length (1-65,535) | Variable-length binary data. |
Blob Types
| Type | Storage (Bytes) | Maximum Length | Description |
|---|
TINYBLOB | 1 + L | 255 bytes | Small binary objects. |
BLOB | 2 + L | 65,535 bytes | Large binary objects. |
MEDIUMBLOB | 3 + L | 16,777,215 bytes | Medium binary objects. |
LONGBLOB | 4 + L | 4,294,967,295 bytes | Very large binary objects. |
4. Spatial Data Types
MySQL supports geographic data types through the Spatial Extensions.
| Type | Description |
|---|
GEOMETRY | Stores any spatial data. |
POINT | Stores a single point (X, Y). |
LINESTRING | Stores a series of points forming a line. |
POLYGON | Stores a closed geometric shape. |
5. JSON Data Type
| Type | Description |
|---|
JSON | Stores JSON-formatted text data. |
Choosing the Right Data Type
- Numeric Data: Use exact types (
INT, DECIMAL) for precise calculations and approximate types (FLOAT, DOUBLE) for scientific data. - String Data: Use
CHAR for fixed-length fields and VARCHAR for variable-length fields. - Large Text/Binary Data: Use
TEXT or BLOB for large unstructured data. - Date and Time: Use
TIMESTAMP for UTC-based logging and DATETIME for general date-time storage. - Hierarchical/Spatial Data: Use spatial types for GIS or geometric data.
Common Mistakes and Best Practices
- Overusing
TEXT or BLOB: Only use these types when absolutely necessary, as they can impact performance. - Choosing Inappropriate Size: For example, using
BIGINT when INT is sufficient to increase storage overhead. - Not Indexing JSON Data: JSON data types can be indexed using generated columns.
This guide provides an overview of MySQL data types and their best use cases. Let me know if you need deeper insights or further examples!
