MySQL Data Types with Examples
Numeric Data Types
Numeric Data TypesIn MySQL, numeric data types are used to store numeric values. They can be categorized into exact numeric types, like INTEGER, and approximate numeric types, like FLOAT.
| Data Type | Description | Example |
|---|---|---|
INT | A normal-size integer that can be signed or unsigned. | INT(11) |
FLOAT | A floating-point number. | FLOAT |
| Data Type | Description | Example |
TINYINT | A very small integer that can range from -128 to 127. | TINYINT |
SMALLINT | A small integer that can range from -32,768 to 32,767. | SMALLINT |
MEDIUMINT | A medium-sized integer that can range from -8,388,608 to 8,388,607. | MEDIUMINT |
INT | A standard integer that can range from -2,147,483,648 to 2,147,483,647. | INT |
BIGINT | A large integer that can range from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. | BIGINT |
DECIMAL | An exact fixed-point number. | DECIMAL(5,2) |
FLOAT | A small approximate floating-point number. | FLOAT |
DOUBLE | A large approximate floating-point number. | DOUBLE |
String Data Types
String Data TypesIn MySQL, string data types are used to store textual or binary data. They come in various forms to suit different requirements for storage and complexity.
| Data Type | Description | Example |
|---|---|---|
VARCHAR | A variable-length string. | VARCHAR(255) |
TEXT | A text column with a maximum length of \(2^{16} – 1\) characters. | TEXT |
| Data Type | Description | Example |
CHAR | A fixed-length string that is always right-padded with spaces to the specified length. | CHAR(25) |
VARCHAR | A variable-length string. Efficient for storing strings that can have a variable length up to a defined maximum. | VARCHAR(255) |
TINYTEXT | A small text column with a maximum length of 255 characters. | TINYTEXT |
TEXT | A text column with a maximum length of \(2^{16} – 1\) characters. | TEXT |
MEDIUMTEXT | A text column with a maximum length of \(2^{24} – 1\) characters. | MEDIUMTEXT |
LONGTEXT | A text column with a maximum length of \(2^{32} – 1\) characters. | LONGTEXT |
BINARY | Similar to CHAR but stores binary byte strings. | BINARY(10) |
VARBINARY | Similar to VARCHAR but for binary byte strings. | VARBINARY(100) |
BLOB | A Binary Large Object column that can store up to \(2^{16} – 1\) bytes of data. | BLOB |
Temporal Data Types
Date and Time Data TypesIn MySQL, date and time data types are used to store date, time, or datetime values. These types allow you to work efficiently with date and time information.
| Data Type | Description | Example |
|---|---|---|
DATETIME | Used for values that contain both date and time. | DATETIME |
DATE | Used for values with a date part but no time part. | DATE |
| Data Type | Description | Example |
DATE | Used to store date values in the format ‘YYYY-MM-DD’. | DATE |
TIME | Used to store time values in the format ‘HH:MM:SS’. | TIME |
DATETIME | Used to store date and time values in the format ‘YYYY-MM-DD HH:MM:SS’. | DATETIME |
TIMESTAMP | Stores the number of seconds since the Unix epoch (‘1970-01-01 00:00:00’ UTC) as a datetime value. | TIMESTAMP |
YEAR | Used to store year information, either as a 4-digit or 2-digit format. | YEAR(4) |