We need to specify while designing the database, what type of data will be stored inside each column when creating a table in the database.
There are three main data types in MySQL:- string or text, numeric and date & time. We must use only the type and size of the field we really need to use. Eg: do not define a field for phone numbers with 50 characters wide, if you know you are only going to use an integer of 10 digits.
String or Text Data Types
| Data type |
Size |
Description |
| BINARY(size) |
0 to 255 |
Contains binary strings. |
| BLOB(size) |
up to 65,535 bytes |
BLOB is a binary large object that can hold a variable amount of data. |
| CHAR(size) |
0 to 255 |
The CHAR data type is a fixed-length character type in MySQL. |
| ENUM |
1 or 2 bytes |
It allows us to limit the value chosen from a list of permitted values in the column specification at the time of table creation. |
| LONGBLOB |
Up to 4 GB |
Each LONGBLOB value is stored using a four-byte length prefix that indicates the number of bytes in the value. |
| LONGTEXT |
Up to 4 GB |
Holds a string. |
| MEDIUMBLOB |
up to 16 MB |
Binary large Object |
| MEDIUMTEXT |
up to 16 MB |
Holds a string |
| SET |
1, 2, 3, 4, or 8 bytes |
A SET is a string object that can have zero or more values, each of which must be chosen from a list of permitted values. |
| TEXT(size) |
up to 65,535 bytes |
Holds a string |
| TINYBLOB |
Up to 255 bytes |
For BLOBs (Binary Large Objects). |
| TINYTEXT |
Up to 255 bytes |
Holds a string |
| VARBINARY(size) |
up to 65,535 bytes |
Contains binary strings. |
| VARCHAR(size) |
up to 65,535 bytes |
A VARIABLE length string (can contain letters, numbers, and special characters). |
Numeric data types
Integer Types
| Type |
Length in Bytes |
Minimum Value(Signed) |
Maximum Value (Signed) |
Minimum Value (Unsigned) |
Maximum Value (Unsigned) |
| TINYINT |
1 |
-128 |
127 |
0 |
255 |
| SMALLINT |
2 |
-32768 |
32767 |
0 |
65535 |
| MEDIUMINT |
3 |
-8388608 |
8388607 to 0 |
16777215 |
| INT |
4 |
-2147483648 |
2147483647 |
0 |
4294967295 |
| BIGINT |
8 |
-9223372036854775808 |
9223372036854775807 |
0 |
18446744073709551615 |
Floating-Point Types
| Data type |
Description |
| FLOAT |
A precision from 0 to 23 results in a four-byte single-precision FLOAT column. |
| DOUBLE |
A precision from 24 to 53 results in an eight-byte double-precision DOUBLE column. |
Boolean Value Types
| Data type |
Description |
| BOOL |
Zero is considered as false, nonzero values are considered as true. |
| BOOLEAN |
Equal to BOOL. |
Date and Time Data Types
| Data type |
Description |
| Date |
A date. Format: YYYY-MM-DD. The supported range is from '1000-01-01' to '9999-12-31'. |
| DATETIME |
The DATETIME type is used for values that contain both date and time. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD hh:mm:ss' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. |
| TIMESTAMP |
The MySQL TIMESTAMP is a temporal data type that holds the combination of date and time. The format of a TIMESTAMP is YYYY-MM-DD HH:MM:SS. The supported range is from '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC. |
| TIME |
The TIME data type can express the time of day or duration, and has a range of ‘-838:59:59’ to ‘838:59:59’. MySQL displays TIME values in ‘HH:MM:SS’ format. |
| YEAR |
A year in four-digit format. Values allowed in four-digit format: 1901 to 2155, and 0000. |
Bit Value Types
The BIT data type is used to store bit-field values. A type of BIT(N) enables storage of N-bit values. N can range from 1 to 64.
To specify bit values, b 'value' notation can be used. value is a binary value written using zeros and ones. For example, b'111' and b'10000000' represent 7 and 128, respectively.
| Data type |
Size |
Description |
| DOUBLE(m,d) |
Double precision floating point number. |
Where m is the total digits and d is the number of digits after the decimal. |
| DECIMAL(m,d) |
Unpacked fixed point number. |
Where m is the total digits and d is the number of digits after the decimal. |
