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 |
|---|---|
DOUBLE(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)
| 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. |