mysql field type description_PHP tutorial-PHP Tutorial-php.cn

mysql field type description_PHP tutorial

WBOY

Release： 2016-07-21 15:54:49

Original

663 people have browsed it

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

The column types supported by MySQL are listed below. 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 no larger than M-2.
Square brackets ("[" and "]") indicate parts of optional type modifiers.

Note that if you specify a ZEROFILL, MySQL will automatically add the UNSIGNED attribute to the column.

TINYINT[(M)] [UNSIGNED] [ZEROFILL]
A very 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]
A medium-sized integer. The signed range is -8388608 to 8388607, and the unsigned range is 0 to 16777215.

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

18446744073709551615. Note that all arithmetic operations are done with signed BIGINT or DOUBLE values, so you should not use signed big integers larger than 9223372036854775807 (63 bits), except for bit functions! Note that when the two parameters are INTEGER values, -, + and * will use the BIGINT operation! This means that if you multiply 2 large integers (or from a function that returns an integer), you can get unexpected results if the result is greater than 9223372036854775807. A floating-point number cannot be unsigned. For a single-precision floating-point number, its precision can be
FLOAT[(M,D)] [ZEROFILL]
A small (single precision) floating point number. Cannot be unsigned. Allowed values are -3.402823466E+38 to -1.175494351E-38, 0 and 1.175494351E-38 to 3.402823466E+38. M is the display width and D is the number of decimal places. FLOAT with no arguments or with one argument
DOUBLE[(M,D)] [ZEROFILL]
A normal-sized (double precision) floating point number. Cannot be unsigned. 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 places. DOUBLE or FLOAT(X) without an argument (25 < = X < = 53) represents a double precision floating point number.

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

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

DECIMAL[(M[,D])] [ZEROFILL]
An unpacked floating point number. Cannot be unsigned. Behaves like a CHAR column: "uncompressed" means that the number is stored as a string, using one character for each 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 for DOUBLE, but for a given DECIMAL column, the actual range can be limited by the M and D selections. 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 the sign and decimal point.

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

DATE
A 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 use strings or numbers to assign values to DATE columns. Note: You can use now() in PHP to assign an initial value.

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

TIMESTAMP[(M)]
A timestamp. The range is '1970-01-01 00:00:00' to sometime in 2037.MySQL displays TIMESTAMP values in the format YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD, or YYMMDD, depending on whether M is 14 (or omitted), 12, 8, or 6, but allows you to 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 don't assign a value to 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 displays TIME values in the format 'HH:MM:SS', but allows you to use strings or numbers to assign values to the TIME column.

YEAR[(2|4)]
A year in 2 or 4 digit format (default is 4 digits). Allowed values are 1901 to 2155, and 0000 (4-digit year format), if you use 2 digits, 1970-2069 (70-69). MySQL displays YEAR values in YYYY format, but allows you to assign string or numeric values to YEAR columns. (The YEAR type is a new type in MySQL 3.22.)

CHAR(M) [BINARY]
A fixed-length string, when stored, is always filled with spaces on the right to the specified length. The range of M is 1 ~ 255 characters. When the value is retrieved, trailing spaces are removed. CHAR values are sorted and compared in a case-insensitive manner according to the default character set, unless the BINARY keyword is given. NATIONAL CHAR (short form NCHAR) is the ANSI SQL way of defining that a CHAR column should use the default character set. This is MySQL's default. CHAR is an abbreviation for CHARACTER.

[NATIONAL] VARCHAR(M) [BINARY]
A variable-length string. Note: Trailing spaces are removed when the value is stored (this differs from the ANSI SQL specification). The range of M is 1 ~ 255 characters. VARCHAR values are sorted and compared in a case-insensitive manner according to the default character set, unless the BINARY keyword value is 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
is used to process binary files and can be used to upload files.

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 a single value selected from the list of values 'value1', 'value2', ..., or NULL. An ENUM can have up to 65535 different values.

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