| Data type | Storage size | Description | Permissible values |
| TINYINT(M) | 1 byte | A very small integer. M indicates the maximum display width for integer types. |
The signed range is -128 to 127. The unsigned range is 0 to 255. |
| Example: tinyint(2) can hold an integer up to 99 | |||
| SMALLINT(M) | 2 bytes | A small integer. M indicates the maximum display width for integer types. |
The signed range is -32768 to 32767. The unsigned range is 0 to 65535. |
| Example: smallint(4) can hold an integer up to 9999 | |||
| MEDIUMINT(M) | 3 bytes | A medium-sized integer. M indicates the maximum display width for integer types. |
The signed range is -8388608 to 8388607. The unsigned range is 0 to 16777215. |
| Example: mediumint(6) can hold an integer up to 999999 | |||
| INT(M) | 4 bytes | A normal-size integer. M indicates the maximum display width for integer types. |
The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295. |
| Example: int(8) can hold an integer up to 99999999 | |||
| BIGINT(M) | 8 bytes | A large integer. M indicates the maximum display width for integer types. |
The signed range is -9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615. |
| Example: bigint(12) can hold an integer up to 999999999999 | |||
| BIT(M) | (M+7)/8 bytes M indicates the maximum display width for integer types. |
A bit-value type. M indicates the number of bits per value. The default is 1 if M is omitted. | From 1 to 64 |
| Example: bit(2) can hold a bit sequence up to 11 | |||
| BOOL, BOOLEAN | 1 byte | These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true |
0-1 |
| Example: bool can hold an integer up to 1 | |||
| DECIMAL(M,D) | Each multiple of nine digits requires 4 bytes | A packed "exact" fixed-point number. M is the total number of digits (the precision) and D is the number of digits after the decimal point (the scale). The decimal point and (for negative numbers) the - sign are not counted in M. If D is 0, values have no decimal point or fractional part. |
The maximum number of digits (M) for DECIMAL is 65. The maximum number of supported decimals (D) is 30. If D is omitted, the default is 0. If M is omitted, the default is 10. Each multiple of nine digits requires 4 bytes, and any remaining digits left over require some fraction of 4 bytes. The storage required for remaining digits is given by the following: 0 digit: 0 byte; 1-2 digits: 1 byte; 3-4 digits: 2 bytes; 5-6 digits: 3 bytes; 7-9 digits: 4 bytes |
| Example: decimal(12,3) can hold a fixed-point number up to 999999999.999 | |||
| FLOAT(M,D) | 4 bytes | A small (single-precision) floating-point number. M is the total number of digits and D is the number of digits following the decimal point. If M and D are omitted, values are stored to the limits permitted by the hardware. |
Permissible values are -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38. These are the theoretical limits, based on the IEEE standard. UNSIGNED, if specified, disallows negative values. |
| Example: float(4,2) can hold a floating-point number up to 99.99 | |||
| DOUBLE(M,D) | 8 bytes | A normal-size (double-precision) floating-point number. M is the total number of digits and D is the number of digits following the decimal point. If M and D are omitted, values are stored to the limits permitted by the hardware. |
Permissible values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308. These are the theoretical limits, based on the IEEE standard. UNSIGNED, if specified, disallows negative values. |
| Example: double(8,2) can hold a floating-point number up to 999999.99 |
| Data type | Storage size | Description | Permissible values |
| CHAR(M) | M * w bytes | A fixed-length string that is always right-padded with spaces to the specified length when stored. M represents the column length in characters. w is the number of bytes required for the maximum-length character in the character set. |
The range of M is 0 to 255. If M is omitted, the length is 1. |
| Example: char(5) can be a text like abcde | |||
| VARCHAR(M) | L + 1 or L + 2 bytes | A variable-length string. M represents the maximum column length in characters. MySQL stores VARCHAR values as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A VARCHAR column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes. L + 1 bytes if column values require 0 - 255 bytes, L + 2 bytes if values may require more than 255 bytes |
The range of M is 0 to 65,535. |
| Example: varchar(6) can be a text like abcdef | |||
| BINARY(M) | M bytes | The BINARY type is similar to the CHAR type, but stores binary byte strings rather than nonbinary character strings. M represents the column length in bytes. |
0-255 |
| Example: binary(2) can be a blob like 0x12 | |||
| VARBINARY(M) | L + 1 or L + 2 bytes | The VARBINARY type is similar to the VARCHAR type, but stores binary byte strings rather than nonbinary character strings. M represents the maximum column length in bytes. L + 1 bytes if column values require 0 - 255 bytes, L + 2 bytes if values may require more than 255 bytes |
0-65,535 |
| Example: varbinary(4) can be a blob like 0x1200 | |||
| TINYBLOB | L + 1 bytes | Each TINYBLOB value is stored using a 1-byte length prefix that indicates the number of bytes in the value. | A BLOB column with a maximum length of 255 (28 - 1) bytes. |
| TINYTEXT | L + 1 bytes | Each TINYTEXT value is stored using a 1-byte length prefix that indicates the number of bytes in the value. | A TEXT column with a maximum length of 255 (28 - 1) characters. |
| Example: tinytext can be a text like abcdefg... | |||
| BLOB(M) | L + 2 bytes | Each BLOB value is stored using a 2-byte length prefix that indicates the number of bytes in the value. An optional length M can be given for this type. If this is done, MySQL creates the column as the smallest BLOB type large enough to hold values M bytes long. |
A BLOB column with a maximum length of 65,535 (216 - 1) bytes. |
| TEXT(M) | L + 2 bytes | The effective maximum length is less if the value contains multibyte characters. Each TEXT value is stored using a 2-byte length prefix that indicates the number of bytes in the value. An optional length M can be given for this type. If this is done, MySQL creates the column as the smallest TEXT type large enough to hold values M characters long. |
A TEXT column with a maximum length of 65,535 (216 - 1) characters. |
| Example: text can be a text like abcdefg... | |||
| MEDIUMBLOB | L + 3 bytes | Each MEDIUMBLOB value is stored using a 3-byte length prefix that indicates the number of bytes in the value. | A BLOB column with a maximum length of 16,777,215 (224 - 1) bytes. |
| MEDIUMTEXT | L + 3 bytes | Each MEDIUMTEXT value is stored using a 3-byte length prefix that indicates the number of bytes in the value. | A TEXT column with a maximum length of 16,777,215 (224 - 1) bytes. |
| Example: mediumtext can be a text like abcdefg... | |||
| LONGBLOB | L + 4 bytes | Each LONGBLOB value is stored using a 4-byte length prefix that indicates the number of bytes in the value. | A BLOB column with a maximum length of 4,294,967,295 or 4GB (232 - 1) bytes. |
| LONGTEXT | L + 4 bytes | Each LONGTEXT value is stored using a 4-byte length prefix that indicates the number of bytes in the value. | A TEXT column with a maximum length of 4,294,967,295 or 4GB (232 - 1) characters. |
| Example: longtext can be a text like abcdefg... | |||
| ENUM | 1 or 2 bytes | An enumeration. A string object that can have only one value 1 or 2 bytes, depending on the number of enumeration values. |
An ENUM column can have a maximum of 65,535 distinct elements. |
| SET | 1, 2, 3, 4, or 8 bytes | A set. A string object that can have zero or more values. 1, 2, 3, 4, or 8 bytes, depending on the number of set members. |
A SET column can have a maximum of 64 distinct members. |
| Data type | Storage size | Description | Permissible values |
| DATE | 3 bytes | A date. MySQL displays DATE values in 'YYYY-MM-DD' format. |
The supported range is '1000-01-01' to '9999-12-31'. |
| Example: date can be a date like 2017-01-01 | |||
| DATETIME(fsp) | 8 bytes | A date and time combination. MySQL displays DATETIME values in 'YYYY-MM-DD HH:MM:SS[.fraction]'. An optional fsp value in the range from 0 to 6 may be given to specify fractional seconds precision. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0. |
The supported range is '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999'. |
| Example: datetime(3) can be a date, time and fraction like 2017-01-01 12:12:12.123 | |||
| TIMESTAMP(fsp) | 4 bytes | A timestamp. TIMESTAMP values are stored as the number of seconds since the epoch ('1970-01-01 00:00:00' UTC). An optional fsp value in the range from 0 to 6 may be given to specify fractional seconds precision. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0. |
The range is '1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.999999' UTC. |
| Example: timestamp(4) can be a date, time and fraction like 2017-01-01 12:12:12.1234 | |||
| TIME(fsp) | 3 bytes | A time. MySQL displays TIME values in 'HH:MM:SS[.fraction]' format An optional fsp value in the range from 0 to 6 may be given to specify fractional seconds precision. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0. |
The range is '-838:59:59.000000' to '838:59:59.000000'. |
| Example: time(5) can be a time and fraction like 12:12:12.12345 | |||
| YEAR(4) | 1 byte | A year in four-digit format. MySQL displays YEAR values in YYYY format, but permits assignment of values to YEAR columns using either strings or numbers. |
Values display as 1901 to 2155, and 0000. |
| Example: year can be a year like 2017 |
| Input string | Storage size |
| 0 byte | |
| The above storage size is calculated for utf8_general collation. | |
| Specify column types you wish to use in the table | ||
| Column type | Storage size | |
| Expected number of rows in the table: | ||
| Approximate table storage size: | 0 byte | |