MySQL Datatypes
There are three main datatypes in MySQL database.
1. Numeric Data Types
2. String Data Types
3. Date and Time Data Types
Numeric Data Types
These are the following numeric datatypes -
Data Type | Description |
INT | Integer can be signed and unsigned. Signed values range from -2147483648 to 2147483647. Unsigned values range from 0 to 4294967295. |
TINYINT | Very small integer that can be signed and unsigned. Signed values range from -128 to 127. Unsigned values range from 0 to 255. |
SMALLINT | Small integer value that can be signed and unsigned. Signed values range from -32768 to 32767. Unsigned values range from 0 to 65535. |
MEDIUMINT | Medium integer value that can be signed and unsigned. Signed values range from -8388608 to 8388607. Unsigned values range from 0 to 16777215. |
BIGINT | Big integer value that can be signed and unsigned. Signed values range from -9223372036854775808 to 9223372036854775807. Unsigned values range from 0 to 18446744073709551615. |
FLOAT(m,d) | Float is single precision, floating point number. It is always signed. m is the total digits and d is the number of digits after decimal. By default m is 10 and d is 2. |
DOUBLE(m,d) | Double is double precision, floating point number. m is the total digits and d is the number of digits after decimal. By default m is 10 and d is 2. |
DECIMAL(m,d) | Decimal is signed, fixed point number. It has exact values. m is the total digits and d is the number of digits after decimal. |
String Data Types
These are the following string datatypes -
CHAR(size) | CHAR datatype is a fixed length character string. It can hold upto 255 characters in length. By default it can store only one character. When we retrieved the maximum char limit values, then the trailing space is removed automatically. |
VARCHAR(size) | VARCHAR datatype is a variable length character string. It can hold upto 255 characters in length. When we retrieved the maximum VARCHAR limit values, the trailing space is not removed. |
BINARY(size) | It contains fixed length binary string. It can hold 255 byte strings in length. |
VARBINARY(size) | It contains variable length binary string. It can hold 255 byte strings in length. |
BLOB(size) | BLOB stands for Binary Large Objects. It is used to store variable amounts of binary data like images or other types of files. It has maximum size limits to 65535 characters. There are four BLOB types that differ only on size limits- TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB. |
TEXT(size) | TEXT is character strings. It has maximum size limits to 65535 characters. |
ENUM(size) | In ENUM, you are creating a set options. The value of the field is one of the created options or it can be NULL. |
Date and Time Data types
DATE | DATE datatype is used to store DATE only and by default it is in 'YYYY-MM-DD' format. |
TIME | TIME datatype is used to store TIME only. It is in format 'HHH:MM:SS' and range from '-838:59:59' to '838:59:59'. |
DATETIME | DATETIME contains both DATE and TIME in format 'YYYY-MM-DD HH:MM:SS'. It range from '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. |
TIMESTAMP | It displays DATE and TIME in same format as DATETIME. It range from '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. |
YEAR | YEAR stores in four digits format. It range from 1901 to 2155. |