MySQL Data Types – Eric Frisino

Recently I was creating a few database tables and was looking for a quick and easy reference to check what each MySQL data type contained. This handy little table is what what made as a result.

String Types

`CHAR(L)`	Length: 1 to 255 Characters L: Sets length of string. Can Contain: letters, numbers, and special characters Note: the entry will be automatically right-padded with spaces to the specified length (L) when stored. Example: `Hi!` passed into `CHAR(5)` will be `Hi!__` where `_` equals a blank space. Example: `Hospital` passed into `CHAR(5)` will be saved as `Hospi` in the database.
`VARCHAR(L)`	Length: 1 to 255 Characters L: Sets the max length of string. Can Contain: letters, numbers, and special characters Note: the entry can be any length up to the defined max length. Note: If the defined length is greater than 255 on creation of the field then it will automatically be converted to a text field.
`TINYTEXT`	Maximum Length: 255 Characters Can Contain: letters, numbers, and special characters Note: Unlike BLOBs TEXT fields sort and compare stored data as non-case sensitive.
`TEXT`	Maximum Length: 65,535 Characters Can Contain: letters, numbers, and special characters Note: Unlike BLOBs TEXT fields sort and compare stored data as non-case sensitive.
`MEDIUMTEXT`	Maximum Length: 16,777,215 Characters Can Contain: letters, numbers, and special characters Note: Unlike BLOBs TEXT fields sort and compare stored data as non-case sensitive.
`LONGTEXT`	Maximum Length: 4,294,967,295 Characters Can Contain: letters, numbers, and special characters Note: Unlike BLOBs TEXT fields sort and compare stored data as non-case sensitive.
`TINYBLOB`	Binary Large Objects Maximum Data Size: 255 bytes or .24 kb Can Contain: Tiny amounts of binary data, such as images or other types of files. Note: Unlike TEXT fields BLOBs sort and compare stored data as case sensitive.
`BLOB`	Binary Large Objects Maximum Data Size: 65,535 bytes or 63.9 Kb. Can Contain: Binary data, such as images or other types of files. Note: Unlike TEXT fields BLOBs sort and compare stored data as case sensitive.
`MEDIUMBLOB`	Binary Large Objects Maximum Data Size: 16,777,215 bytes or 16 Mb. Can Contain: Medium amounts of binary data, such as images or other types of files. Note: Unlike TEXT fields BLOBs sort and compare stored data as case sensitive.
`LONGBLOB`	Binary Large Objects Maximum Data Size: 4,294,967,295 bytes or 4 Gb. Can Contain: Large amounts of binary data, such as images or other types of files. Note: Unlike TEXT fields BLOBs sort and compare stored data as case sensitive.
`ENUM(X, X, X)`	X: A comma separated list of accepted values for this field. Maximum List Items: 65,535 Note: Only values in the list can populate this field, if a value is entered that is not in the list a `null` will be inserted into the field. Note: When sorting an ENUM filed the values will be sorted in the order that you have entered them in the field.

Numbers without decimals

`TINYINT(L)`	Signing: signed or unsigned Signed Range: -128 to 127 Unsigned Range: 0 to 255 L Info: Maximum length of number L Value: 0 – 4 digits. L Requirement: Not Required
`SMALLINT(L)`	Signing: signed or unsigned Signed Range: -32,768 to 32,767 Unsigned Range: 0 to 65,535 L Info: Maximum length of number L Value: 0 – 5 digits. L Requirement: Not Required
`MEDIUMINT(L)`	Signing: signed or unsigned Signed Range: -8,388,608 to 8,388,607 Unsigned Range: 0 to 16,777,215 L Info: Maximum length of number L Value: 0 – 9 digits. L Requirement: Not Required
`INT(L)`	Signing: signed or unsigned Signed Range: -2,147,483,648 to 2,147,483,647 Unsigned Range: 0 to 4,294,967,295 L Info: Maximum length of number L Value: 0 – 11 digits. L Requirement: Not Required
`BIGINT(L)`	Signing: signed or unsigned Signed Range: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 Unsigned Range: 0 to 18,446,744,073,709,551,615 L Info: Maximum length of number L Value: 0 – 20 digits. L Requirement: Not Required

Numbers with decimals

FLOAT(T,D) Signing: signed
Defining T&D: Optional.
Default: 10,2
T: Total number of digits in float, including decimal places.
D: Number of decimal places in float, max precision of 24 digits.
Example: FLOAT(10,5) = 01234.56789

`FLOAT(T,D)`	Signing: signed Defining T&D: Optional. Default: 10,2 T: Total number of digits in float, including decimal places. D: Number of decimal places in float, max precision of 24 digits. Example: FLOAT(10,5) = 01234.56789
`DOUBLE(T,D)` `REAL(T,D)`	Signing: signed Defining T&D: Optional. Default: 16,4 T: Total number of digits in float, including decimal places. D: Number of decimal places in float, max precision of 53 digits. Example: FLOAT(20,10) = 0123456789.0123456789
`DECIMAL(T,D)` `NUMERIC(T,D)`	Signing: signed Defining T&D: Required. T: Total number of digits in float, including decimal places. D: Number of decimal places in float. Note: Each decimal = 1 bite.

DOUBLE(T,D)

REAL(T,D)

Signing: signed
Defining T&D: Optional.
Default: 16,4
T: Total number of digits in float, including decimal places.
D: Number of decimal places in float, max precision of 53 digits.
Example: FLOAT(20,10) = 0123456789.0123456789

DECIMAL(T,D)

NUMERIC(T,D)

Signing: signed
Defining T&D: Required.
T: Total number of digits in float, including decimal places.
D: Number of decimal places in float.
Note: Each decimal = 1 bite.

Dates and Times

`DATE`	Format: YYYY-MM-DD Range: 1000-01-01 to 9999-12-31
`TIME`	Format: HH:MM:SS Note: Hours are stored in 24 hour format.
`YEAR(L)`	L: Length of year, 2 or 4 Format: YY or YYYY YYYY Range: 1901 – 2155 YY Range: 70 – 69 (1970 – 2069) Default: YYYY
`DATETIME`	Format: YYYY-MM-DD HH:MM:SS Range: 1000-01-01 00:00:00 to 9999-12-31 23:59:59 Note: Hours are stored in 24 hour format.
`TIMESTAMP`	Format: YYYY-MM-DD HH:MM:SS Range: 1970-01-01 00:00:01 to 2038-01-09 03:14:07 Alternate Formats: YYYYMMDDHHMISS or YYMMDDHHMISS or YYYYMMDD or YYMMDD Note: In an INSERT or UPDATE query, TIMESTAMP automatically sets itself to the current date and time.