Mysql integer type enumeration field operation_mysql-MySQL tutorial

Mysql integer-type enumeration field operation-BIT [(M)]

Bit field type. M indicates the number of digits of each value, ranging from 1 to 64. If M is omitted, the default value is 1.

· TINYINT [(M)] [UNSIGNED] [ZEROFILL]

A small integer. The signed range is-128 to 127. The unsigned range is 0 to 255.

· BOOL, BOOLEAN

Is a synonym for TINYINT (1. The zero value is considered false. The non-zero value is true.

In the future, full Boolean processing will be introduced based on standard SQL.

· 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]

An integer of medium size. The signed range is-8388608 to 8388607. The unsigned range is 0 to 16777215.

· INT [(M)] [UNSIGNED] [ZEROFILL]

An integer of the normal size. 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.

The following content of the BIGINT column should be clear:

O use signed BIGINT or DOUBLE values for all algorithms. Therefore, except for bitwise functions, you should not use an unsigned big integer greater than 9223372036854775807 (63 bits! If this is done, the last few digits in the result may be incorrect, which is caused by rounding the BIGINT value to DOUBLE.

MySQL can process BIGINT in the following cases:

§ When an integer is used to save a large unsigned value in a BIGINT column.

§ In MIN (col_name) or MAX (col_name), col_name indicates the BIGINT column.

§ When the operator (+,-, *, and so on) is used and both operands are integers.

O can always use a string to save a strict integer in the BIGINT column. In this case, MySQL executes string-numeric conversion, during which there is no double-precision representation.

O when both operands are integer values, The-, +, and * operators use the BIGINT algorithm. This indicates that if two big integers (or functions from Return integers) are multiplied, unexpected results will be obtained when the result is greater than 9223372036854775807.

· FLOAT [(M, D)] [UNSIGNED] [ZEROFILL]

Small (single-precision) floating point number. Valid values:-3.402823466E + 38 to-1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E + 38. These are theoretical limitations based on IEEE standards. The actual range may be slightly smaller depending on the hardware or operating system.

M is the vertical digits of decimal places, and D is the digits after the decimal places. If M and D are omitted, the values are saved according to the restrictions allowed by the hardware. The single-precision floating point number is precise to about 7 decimal places.

If UNSIGNED is specified, a negative value is not allowed.

Floating point numbers may encounter unexpected problems, because all calculations in MySQL are completed with double precision.

· DOUBLE [(M, D)] [UNSIGNED] [ZEROFILL]

Common (double-precision) floating point number. The allowed values are-1.7976931348623157E + 308 to-2.225074255072014e-308, 0, and 2.225074255072014e-308 to 1.7976931348623157E + 308. These are theoretical limitations based on IEEE standards. The actual range may be slightly smaller depending on the hardware or operating system.

M is the total number of decimal places, and D is the number of digits after the decimal point. If M and D are omitted, the values are saved according to the restrictions allowed by the hardware. The double-precision floating point number is precise to about 15 decimal places.

If UNSIGNED is specified, a negative value is not allowed.

· Double precision [(M, D)] [UNSIGNED] [ZEROFILL], REAL [(M, D)] [UNSIGNED] [ZEROFILL]

Is a synonym of DOUBLE. Except: If the SQL Server mode includes the REAL_AS_FLOAT option, REAL is a synonym for FLOAT, not a synonym for DOUBLE.

· FLOAT (p) [UNSIGNED] [ZEROFILL]

Floating point number. P indicates precision (in digits), but MySQL only uses this value to determine whether the data type of the result column is FLOAT or DOUBLE. If p is from 0 to 24, the data type changes to FLOAT without M or D values. If p is from 25 to 53, the data type changes to a DOUBLE without M or D values. The result column range is the same as the single precision FLOAT or DOUBLE data type described earlier in this section.

The FLOAT (p) syntax is compatible with ODBC.

· DECIMAL [(M [, D])] [UNSIGNED] [ZEROFILL]

The number of strictly compressed points. M is the total number of decimal places (precision), and D is the number of digits after the decimal point (scale. The '-' sign of the decimal point and (negative number) is not included in M. If D is 0, the value has no decimal point or fraction. The maximum number of digits of a DECIMAL integer (M) is 65. The maximum digit (D) of the supported decimal number is 30. If D is omitted, the default value is 0. If M is omitted, the default value is 10.

If UNSIGNED is specified, a negative value is not allowed.

The basic computation (+,-, *,/) of all DECIMAL columns is completed with a precision of 65 bits.

· DEC [(M [, D])] [UNSIGNED] [ZEROFILL], NUMERIC [(M [, D])] [UNSIGNED] [ZEROFILL], FIXED [(M [, D])] [UNSIGNED] [ZEROFILL]

Is a synonym for DECIMAL. FIXED synonyms apply to compatibility with other servers.

11.1.2. Overview of date and time types

This section comprehensively discusses the types of temporary columns.

· DATE

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 a value to the DATE column using strings or numbers.

· DATETIME

A combination of date and time. 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 values to be assigned to DATETIME columns using strings or numbers.

· TIMESTAMP [(M)]

Timestamp. The range is '2017-01-01 00:00:00 'to July 22, 1970.

The TIMESTAMP column is used to record the date and time during INSERT or UPDATE operations. If you do not assign a value, the first TIMESTAMP column in the table is automatically set to the date and time of the last operation. You can also assign a NULL value to set the TIMESTAMP column to the current date and time.

Returns a string in 'yyyy-MM-DD HH: MM: SS' format with a fixed display width of 19 characters. To obtain the numeric value, add + 0 to the TIMESTAMP column.

Note: The TIMESTAMP format used before MySQL 4.1 is not supported in MySQL 5.1. for information about the old format, see MySQL 4.1 Reference Manual.

· TIME

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 a value to the TIME column using strings or numbers.

· YEAR [(2 | 4)]

Two or four-digit year. The default format is four bits. In the four-digit format, the allowed values are 1901 to 2155 and 0000. In the two-digit format, the allowed values are 70 to 69, indicating that the value ranges from January 1, 1970 to January 1, 2069. MySQL displays the YEAR value in YYYY format, but allows you to assign a value to the YEAR column using a string or number.

CHAR (MB) 0-255

VARCHAR (M) 0-65,535

When VARCHAR is saved, it uses a prefix of one or two bytes plus data. If the length declared by the VARCHAR column is greater than 255, the length prefix is two bytes.

TINYBLOB

BLOB column with a maximum length of 255 (28-1) bytes.

When VARCHAR is saved, it uses a prefix of one or two bytes plus data. If the length declared by the VARCHAR column is greater than 255, the length prefix is two bytes.

· BINARY (M)

The BINARY type is similar to the CHAR type, but stores BINARY byte strings instead of non-BINARY strings.

· VARBINARY (M)

The VARBINARY type is similar to the VARCHAR type, but stores binary byte strings instead of non-binary strings.

· TINYBLOB

BLOB column with a maximum length of 255 (28-1) bytes.

· TINYTEXT

The maximum length of a TEXT column is 255 (28-1) characters.

· BLOB [(M)]

BLOB column with a maximum length of 65,535 (216-1) bytes.

The optional length of this type is M. If given, MySQL creates the column as the BLOB type that is the smallest but sufficient to accommodate the M-byte length value.

· TEXT [(M)]

A text column with a maximum length of 65,535 (216-1) characters.

The optional length is M. MySQL creates a column of the TEXT type that is the smallest but sufficient to accommodate M characters.

· MEDIUMBLOB

BLOB column with a maximum length of 16,777,215 (224-1) bytes.

· MEDIUMTEXT

A text column with a maximum length of 16,777,215 (224-1) characters.

· LONGBLOB

A blob column with a maximum length of 4,294,967,295 or 4 GB (232-1) bytes. The maximum valid (allowed) length of the LONGBLOB column depends on the maximum package size and available memory configured in the client/server protocol.

· LONGTEXT

A text column with a maximum length of 4,294,967,295 or 4 GB (232-1) characters. The maximum valid (allowed) length of the LONGTEXT column depends on the maximum package size and available memory configured in the client/server protocol.

· ENUM ('value1', 'value2 ',...)

Enumeration type. Only one value string can be selected from the 'value1 ', 'value2',..., NULL or special ''error values. The ENUM column can have a maximum of 65,535 distinct values. The ENUM value is represented by an integer internally.

· SET ('value1', 'value2 ',...)

One setting. A string object can have zero or multiple values. each value must come from a column value of 'value1 ', 'value2',... the SET column can have a maximum of 64 members. The SET value is represented by an integer internally.