Difference between int (11) and int (3) in MySQL _ MySQL

Difference between int (11) and int (3) in MySQL 11.2. numerical type

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.

Type	Bytes	Minimum value	Maximum value
(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

MySQL also supports specifying the display width of an integer (for example, INT (4) in parentheses after a keyword of this 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.

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) Or REAL (M,D) Or double precision (M,D). Here, "(M,D) "Indicates that the value is displayed in total.MAn integer.DDigits are placed after 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), which can be determined by calculationM. MySQL 5.1 supports DECIMAL and NUMERIC data types.MThe default 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. BIT (M) Type can be storedMBit value.MThe range is 1 to 64.

To specify the positioning value, you can use B'Value'Character.ValueIs a binary value written with 0 and 1. For example, B '000000' and B '000000' indicate 7 and 111 respectively. See section 9.1.5 "bit field value ".

If the length of the value allocated for the BIT (M) column is smallerMBit. fill the value with 0 on the left. For example, if you assign a B '000000' value to the BIT (6) column, the effect is the same as that for allocating B '000000.

When you want to save a value that exceeds the permitted range of the column in a numeric column, the MySQL operation depends on the valid SQL mode. If the mode is not set, MySQL crops the value to the corresponding endpoint of the range and saves the cut value. However, if the mode is set to traditional ("strict mode"), values out of the range will be rejected and an error will be prompted, and insertion based on SQL standards will fail. See section 5.3.2 "SQL Server mode ".

If the INT column is UNSIGNED, the column range is the same, but its endpoint is changed to 0 and 4294967295. If you try to save-9999999999 and 9999999999, the values saved to the column in non-strict mode are 0 and 4294967296.

If the value allocated in the floating point or fixed point column exceeds the specified (or default) precision and scale range, MySQL saves the value of the corresponding range endpoint in non-strict mode.

When MySQL does not work in strict mode, the conversion of alter table, load data infile, UPDATE, and multiline INSERT statements due to cropping will be reported as a warning. When MySQL works in strict mode, these statements fail, and some or all values are not inserted or changed, depending on whether the table is a transaction table and other factors. For more information, see section 5.3.2 "SQL Server mode ".

Reference: http://dev.mysql.com/doc/refman/5.1/zh/column-types.html#numeric-types

Simply put:

Int (11) and int (3) have no impact on the internal storage, including the storage space that can be stored in the value range; only when required by some applications, return a value of at least int (M) M Width. The original English text is as follows:

MySQL supports an extension for optionally specifying the display width of integer data types in parentheses following the base keyword for the type. for example, INT (4) specifies an INT with a display width of four digits. this optional display width may be used by applications to display integer values having a width less than the width specified for the column by left-padding them with spaces. (That is, this width is present in the metadata returned with result sets. whether it is used or not is up to the application .)

The display width does not constrain the range of values that can be stored in the column. nor does it prevent values wider than the column display width from being displayed correctly. for example, a column specified as SMALLINT (3) has the usual SMALLINT range of-32768 to 32767, and values outside the range permitted by three digits are displayed in full using more than three digits.