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 grew out of that need.

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
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.

Leave a Reply

Your email address will not be published.

*