The following is a list of datatypes available in MySQL, which includes string, numeric, date/time, and large object datatypes.
String Datatypes
The following are the String Datatypes in MySQL:
| Data Type Syntax | Maximum Size | Explanation |
|---|
| CHAR(size)
| Maximum size of 255 characters.
| Where size is the number of characters to store. Fixed-length strings. Space padded on right to equal size characters.
|
| VARCHAR(size)
| Maximum size of 255 characters.
| Where size is the number of characters to store. Variable-length string.
|
| TINYTEXT(size)
| Maximum size of 255 characters.
| Where size is the number of characters to store.
|
| TEXT(size)
| Maximum size of 65,535 characters.
| Where size is the number of characters to store.
|
| MEDIUMTEXT(size)
| Maximum size of 16,777,215 characters.
| Where size is the number of characters to store.
|
| LONGTEXT(size)
| Maximum size of 4GB or 4,294,967,295 characters.
| Where size is the number of characters to store.
|
| BINARY(size)
| Maximum size of 255 characters.
| Where size is the number of binary characters to store. Fixed-length strings. Space padded on right to equal size characters.
(Introduced in MySQL 4.1.2)
|
| VARBINARY(size)
| Maximum size of 255 characters.
| Where size is the number of characters to store. Variable-length string.
(Introduced in MySQL 4.1.2)
|
Numeric Datatypes
The following are the Numeric Datatypes in MySQL:
| Data Type Syntax | Maximum Size | Explanation |
|---|
| BIT
| Very small integer value that is equivalent to TINYINT(1).
Signed values range from -128 to 127. Unsigned values range from 0 to 255.
|
|
| TINYINT(m)
| Very small integer value.
Signed values range from -128 to 127. Unsigned values range from 0 to 255.
|
|
| SMALLINT(m)
| Small integer value.
Signed values range from -32768 to 32767. Unsigned values range from 0 to 65535.
|
|
| MEDIUMINT(m)
| Medium integer value.
Signed values range from -8388608 to 8388607. Unsigned values range from 0 to 16777215.
|
|
| INT(m)
| Standard integer value.
Signed values range from -2147483648 to 2147483647. Unsigned values range from 0 to 4294967295.
|
|
| INTEGER(m)
| Standard integer value.
Signed values range from -2147483648 to 2147483647. Unsigned values range from 0 to 4294967295.
| This is a synonym for the INT datatype.
|
| BIGINT(m)
| Big integer value.
Signed values range from -9223372036854775808 to 9223372036854775807. Unsigned values range from 0 to 18446744073709551615.
|
|
| DECIMAL(m,d)
| Unpacked fixed point number.
m defaults to 10, if not specified.
d defaults to 0, if not specified.
| Where m is the total digits and d is the number of digits after the decimal.
|
| DEC(m,d)
| Unpacked fixed point number.
m defaults to 10, if not specified.
d defaults to 0, if not specified.
| Where m is the total digits and d is the number of digits after the decimal. This is a synonym for the DECIMAL datatype.
|
| NUMERIC(m,d)
| Unpacked fixed-point number.
m defaults to 10, if not specified.
d defaults to 0, if not specified.
| Where m is the total digits and d is the number of digits after the decimal. This is a synonym for the DECIMAL datatype.
|
| FIXED(m,d)
| Unpacked fixed-point number.
m defaults to 10, if not specified.
d defaults to 0, if not specified.
| Where m is the total digits and d is the number of digits after the decimal.
(Introduced in MySQL 4.1) This is a synonym for the DECIMAL datatype.
|
| FLOAT(m,d)
| Single precision floating point number.
| Where m is the total digits and d is the number of digits after the decimal.
|
| DOUBLE(m,d)
| Double precision floating point number.
| Where m is the total digits and d is the number of digits after the decimal.
|
| DOUBLE PRECISION(m,d)
| Double precision floating point number.
| Where m is the total digits and d is the number of digits after the decimal. This is a synonym for the DOUBLE datatype.
|
| REAL(m,d)
| Double precision floating point number.
| Where m is the total digits and d is the number of digits after the decimal. This is a synonym for the DOUBLE datatype.
|
| FLOAT(p)
| Floating point number.
| Where p is the precision.
|
| BOOL
| Synonym for TINYINT(1)
| Treated as a boolean data type where a value of 0 is considered to be FALSE and any other value is considered to be TRUE.
|
| BOOLEAN
| Synonym for TINYINT(1)
| Treated as a boolean data type where a value of 0 is considered to be FALSE and any other value is considered to be TRUE.
|
Date/Time Datatypes
The following are the Date/Time Datatypes in MySQL:
| Data Type Syntax | Maximum Size | Explanation |
|---|
| DATE
| Values range from '1000-01-01' to '9999-12-31'.
| Displayed as 'YYYY-MM-DD'.
|
| DATETIME
| Values range from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
| Displayed as 'YYYY-MM-DD HH:MM:SS'.
|
| TIMESTAMP(m)
| Values range from '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.
| Displayed as 'YYYY-MM-DD HH:MM:SS'.
|
| TIME
| Values range from '-838:59:59' to '838:59:59'.
| Displayed as 'HH:MM:SS'.
|
| YEAR[(2|4)]
| Year value as 2 digits or 4 digits.
| Default is 4 digits.
|
Large Object (LOB) Datatypes
The following are the LOB Datatypes in MySQL:
| Data Type Syntax | Maximum Size | Explanation |
|---|
| TINYBLOB
| Maximum size of 255 bytes.
|
|
| BLOB(size)
| Maximum size of 65,535 bytes.
| Where size is the number of characters to store (size is optional and was introduced in MySQL 4.1)
|
| MEDIUMBLOB
| Maximum size of 16,777,215 bytes.
|
|
| LONGTEXT
| Maximum size of 4GB or 4,294,967,295 characters.
|
|
What is the size of BIGINT in MySQL?
Table 11.1 Required Storage and Range for Integer Types Supported by MySQL.
What is BIGINT 20 MySQL?
The "BIGINT(20)" specification isn't a digit limit. It just means that when the data is displayed, if it uses less than 20 digits it will be left-padded with zeros.
What is BIGINT in MySQL?
BIGINT is the MySQL data type that can be assigned to the columns of the table in which we want to store the whole numbers and we are aware that the range of the numbers that we will store in that column will be huge and not exceed the range of the BIGINT data type.
What is the range of BIGINT?
A big integer is a binary integer with a precision of 63 bits. The range of big integers is -9223372036854775808 to +9223372036854775807.
