The difference between int (10) and int (11) in MySQL

I have been looking for a job recently, so I have been interviewing. During the interview process, I exposed many of my shortcomings, and I have been summarizing and summarizing. Perhaps most interviewers will ask questions about MySQL such as database optimization, master-slave replication, indexes, engines, etc. So what strikes me most is what is the difference between int (10) and int (11). This seems to be the most basic question, and I never seem to care about it. I was a little confused at the time, what’s the difference? have no idea! have not thought!

After returning home, I quickly reviewed the knowledge about mysql data types. A few words about integer types.

Integer data type in mysql

Data type	Description	Storage requirements
tinyint	Tiny integer	1 byte
smallint	Small integer	2 bytes
mediumint	Medium integer	3 bytes
int(integer)	Normal size integer	4 bytes
bigint	Big integer	8 Bytes

Value range of different types

##tinyint-128～1270～ 255##smallintmediumintint(integer)bigint##Different data types Default display width

Data type	Signed	Unsigned
-32768～32767	0～65535
-8388608～8388607	0～16777215
-2147483648～2147483647	0～4294967295
-9223372036854775808~9223372036854775807	0~18446744073709551615

Data typeDefault display widthtinyint4smallint6911##bigint20So what is the relationship between the value range and the display width? I did an experiment using tinyint. First, I created a table as follows

mysql> desc test_integer;
+-----------+------------+------+-----+---------+----------------+
| Field     | Type       | Null | Key | Default | Extra          |
+-----------+------------+------+-----+---------+----------------+
| id        | int(11)    | NO   | PRI | NULL    | auto_increment |
| test_id_1 | tinyint(1) | NO   |     | NULL    |                |
| test_id_2 | tinyint(4) | NO   |     | NULL    |                |
+-----------+------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

	##mediumint
	int(integer)
	* Because the negative sign occupies one digit, the width of each type is the maximum value of 1 digit. For example, the maximum number of digits for tinyint is 3, plus the negative sign, so the display width is 4.

I created two fields, test_id_1 with a display width of 1 and test_id_2 with a display width of 4, of the signed tinyint type. Then I inserted the data, and at the same time Insert the values ​​- 128, 1 and 127 and see what happens.

mysql> insert into test_integer (test_id_1,test_id_2) values (-128,-128), (1,1), (127,127);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

ok prompts that the insertion is successful, let’s query

mysql> select * from test_integer;
+----+-----------+-----------+
| id | test_id_1 | test_id_2 |
+----+-----------+-----------+
|  1 |      -128 |      -128 |
|  2 |         1 |         1 |
|  3 |       127 |       127 |
+----+-----------+-----------+
3 rows in set (0.00 sec)

and the result is the same. After the above experiment, we draw the conclusion:

The value range of the display width integer type is unrelated. The display width only specifies the maximum number of numbers that may be displayed by MySQL. If the number of digits is less than the specified width, it will be filled with spaces. If a value larger than the display width is inserted, as long as the value does not exceed the value range of the integer of this type, the value It can still be inserted and displayed.

Similarly, there is no difference between int (10) and int (11), except that the display width is different. Friends, please don’t forget it.

Recommended tutorial: "

PHP Tutorial

"

The above is the detailed content of The difference between int (10) and int (11) in MySQL. For more information, please follow other related articles on the PHP Chinese website!