MySQL field type description _ MySQL-mysql tutorial

MySQL supports a large number of column types, which can be divided into three types: number type, date and time type, and string (character) type. This section first provides an overview of available types, summarizes the storage requirements for each column type, and then provides a more detailed description of the type nature in each class. The overview is intended to be simplified. a more detailed description should take into account the support for a large number of column types in the attached letter MySQL for specific column types. it can be divided into three categories: numeric, date, and time, and string (character) types. This section first provides an overview of available types, summarizes the storage requirements for each column type, and then provides a more detailed description of the type nature in each class. The overview is intended to be simplified. more detailed descriptions should take into account additional information about specific column types, such as the allowed format for which you can specify values.

The column types supported by MySQL are listed below. The following code is used to describe:

M
Specifies the maximum display size. The Max valid display size is 255.
D
Applicable to the floating point type and the number of digits following the decimal point. The maximum possible value is 30, but should not be greater than the M-2.
Square brackets ("[" and "]") indicate the part of the optional type modifier.

Note: If you specify ZEROFILL, MySQL automatically adds the UNSIGNED attribute to this column.

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

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

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

INT [(M)] [UNSIGNED] [ZEROFILL]
A normal integer. 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]

A large integer. The signed range is-9223372036854775808 to 9223372036854775807, and the unsigned range is 0

18446744073709551615. Note that all arithmetic operations are completed with signed BIGINT or DOUBLE values. Therefore, you should not use signed big integers greater than 9223372036854775807 (63 bits), except for bitwise functions! Note that when the two parameters are INTEGER values,-, +, and * use BIGINT operations! This means that if you take two big integers (or from the function that returns integers) and the result is greater than 9223372036854775807, you can get unexpected results. A floating-point number cannot be unsigned. for a single-precision floating-point number, its precision can be <= 24. for a double-precision floating-point number, it is between 25 and 53, these types, such as FLOAT and DOUBLE, are immediately described below. FLOAT (X) has the same FLOAT and DOUBLE ranges, but the display size and decimal places are undefined. In MySQL3.23, this is a real floating point value. In earlier MySQL versions, FLOAT (precision) always has two decimal places. This syntax is provided for ODBC compatibility.

FLOAT [(M, D)] [ZEROFILL]
A small (single precision) floating point number. It cannot be unsigned. The allowed values are-3.402823466E + 38 to-1.175494351E-38,0 and 1.175494351E-38 to 3.402823466E + 38. M indicates the display width, and D indicates the decimal digits. FLOAT without parameters or a parameter with <24 represents a single precision floating point number.

DOUBLE [(M, D)] [ZEROFILL]
A normal-sized (double-precision) floating point number. It cannot be unsigned. The allowed values are-1.7976931348623157E + 308 to-2.225074255072014e-308, 0, and 2.225074255072014e-308 to 1.7976931348623157E + 308. M indicates the display width, and D indicates the number of decimal places. A double or FLOAT (X) (25 <= X <= 53) without a parameter represents a DOUBLE-precision floating point number.

Double precision [(M, D)] [ZEROFILL]
　

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

DECIMAL [(M [, D])] [ZEROFILL]
Unpack floating point number. It cannot be unsigned. The behavior is like a CHAR column: "uncompress" means that the number is stored as a string, and each bit of the value uses one character. Decimal point. for negative numbers, the "-" symbol is not calculated in M. If D is 0, there will be no decimal point or decimal part. The maximum range of a DECIMAL value is the same as that of a DOUBLE value. However, for a given DECIMAL column, the actual range can be limited through the selection of M and D. If D is omitted, it is set to 0. If M is saved, it is set to 10. Note: in MySQL3.22, the M parameter includes the symbol and the decimal point.

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

DATE
A date. The supported range is '2017-01-01 'to '2017-12-31 '. MySQL displays the DATE value in 'yyyy-MM-DD 'format, but allows you to assign the value to the DATE column using strings or numbers.

DATETIME
A combination of dates and times. The supported range is '2017-01-01 00:00:00 'to '2017-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)]
A time stamp. The value range is '2017-01-01 00:00:00 'to a certain time on January 1, 1970. MySQL uses YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD, or YYMMDD to display the TIMESTAMP value, depending on whether M is 14 (or omitted), 12, 8, or 6, however, you can use strings or numbers to assign values to the TIMESTAMP column. A timestamp column is useful for recording the date and time of an INSERT or UPDATE operation, because if you do not assign a value to it yourself, it is automatically set to the date and time of the most recent operation. You can assign it a NULL value to set it to the current date and time.
TIME
A time. The value range is '-838: 59: 59' to '2014: 59: 59 '. MySQL displays the TIME value in 'hh: MM: SS' format, but allows you to assign the value to the TIME column using a string or number.

YEAR [(2 | 4)]
A year in the format of two or four digits (4 digits by default ). The allowed values are 1901 to 2155, and 0000 (4-digit year format), if you use 2 bits, 1970-2069 (70-69 ). MySQL displays the YEAR value in YYYY format, but allows you to assign a string or number value to the YEAR column. (YEAR is a new type in MySQL3.22 .)

CHAR (M) [BINARY]
A fixed-length string, when stored, is always filled with spaces to the right to the specified length. The range of M is 1 ~ It can contain 255 characters. When the value is retrieved, the tail of the space is deleted. CHAR values are sorted and compared in a case that is not distinguished by the default character set, unless BINARY keywords are given. National char (short form NCHAR) is an ansi SQL method to define CHAR columns should use the default character set. This is the default value of MySQL. CHAR is an abbreviation of CHARACTER.

[NATIONAL] VARCHAR (M) [BINARY]
A variable-length string. Note: When the value is stored, spaces at the end are deleted (different from the ansi SQL specification ). The range of M is 1 ~ It can contain 255 characters. VARCHAR values are sorted and compared based on the default character set in uppercase/lowercase, unless BINARY keyword values are given. VARCHAR is an abbreviation of character varying.

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

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

MEDIUMBLOB
　
MEDIUMTEXT
A blob or TEXT column with a maximum length of 16777215 (2 ^ 24-1) characters.
LONGBLOB
　
LONGTEXT
A 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. this value type is set to 'value1 ', 'value2',..., or NULL. An ENUM can have a maximum of 65535 different values.

SET ('value1', 'value2 ',...)
A collection. A string object with zero or multiple values, each of which must be selected from the value list 'value1 ', 'value2. A set can have up to 64 members.