Data types are used in MySQL to specify the kinds of information that can be kept in a column. Because it directly affects your database's scalability, storage capacity, and performance, selecting the appropriate data type is essential. Numerous data types are supported by MySQL, which can be broadly divided into numeric, date and time, and string kinds in addition to more specialized types like JSON and geographical data. In this article, we will learn about MySQL data types in detail with help of examples.
There are three main categories of data types in MySQL:
- Numeric Types: It used to store floating-point, decimal, and integer numbers, among other numerical quantities.
- Date and Time Types: It used to store timestamp, date, and time information.
- String Types: It used to store text, binary data, and other types are stored in string types.
Specialized types are also available for JSON, geographical data, and other types.
Numeric Data Types
Numeric data types are used to store numbers for calculations, statistics, or identifiers.
Integer Types
Signed and unsigned whole numbers are stored in integer types. Unsigned numbers only permit positive values but broaden the range, whereas signed integers permit both positive and negative values.
| Data Type | Storage (Bytes) | Range |
| TINYINT | 1 | -128 to 127 |
| SMALLINT | 2 | -32,768 to 32,767 |
| MEDIUMINT | 3 | -8,388,608 to 8,388,607 |
| INT | 4 | -2,147,483,648 to 2,147,483,647 |
| BIGINT | 8 | -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 |
Use Case Example
- TINYINT: Stores status flag like true/false (e.g., 0 for false, 1 for true).
- SMALLINT: Stores small numeric ranges like age or category IDs.
- INT: Default for most IDs (e.g., user IDs).
- BIGINT: For very large numbers, such as timestamps or financial records.
Floating-Point Types
It mostly used to store for real numbers (numbers with a fractional part).
| Data Type | Storage (Bytes) | Range |
| FLOAT | 4 | ±3.402823466E+38 (7 digits precision) |
| DOUBLE | 8 | ±1.7976931348623157E+308 (16 digits precision) |
Use Case Examples
- FLOAT: Scientific measurements, like temperature or GPS coordinates.
- DOUBLE: Financial data where higher precision is required.
Fixed-Point Types
For financial applications, fixed-point types are perfect since they hold precise numerical values.
| Data Type | Description |
| DECIMAL(M, D) or NUMERIC(M, D) | Exact numbers with M total digits and D digits after the decimal point. |
Example: DECIMAL(10, 2) stores a number with up to 8 digits before the decimal and 2 digits after.
Use Case Examples
- DECIMAL: It store price related value like product price (e.g., price = 99.99)
Also read about MySQL Introduction & its Feature
Date and Time Data Types
Date and time types are helpful for keeping track of events, timetables, and logs since they hold temporal values.
| Data Type | Storage (Bytes) | Range | Format |
| DATE | 3 | '1000-01-01' to '9999-12-31' | 'YYYY-MM-DD' |
| DATETIME | 8 | '1000-01-01 00:00:00' to '9999-12-31 23:59:59' | 'YYYY-MM-DD HH:MM:SS' |
| TIMESTAMP | 4 | '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC | 'YYYY-MM-DD HH:MM:SS' |
| TIME | 3 | '-838:59:59' to '838:59:59' | 'HH:MM:SS' |
| YEAR | 1 | 1901 to 2155 | 'YYYY' |
Use Case Examples
- DATE: It stores birth date or fixed calendar dates.
- DATETIME: It stores events with full date and time (e.g., order timestamp).
- TIMESTAMP: It automatically adjust for time zones.
- TIME: Durations or times of day (e.g., "09:30:00").
- YEAR: Representing years (e.g., "2023").
String Data Types
String types are used for text related data and binary data.
| Data Type | Storage | Maximum Length |
| CHAR | Fixed-length | Up to 255 characters |
| VARCHAR | Variable-length | Up to 65,535 characters(depends on row size) |
| TEXT | Variable-length | Up to 65,535 characters |
| TINYTEXT | Variable-length | Up to 255 characters |
| MEDIUMTEXT | Variable-length | Up to 16,777,215 characters |
| LONGTEXT | Variable-length | Up to 4,294,967,295 characters |
Use Case Examples
- CHAR: It stores Fixed-length sizes text like postal codes or country codes.
- VARCHAR: It stores Usernames, email addresses, or name etc.
- TEXT: It stores articles text, product description etc.
Binary Types
| Data Type | Storage | Maximum Length |
| BLOB | Variable-length | Up to 65,535 bytes |
BLOB type generally used to store like images, videos, files etc.
Conclusion
To accommodate various types of data, MySQL offers a wide variety of data types. You can guarantee your database's scalability, excellent performance, and effective storage by comprehending and selecting the appropriate type for each column. A solid database is built on a well-designed schema, and data types are essential to doing that.
