Mysql Data Types

MySQL: Data Types

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 Explanation
CHAR(size) Where size is the number of characters to store. Fixed-length strings. Space padded on right to equal size characters.
VARCHAR(size) Where size is the number of characters to store. Variable-length string.
TINYTEXT(size) Where size is the number of characters to store.
TEXT(size) Where size is the number of characters to store.
MEDIUMTEXT(size) Where size is the number of characters to store.
LONGTEXT(size) Where size is the number of characters to store.
BINARY(size) 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) 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 Explanation
BIT
TINYINT(m)
SMALLINT(m)
MEDIUMINT(m)
INT(m)
INTEGER(m) This is a synonym for the INT datatype.
BIGINT(m)
DECIMAL(m,d) Where m is the total digits and d is the number of digits after the decimal.
DEC(m,d) 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) 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) 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) Where m is the total digits and d is the number of digits after the decimal.
DOUBLE(m,d) Where m is the total digits and d is the number of digits after the decimal.
DOUBLE PRECISION(m,d) 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) 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) Where p is the precision.
BOOL 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 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 Explanation
DATE Displayed as 'YYYY-MM-DD'.
DATETIME Displayed as 'YYYY-MM-DD HH:MM:SS'.
TIMESTAMP(m) Displayed as 'YYYY-MM-DD HH:MM:SS'.
TIME Displayed as 'HH:MM:SS'.
YEAR[(2|4)] Default is 4 digits.

Large Object (LOB) Datatypes

The following are the LOB Datatypes in MySQL:

Data Type Syntax Explanation
TINYBLOB
BLOB(size) Where size is the number of characters to store (size is optional and was introduced in MySQL 4.1)
MEDIUMBLOB
LONGTEXT