MySQL field type description _php tips

MySQL supports a large number of column types, which can be grouped into 3 categories: numeric types, date and time types, and string (character) types. 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. The overview is intended to be simplified, and more detailed instructions should take into account additional information about a specific column type, such as the allowable format for which you can specify a value.

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 it should be less than M-2.
The brackets ("[" and "]") indicate the part of the optional type modifier.

Note that if you specify a zerofill,mysql will automatically increase the unsigned property for 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 size 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 a signed large integer greater than 9223372036854775807 (63 bits), except for the bit function! Note that when two parameters are integer,-, +, and * will use the bigint operation! This means that if you multiply by 2 large integers (or from functions that return integers), you can get unexpected results if the result is greater than 9223372036854775807. A floating-point number, which cannot be unsigned, can be <= 24 for a single, double-precision float, and between 25 and 53, these types such as float and double are described immediately below. Float (X) has the same range as float and double, but the display dimensions and decimal places 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 display width and d is the number of digits. A 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 allowable values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308. M is the width of the display and D is the decimal digit. Double or float (x) with no arguments (< = 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 uncompressed (unpack) floating-point number. cannot be unsigned. Behaves like a char column: "Uncompressed" means that the number is stored as a string, and each digit of the value uses one character. decimal point, and for negative numbers, the "-" symbol is not evaluated in M. If D is 0, the value will not have a decimal point or fractional fraction. 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. The scope of support is ' 1000-01-01 ' to ' 9999-12-31 '. MySQL Displays the date value in ' YYYY-MM-DD ' format, but allows you to assign the value to the Date column using a string or a number. Note: The Assign initial value can be used in PHP now ().

Datetime
A combination of dates and times. The range of support is ' 1000-01-01 00:00:00 ' to ' 9999-12-31 23:59:59 '. MySQL displays datetime values in the ' 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 range is ' 1970-01-01 00:00:00 ' to a certain time of 2037. MySQL Displays the timestamp value 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. 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 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 the time value in ' HH:MM:SS ' format, but allows you to assign the value to the time column using a string or a number.

year[(2|4)]
The year of a 2-or 4-bit number format (the default is 4 bits). The allowable 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 according to the default character set in a case-insensitive manner, unless a binary keyword is given. National CHAR (short form nchar) is an ANSI-SQL way to define that the CHAR column 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 removed (this is different from the ANSI SQL specification). The range of M is 1 ~ 255 characters. VarChar values are sorted and compared according to the default character set in a case-insensitive manner, unless a 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
For processing binary files, upload files can be used.

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 that has only one value, which is 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 up to 64 members.