What is the difference between int (10) and int (11) in Mysql? _ MySQL

What is the difference between int (10) and int (11) in Mysql? bitsCN.com

What is the difference between int (10) and int (11) in Mysql?

Mysql supports all standard SQL numeric data types. These types include strict NUMERIC data types (INTEGER, SMALLINT, DECIMAL, and NUMERIC), and approximate NUMERIC data types (FLOAT, REAL, and double precision ). The keyword INT is the synonym of INTEGER, and the keyword DEC is the synonym of DECIMAL.

BIT data type stores BIT field values and supports MyISAM, MEMORY, InnoDB, and BDB tables.

As an extension of the SQL standard, MySQL also supports integer TINYINT, MEDIUMINT, and BIGINT. The following table shows the storage and range of each integer type.

Maximum minimum value of type bytes

(Signed/unsigned) (signed/unsigned)

TINYINT 1-128 127

0 255

SMALLINT 2-32768 32767

0 65535

MEDIUMINT 3-8388608 8388607

0 16777215

INT 4-2147483648 2147483647

0 4294967295

BIGINT 8-9223372036854775808 9223372036854775807

0 18446744073709551615

Specify the display width (for example, INT (4) of the integer in parentheses after the keyword of the Mysql type )). The optional display width rule is used to fill up the width from the left when the display width is less than the specified column width value. The display width does not limit the range of values that can be saved in the column, nor the display of values that exceed the specified width of the column.

When combined with the optional extension attribute ZEROFILL, the spaces supplemented by default are replaced by zero. For example, for a column declared as INT (5) ZEROFILL, value 4 is retrieved as 00004. Note that if you save a value that exceeds the display width in an integer column, MySQL may encounter problems when generating a temporary table for a complex join, because MySQL believes that the data is suitable for the width of the original column.

All integer types can have an optional (non-standard) attribute UNSIGNED. You can use an unsigned value when you want to allow only non-negative numbers in a column and the column requires a large upper limit value range.

Therefore, the characters in parentheses after int (10) and int (11) indicate the display width. the display width of the integer column and the number of characters required by mysql to display the value of this column, it has nothing to do with the size of the bucket required by this integer. The maximum data storage capacity of int-type fields is 2147483647 (signed) and 4294967295 (unsigned ).

The floating point and fixed point types can also be UNSIGNED. Same number type. this attribute prevents negative values from being saved to columns. However, unlike the integer type, the upper range of the column value remains unchanged.

If ZEROFILL is specified for a value column, MySQL automatically adds the UNSIGNED attribute to the column.

For the floating-point column type, the single-precision value in MySQL uses 4 bytes, and the double-precision value uses 8 bytes.

The FLOAT type is used to indicate the approximate numeric data type. The SQL standard allows you to specify the precision (but not the exponential range) with bits in parentheses after the keyword FLOAT ). MySQL also supports optional specifications that are only used to determine the storage size. The precision between 0 and 23 corresponds to the 4-byte single precision of the FLOAT column. The precision between 24 and 53 corresponds to the 8-byte dual precision of the DOUBLE column.

MySQL allows the use of non-standard syntax: FLOAT (M, D), REAL (M, D), or double precision (M, D ). Here, "(M, D)" indicates that the value displays a total of M-bit integers, where D is behind the decimal point. For example, a column defined as FLOAT (999.9999) can be displayed. MySQL returns a rounded value when saving the value. Therefore, if 999.00009 is inserted in the FLOAT (999.0001) column, the approximate result is.

MySQL treats DOUBLE as a synonym for double precision (non-standard extension. MySQL also treats REAL as a synonym for double precision (non-standard extension) unless the SQL Server mode includes the REAL_AS_FLOAT option.

To ensure maximum portability, FLOAT or double precision should be used for codes that require storing approximate numeric data values. PRECISION or digits are not specified.

The DECIMAL and NUMERIC types are treated as the same type in MySQL. They are used to save values that must be accurate, such as currency data. When declaring a column of this type, you can (and usually need to) specify the precision and scale. for example:

Salary DECIMAL (5, 2)

In this example, 5 is the precision and 2 is the scale. The precision indicates the primary digits of the saved value, and the scale indicates the digits that can be saved after the decimal point.

In MySQL 5.1, DECIMAL and NUMERIC values are saved in binary format.

Standard SQL requires that the salary column be able to save any value with an integer of 5 digits and two decimal places. Therefore, the range of values that can be saved in the salary column is from-999.99 to 999.99.

In standard SQL, the syntax DECIMAL (M) is equivalent to DECIMAL (M, 0 ). Similarly, the syntax DECIMAL is equivalent to DECIMAL (M, 0). The M value can be determined by calculation. MySQL 5.1 supports DECIMAL and NUMERIC data types. The default M value is 10.

The maximum number of digits of a DECIMAL or NUMERIC is 65, but the actual range of a specific DECIMAL or NUMERIC column is subject to the precision or scale of a specific column. If the number of digits after the decimal point assigned by such columns exceeds the allowed range of the specified scale, the value is converted to this scale. (The specific operation is related to the operating system, but the general results are intercepted to the allowed digits ).

BIT data types can be used to save BIT field values. The BIT (M) type allows the storage of M-BIT values. M ranges from 1 to 64.

To specify the positioning value, you can use the B 'value' character. Value is a binary value written in numbers 0 and 1. For example, B '000000' and B '000000' indicate 7 and 111 respectively.

BitsCN.com