Datatypes in MySQL
MySQL supports a variety of data types that can be used to define the structure of tables. These data types are broadly categorized into three groups: numeric types, date and time types, and string (character) types. Each type has specific use cases depending on the nature of the data to be stored.
1. Numeric Data Types
Numeric data types are used to store numbers, either integers or decimals.
Integer Types:
| Data Type | Storage | Range (Signed) | Range (Unsigned) |
|---|---|---|---|
| TINYINT | 1 byte | -128 to 127 | 0 to 255 |
| SMALLINT | 2 bytes | -32,768 to 32,767 | 0 to 65,535 |
| MEDIUMINT | 3 bytes | -8,388,608 to 8,388,607 | 0 to 16,777,215 |
| INT or INTEGER | 4 bytes | -2,147,483,648 to 2,147,483,647 | 0 to 4,294,967,295 |
| BIGINT | 8 bytes | -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 | 0 to 18,446,744,073,709,551,615 |
- Signed: Can store negative and positive numbers.
- Unsigned: Can store only positive numbers but has a larger upper limit.
Floating Point and Decimal Types:
| Data Type | Storage | Description |
|---|---|---|
| FLOAT(M,D) | 4 bytes | Approximate floating-point number. M is the total number of digits, and D is the number of digits after the decimal point. |
| DOUBLE(M,D) | 8 bytes | Approximate floating-point number with more precision than FLOAT. |
| DECIMAL(M,D) | Varies | Exact fixed-point number. M is the total number of digits, and D is the number of digits after the decimal point. |
- FLOAT and DOUBLE are used for approximate numeric values, while DECIMAL is used for exact values (especially useful for currency).
Boolean Type:
- BOOLEAN or BOOL: A synonym for TINYINT(1). It stores 0 (false) and 1 (true).
2. Date and Time Data Types
MySQL supports several types for handling date and time data:
| Data Type | Storage | Description |
|---|---|---|
| DATE | 3 bytes | Stores a date in the format YYYY-MM-DD. |
| DATETIME | 8 bytes | Stores date and time in the format YYYY-MM-DD HH:MM:SS. |
| TIMESTAMP | 4 bytes | Stores date and time as a Unix timestamp, and automatically updates when records are inserted or modified. |
| TIME | 3 bytes | Stores time in the format HH:MM:SS. |
| YEAR | 1 byte | Stores a year as a 4-digit value (e.g., 2024). |
- DATETIME is useful when the application needs to store both date and time values.
- TIMESTAMP is often used for recording the exact time of an event, and it's tied to time zones.
3. String (Character) Data Types
String data types are used to store text or binary data.
Textual Data Types:
| Data Type | Storage | Description |
|---|---|---|
| CHAR(M) | Fixed length | Fixed-length string with a maximum length of M characters. Padded with spaces if shorter than M. |
| VARCHAR(M) | Variable length | Variable-length string with a maximum length of M characters. Efficient for storing varying-length text. |
| TEXT | 2 + string length | Stores long text data. Can hold up to 65,535 characters. |
| TINYTEXT | 1 + string length | Stores short text data. Can hold up to 255 characters. |
| MEDIUMTEXT | 3 + string length | Can hold up to 16,777,215 characters. |
| LONGTEXT | 4 + string length | Can hold up to 4,294,967,295 characters. |
- CHAR is used for strings of fixed length, while VARCHAR is used for strings that can vary in length.
- TEXT types are used when dealing with larger text data.
Binary Data Types:
| Data Type | Storage | Description |
|---|
| BINARY(M) | Fixed length | Stores fixed-length binary data. Similar to CHAR but for binary data. |
| VARBINARY(M) | Variable length | Stores variable-length binary data. Similar to VARCHAR but for binary data. |
| BLOB | Varies | Binary Large Object. Stores binary data, useful for storing images, multimedia files, etc. |
- BLOB (Binary Large Object) is used to store binary data like images, audio files, or documents.
Enum and Set Data Types:
| Data Type | Description |
|---|---|
| ENUM | A string object that can have one value chosen from a list of predefined values. |
| SET | A string object that can have zero or more values chosen from a predefined list. |
Example of ENUM:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Gender ENUM('Male', 'Female', 'Other')
);