Are the value ranges of int(3) and int(10) the same in mysql

Question:

Do MySQL fields, unsigned int(3), and unsigned int(6), have the same range of values? If different, how big are they?

answer:

Different, int(3) can display up to 3 digits of unsigned integer, and int(6) can display up to 6 digits of unsigned integer.

If your answer is the same as above, congratulations, you made the same mistake as me.

Reality:

An int type integer occupies 4 bytes. The range of a signed integer is: -2^31 ~ 2^31-1, and the range of an unsigned integer is: 0 ~ 2^32-1. Since they are all unsigned integers, the storage range is the same. int(6) just fills the number with 0 when it is less than 6 digits. If it exceeds 6 digits, no filling is required.

We create the following table:

drop table if exists test;
CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) NOT NULL,
  `b` int(11) unsigned zerofill NOT NULL,
  `c` int(5) DEFAULT NULL,
  `d` int(5) unsigned zerofill NOT NULL,
  `e` int(15) DEFAULT NULL,
  PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

After inserting some data, use the cmd command line to view:

mysql> select * from test;
+----+------------+-------------+------------+------------+------------+
| id | a | b | c | d | e |
+----+------------+-------------+------------+------------+------------+
| 1 | 1 | 00000000001 | 1 | 00001 | 1 |
| 2 | 1234567890 | 01234567890 | 1234567890 | 1234567890 | 1234567890 |
+----+------------+-------------+------------+------------+------------+

It is found that both int(3) and int(6) can display integers with more than 6 digits. However, when the number is less than 3 or 6 digits, it will be padded with 0s in front.

Note: If you use a tool like navicat to view it, 0 will not be added. It may have been processed. You need to use the mysql cmd command line to see this effect.

Check the manual, the explanation is this:

MySQL also supports the option of specifying the display width of integer values ​​in parentheses following the type keyword (for example, INT(4)). This optional display width specifies that the display width should be filled from the left if it is smaller than the specified column width. The display width does not restrict the range of values ​​that can be held in the column, nor does it restrict the display of values ​​that exceed the specified width of the column.

In other words, the length of int does not affect the storage accuracy of the data. The length is only related to the display. In order to make it clearer, we used zerofill in the table creation statement in the above example.

Knowledge Points

The M in int(M) indicates the maximum display width. The maximum valid display width is 255, and the display width has nothing to do with the storage size or the range of values ​​contained in the type.

First, let's talk about MySQL's numeric types. 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 a synonym for INTEGER, and the keyword DEC is a synonym for DECIMAL.

The BIT data type stores bit field values ​​and is supported by MyISAM, MEMORY, InnoDB, and BDB tables. As an extension to the SQL standard, MySQL also supports the integer types TINYINT, MEDIUMINT, and BIGINT. The following table shows the storage and range required for each integer type.

in conclusion:

Whether it is unsigned int(3) or unsigned int(6), it stores a 4-byte unsigned integer, that is, 0~2^32.

Summarize

This is the end of this article about whether the value range of int(3) and int(10) in MySQL is the same. For more information about the value range of mysql int(3) and int(10), please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!