What is the length of int(5) in mysql

How long is int(5)?

When designing mysql table fields, does int(5) mean that the length of the field is 5?

If you think so, then please continue reading, I believe you will gain something new.

Let’s look at a practical example first

We create a new table with only one primary key id and one length field, where the length field is set to int(5).

#新建test表
CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `length` int(5) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Is it true that the length field can only store numbers with a length of no more than 5? Let’s try inserting a piece of data.

We insert a larger piece of data 123456789. This data has a total of 9 digits to see if it can be inserted normally.

#插入一条大数据
insert into test (length) values (123456789);

It turns out that int(5) can insert data with a length greater than 5, so now it is certain that int(5) does not represent the width of the data.

#查看插入数据
mysql> select * from test;
+------------+-----------+
| id         | length    |
+------------+-----------+
|  1         | 123456789 |
+------------+-----------+

In mysql, the length of the int field is actually fixed, which is 4 bytes. Regardless of whether you use int(11) or int(5), the length of the field is fixed at 4 bytes.

int(5) is actually used in conjunction with another attribute zerofill, which means that if the width of the field value is less than 5, 0 will be automatically added in front. If the width is greater than or equal to 5, then No need to add 0.

#int(5) length 字段添加了 zerofill 属性
mysql> select * from test;
+----+--------+
| id | length |
+----+--------+
|  1 |  00888 |
|  2 |  00012 |
|  3 |  12345 |
|  4 | 123456 |
+----+--------+

Note that the premise is to add the zerofill attribute to the field, otherwise int(5) will not work.

#int(5) length 字段没有 zerofill 属性
mysql> select * from test;
+----+--------+
| id | length |
+----+--------+
|  1 |    888 |
|  2 |     12 |
|  3 |  12345 |
|  4 | 123456 |
+----+--------+

If you want to set the length of an integer field, you should choose the types tinyint, smallint, mediumint, int, bigint. These integer types represent fixed lengths. Please see the following for the specific length. sheet.

Note that if you want to store signed integer numbers, the zerofill attribute is not supported and will be automatically converted to 0. This needs to be considered in some businesses involving decrement.

The above is the detailed content of What is the length of int(5) in mysql. For more information, please follow other related articles on the PHP Chinese website!