Differences between int (1) and int (11) in mysql

In the mysql field definition, x in INT (x) only indicates the display width. 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. Therefore, the definition of x has nothing to do with the storage space. It is 4 bytes.

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

1 ). int (10), which will be placed in the metadata of the result set and returned to the application. the application can be left filled with less than 10 characters. Spaces are filled by default.

2). Add zerofill and fill it with 0.

Mysql> create table ccc (a int (4), B int (20), c int (20) zerofill );
Query OK, 0 rows affected (0.01 sec)

Mysql> insert into ccc values (4342343,342,342 342 );
Query OK, 1 row affected (0.00 sec)

Mysql> select * from ccc;
+ --------- + ------ + ---------------------- +
| A | B | c |
+ --------- + ------ + ---------------------- +
| 4342343 | 342 | 00000000000000342342 |
+ --------- + ------ + ---------------------- +
1 row in set (0.00 sec)

We can see that we need to fill B with spaces left, but the program chooses not to fill.

If you add optional ZEROFILL to a field, it is more obvious. Because it will replace the default space with zero.

// INT (4) UNSIGNED ZEROFILL
0001
0002
...
0099
...
0999
...
9999
...
10000
 
// INT (2) UNSIGNED ZEROFILL
01
02
...
09
...
99
...
100

If the ZEROFILL option is not included, the left side is filled with spaces.

// INT (4)
   1
   2
...
99
...
999
...
9999
...
10000
 
// INT (2)
 1
 2
...
 9
...
99
...
100

Below is a supplement

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 '111 & prime; and B' 100000000 & prime; indicate 7 and 128 respectively.