(reproduced) MySQL data type: TINYINT, SMALLINT, Mediumint, INT, Integer, etc. field type differences

(Transferred from Http://3y.uu456.com/bp-f088f739376baf1ffc4fad40-1.html)

MySQL supports a large number of column types, which can be divided into 3 categories: numeric type, date and time type, string (character) type.

This section first gives an overview of the available types, summarizes the storage requirements for each column type, and then provides a more detailed description of the nature of the types in each class. An overview is intentionally simplified, and more detailed instructions should take into account additional information about a particular column type, such as the allowable format for which you can specify a value. The following code letters are used in the description:

M: Indicates the maximum display size. The largest legal display size is 255.

D: Applies to floating-point types and indicates the number of digits following the decimal point. The maximum possible value is 30, but should be less than M-2.

Square brackets ("[" and "]") indicate the part of the optional type modifier.

Note that if you specify one for Zerofill,mysql, the unsigned property will be automatically added for that column.

tinyint[(M)] [UNSIGNED] [Zerofill]

A very small integer. The signed range is 128 to 127, the unsigned range is 0 to 255.

smallint[(M)] [UNSIGNED] [Zerofill]

A small integer. The signed range is 32768 to 32767, the unsigned range is 0 to 65535.

mediumint[(M)] [UNSIGNED] [Zerofill]

A medium-sized integer. The signed range is 8388608 to 8388607, the unsigned range is 0 to 16777215.

int[(M)] [UNSIGNED] [Zerofill]

A normal size integer. The signed range is 2147483648 to 2147483647, 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, the unsigned range is 0 to 18446744073709551615.

Note that all arithmetic operations are done with a signed bigint or double value, so you should not use a signed large integer greater than 9223372036854775807 (63-bit), except for the bit function! Note that when two parameters are integer values,-, +, and * will use the bigint operation! This means that if you multiply by 2 large integers (or from a function that returns integers), you can get unexpected results if the result is greater than 9223372036854775807. A floating-point number, which cannot be unsigned, is a single-precision floating point, whose precision can be <=24, to a double-precision floating point, between 25 and 53, these types such as float and double type are described below immediately. Float (X) has the same range as the corresponding float and double, but the display dimensions and scale are undefined. In MySQL3.23, this is a real 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]

A small (single-precision) floating-point number. cannot be unsigned. The allowable values are -3.402823466E+38 to -1.175494351e-38,0 and 1.175494351E-38 to 3.402823466E+38. M is the number of digits that display the width and d is a decimal. Float with no parameters or a parameter with <24 represents a single precision floating point number.

double[(m,d)] [Zerofill]

A normal-size (double-precision) floating-point number. cannot be unsigned. The allowed values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308. M is the display width and d is the number of decimal digits. A double or float (x) without a parameter (< = x < = 53) represents a two-precision floating-point number.

　　

DOUBLE precision[(m,d)] [Zerofill]

real[(m,d)] [Zerofill]

These are double synonyms.

decimal[(M[,d]) [Zerofill]

A floating-point number that is uncompressed (unpack). cannot be unsigned. Behaves like a char column: "Uncompressed" means that the number is stored as a string, and each bit of the value uses one character. decimal point, and for negative numbers, the "-" symbol is not calculated in M. If D is 0, the value will have no decimal point or fractional 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 MySQL3.22, the M parameter includes a symbol and a decimal point.

NUMERIC (M,D) [Zerofill]

This is a synonym for decimal.

DATE

A date. What is the scope of support? 1000-01-01′ to 9999-12-31′. MySQL with? YYYY-MM-DD format to display the date value, but allows you to assign a value to a date column using a string or a number.

Datetime

A date and time combination. What is the scope of support? 1000-01-01 00:00:00′ to 9999-12-31 23:59:59′. MySQL with? YYYY-MM-DD HH:MM:SS? format to display datetime values, but allows you to assign a value to a datetime column by using a string or a number.

　　

timestamp[(M)]

A time stamp. The range is 00:00:00′ 1970-01-01 to 2037 sometime. MySQL Displays the timestamp value in Yyyymmddhhmmss, Yymmddhhmmss, YYYYMMDD, or YYMMDD format, depending on whether M is 14 (or omitted), 12, 8, or 6, However, you are allowed to assign a value to the timestamp column using a string or a number. A timestamp column is useful for recording the date and time of an insert or update operation, because if you do not 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 a null value.

Time

A time. The range is -838:59:59′ to 838:59:59′. MySQL with? HH:MM:SS format to display the time value, but allows you to assign a value to the time column using a string or a number.

year[(2|4)]

A 2-or 4-digit year (the default is 4-bit). The allowed values are 1901 to 2155, and 0000 (4-bit 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 numeric value to the year column. (The year type is the new type in MySQL3.22.) ）

CHAR (M) [BINARY]

A fixed-length string that, when stored, always fills the right to the specified length with a space. The range of M is 1 ~ 255 characters. When the value is retrieved, the trailing space is deleted. Char values are sorted and compared in a case-insensitive manner based on the default character set, unless binary keywords are given. National char (short form NCHAR) is an ANSI SQL way to define a CHAR column that should use the default character set. This is the default for MySQL. Char is an abbreviation for character.

[National] VARCHAR (M) [BINARY]

A variable-length string. Note: When the value is stored, the trailing spaces are deleted (this differs from the ANSI SQL specification). The range of M is 1 ~ 255 characters. The varchar values are sorted and compared in a case-insensitive manner based on the default character set, unless the binary keyword value is given. VARCHAR is an abbreviation for 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 selected from the Value list? value1′,? value2′, ..., or null. An enum can have a maximum of 65535 different values.

SET (? value1′,?value2′,...)

A collection. A string object that can have 0 or more values, each of which must be from a list of values? value1′,? value2′, ... Elected. A set can have a maximum of 64 members.

(reproduced) MySQL data type: TINYINT, SMALLINT, Mediumint, INT, Integer, etc. field type differences