A more detailed description of the MySQL field type

  • 2020-05-07 20:33:13
  • OfStack

The column types supported by MySQL are listed below. The following code letters are used in the description:

M
Indicate the maximum display size. The maximum legal display size is 255.
D
Applies to floating point types and indicates the number of digits following the decimal point in decimal place. The most likely value is 30, but it should not be greater than M-2.
The square brackets (" [" and "] ") indicate the part of the optional type modifier.

Note that if you specify one as ZEROFILL, MySQL automatically adds the UNSIGNED attribute to the column.

TINYINT[(M)] [UNSIGNED] [ZEROFILL]
One very small integer. The signed range is -128 to 127, and the unsigned range is 0 to 255.


SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
One small integer. The signed range is -32768 to 32767, and the unsigned range is 0 to 65535.

MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
One medium integer. The signed range is -8388608 to 8388607, and the unsigned range is 0 to 16777215.

INT[(M)] [UNSIGNED] [ZEROFILL]
1 integer of normal size. The signed range is -2147483648 to 2147483647, and the unsigned range is 0 to 4294967295.

INTEGER[(M)] [UNSIGNED] [ZEROFILL]
This is a synonym for INT.

BIGINT[(M)] [UNSIGNED] [ZEROFILL]

One big integer. The signed range is -9223372036854775808 to 9223372036854775807, and the unsigned range is 0 to

18446744073709551615. Note that all arithmetic operations are performed with signed BIGINT or DOUBLE values, so you should not use signed large integers greater than 9223372036854775807 (63 bits), except for bit functions! Note that when the two parameters are INTEGER values, the -, +, and * operations use BIGINT! This means that if you multiply two large integers (or from functions that return integers), you can get unexpected results if the result is greater than 9223372036854775807. One floating point number, cannot be unsigned, for one single precision floating point number, the precision can be < =24, for a double-precision floating point number, is between 25 and 53. These types such as FLOAT and DOUBLE are described below. FLOAT(X) has the same range as FLOAT and DOUBLE, but the display size and decimal number are undefined. In MySQL 3.23, this is a true floating point value. In earlier versions of MySQL, FLOAT(precision) always had 2 decimal places. This syntax is provided for ODBC compatibility.

FLOAT[(M,D)] [ZEROFILL]
One small (single precision) floating point number. You can't be unsigned. The allowable values are -3.402823466E+38 to -1.175494351 E-38, 0 and 1.175494351 E-38 to 3.402823466E+38. M is the display width and D is the number of decimal places. No parameters for FLOAT contingent < One parameter of 24 represents one single precision floating point number.

DOUBLE[(M,D)] [ZEROFILL]
1 normal size (double precision) floating point number. You can't be unsigned. The allowable values are -1.7976931348623157E+308 to -2.2250738585072014 E-308, 0 and 2.2250738585072014 E-308 to 1.7976931348623157E+308. M is the display width and D is the decimal number. DOUBLE or FLOAT(X) without 1 parameter (25) < = X < = 53) represents one double precision floating point number.

DOUBLE PRECISION[(M,D)] [ZEROFILL]


REAL[(M,D)] [ZEROFILL]
These are synonyms for DOUBLE.

DECIMAL[(M[,D])] [ZEROFILL]
1 uncompressed (unpack) floating point number. You can't be unsigned. Behaves like an CHAR column: "uncompressed" means that the number is stored as a string, with one character per bit of the value. The decimal point, and for negative Numbers, the "-" sign is not evaluated in M. If D is 0, the value will have no decimal point or decimal part. The maximum range of DECIMAL values is the same as DOUBLE, but for a given DECIMAL column, the actual range can be limited by the choice of M and D. If D is omitted, it is set to 0. If M is omitted, it is set to 10. Note that in MySQL 3.22, the M parameter includes symbols and decimal points.

NUMERIC(M,D) [ZEROFILL]
This is a synonym for DECIMAL.

DATE
One date. The supported range is '1000-01-01' to '9999-12-31'. MySQL displays DATE values in 'YYYY-MM-DD' format, but allows you to assign values to DATE columns using strings or Numbers.

DATETIME
1 combination of date and time. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. MySQL displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format, but allows you to assign values to DATETIME columns using strings or Numbers.

TIMESTAMP[(M)]
1 time stamp. The range is '1970-01-01 00:00:00' to sometime in 2037. MySQL displays TIMESTAMP values in YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD, or YYMMDD format, depending on whether M is 14 (or omitted), 12, 8, or 6, but allows you to assign values to TIMESTAMP columns using strings or Numbers. An TIMESTAMP column is useful for recording the date and time of an INSERT or UPDATE operation, because if you don't assign it yourself, it is automatically set to the date and time of the most recent operation. You can set it to the current date and time by assigning it an NULL value.
TIME
One time. The range is' -888:59:59 'to' 888:59:59 '. MySQL displays TIME values in 'HH:MM:SS' format, but allows you to assign values to TIME columns using strings or Numbers.

YEAR[(2|4)]
Year in 2 or 4 digit format (default is 4 digits). The allowed values are 1901 to 2155, and 0000 (4-bit year format), if you use 2-bit, 1970-2069(70-69). MySQL displays YEAR values in YYYY format, but allows you to assign values using strings or Numbers to YEAR columns. (the YEAR type is new in MySQL 3.22.)

CHAR(M) [BINARY]
1 fixed-length string, when stored, always fills the right hand side with space to the specified length. M ranges from 1 to 255 characters. When the value is retrieved, the space tail is deleted. CHAR values are sorted and compared case-insensitive according to the default character set, unless BINARY keywords are given. NATIONAL CHAR (short form NCHAR) is the ANSI SQL way to define CHAR columns should use the default character set. This is the default for MySQL. CHAR is an abbreviation of CHARACTER.

[NATIONAL] VARCHAR(M) [BINARY]
1 variable length string. Note: when the value is stored, the space at the end is removed (this is different from the ANSI SQL specification). M ranges from 1 to 255 characters. The VARCHAR values are sorted and compared case-insensitive according to the default character set, unless the BINARY keyword values are given. VARCHAR is an abbreviation of CHARACTER VARYING1.

TINYBLOB

TINYTEXT
1 BLOB or TEXT column with a maximum length of 255(2^8-1) characters.
BLOB


TEXT
1 BLOB or TEXT column with a maximum length of 65535(2^16-1) characters.

MEDIUMBLOB

MEDIUMTEXT
1 BLOB or TEXT column with a maximum length of 16777215(2^24-1) characters.
LONGBLOB

LONGTEXT
1 BLOB or TEXT column with a maximum length of 4294967295(2^32-1) characters.

ENUM('value1','value2',...)
Enumeration. A string object with only one value, which is selected from the list of values 'value1', 'value2'... , or NULL. One ENUM can have 65535 different values at most.

SET('value1','value2',...)
One set. 1 string object that can have zero or more values, each of which must be from the list of values 'value1', 'value2'... Chosen. An SET can have up to 64 members.

Related articles: