Full analysis of MySQL INT type

Preface:

Integer is one of the most commonly used field types in MySQL, usually used to store integers, among which int is the most commonly used integer type. Do you really understand the int type? This article will familiarize you with the knowledge related to the int type and will also introduce the use of other integer fields.

1. Integer classification and storage range

The table has four columns: field type, number of bytes occupied, signed range, and unsigned range.
Let's take the int type as an example:
The int type occupies 4 bytes. Students who have studied computer principles should know that byte is not the smallest unit of computer storage. There is a smaller unit than byte, that is, bit. One bit represents a 0 or 1; 8 bits make up a byte; generally, byte is represented by uppercase B, and bit is represented by lowercase b.

Conversion of computer storage units: 1B=8b 1KB=1024B 1MB=1024KB

Then, based on the fact that the int type is allowed to store 4 bytes, we can calculate that the minimum value that can be stored in the int UNSIGNED type is 0, and the maximum value is 4294967295 (i.e. 4B=32b, the maximum value is composed of 32 1s, that is, 4294967295 converted to binary is 32 1s).

2. Storage range test

mysql> CREATE TABLE test_int ( -> col1 TINYINT, -> col2 SMALLINT, -> col3 MEDIUMINT, -> col4 INT, -> col5 BIGINT -> ) ENGINE = INNODB DEFAULT CHARSET = utf8; Query OK, 0 rows affected (0.01 sec)
mysql> show create table test_int\G*************************** 1. row *************************** Table: test_intCreate Table: CREATE TABLE `test_int` ( `col1` tinyint(4) DEFAULT NULL, `col2` smallint(6) DEFAULT NULL, `col3` mediumint(9) DEFAULT NULL, `col4` int(11) DEFAULT NULL, `col5` bigint(20) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)
mysql> insert into test_int values ​​(1234,123456,12345678,12345678901,12345678901234567890); Query OK, 1 row affected, 5 warnings (0.00 sec)
mysql> insert into test_int values ​​(-1234,-123456,-12345678,-12345678901,-12345678901234567890); Query OK, 1 row affected, 5 warnings (0.01 sec)
mysql> show warnings;+---------+------+----------------------------------------------+| Level | Code | Message |+---------+------+-----------------------------------------------+| Warning | 1264 | Out of range value for column 'col1' at row 1 || Warning | 1264 | Out of range value for column 'col2' at row 1 || Warning | 1264 | Out of range value for column 'col3' at row 1 || Warning | 1264 | Out of range value for column 'col4' at row 1 || Warning | 1264 | Out of range value for column 'col5' at row 1 |+---------+------+-----------------------------------------------+5 rows in set (0.01 sec)
mysql> select * from test_int;+------+--------+----------+-------------+--------------+----------------------+| col1 | col2 | col3 | col4 | col5 |+------+--------+----------+-------------+----------------------+| 127 | 32767 | 8388607 | 2147483647 | 9223372036854775807 || -128 | -32768 | -8388608 | -2147483648 | -9223372036854775808 |+------+--------+----------+-------------+----------------------+

From the above test, we can see that: when there is a maximum storage range for various integer types, when the size of the stored number is not in the storage range, MySQL will generate an alarm, but the number can be inserted, and the default value is truncated to the maximum or minimum value that can be stored.

3. The meaning of M in int(M) and the use of zerofill

We often hear this sentence: The M in int(M) stands for the maximum display width. Our first reaction to "maximum display width" is the maximum width of the value that the field can store. We think that if we create int(1), we cannot store the data 10. In fact, this is not the meaning.
The display width of an integer column has nothing to do with how many characters MySQL needs to display the value of the column, and has nothing to do with the amount of storage space required for the integer. For example, no matter how many characters the display width is set to, int occupies 4 bytes and bigint occupies 8 bytes. That is, the storage range of int(5) and int(10) is the same.
Integer fields have a ZEROFILL attribute (0 padding), which fills the front of the data that is not long enough with 0 to reach the set length. M behaves differently after adding ZEROFILL. When ZEROFILL is used, the unsigned attribute is automatically added by default. For example, if INT(3) ZEROFILL is used, and you insert 10 into the database, the actual value inserted is 010, which means a 0 is added in front. Let's test it:

mysql> CREATE TABLE test_int_zerofill ( -> col1 INT(5) ZEROFILL, -> col2 INT ZEROFILL, -> col3 INT(5) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.01 sec)
mysql> show create table test_int_zerofill\G*************************** 1. row *************************** Table: test_int_zerofillCreate Table: CREATE TABLE `test_int_zerofill` ( `col1` int(5) unsigned zerofill DEFAULT NULL, `col2` int(10) unsigned zerofill DEFAULT NULL, `col3` int(5) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)
mysql> insert into test_int_zerofill values ​​(12,12,12); Query OK, 1 row affected (0.00 sec)
mysql> select * from test_int_zerofill;+-------+------------+------+| col1 | col2 | col3 |+-------+------------+------+| 00012 | 000000012 | 12 |+-------+------------+------+1 row in set (0.00 sec)

Some students may ask what are the application scenarios of zerofill? The most common one should be to add 0 before the month or date, so that the display will be more standardized

CREATE TABLE `t_zerofill` ( `year` year(4) DEFAULT NULL, `month` int(2) unsigned zerofill DEFAULT NULL, `day` int(2) unsigned zerofill DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
mysql> insert into t_zerofill values ​​(2019,6,5); Query OK, 1 row affected (0.01 sec)
mysql> insert into t_zerofill values ​​(2019,6,18); Query OK, 1 row affected (0.00 sec)
mysql> insert into t_zerofill values ​​(2019,10,1); Query OK, 1 row affected (0.00 sec)
mysql> insert into t_zerofill values ​​(2019,11,11); Query OK, 1 row affected (0.01 sec)
mysql> select * from t_zerofill;+------+-------+------+| year | month | day |+------+-------+------+| 2019 | 06 | 05 || 2019 | 06 | 18 || 2019 | 10 | 01 || 2019 | 11 | 11 |+------+-------+------+4 rows in set (0.00 sec)

4. Type selection

After the above introduction, the selection of different integer fields becomes much easier. In line with the principle of minimizing storage, of course, TINYINT should be chosen over SMALLINT, and MEDIUMINT should be chosen over INT. However, everything should be done under the premise of satisfying the business and the type that occupies fewer bytes should be chosen as much as possible. For fields that are determined to store only positive integers, you can add the unsigned attribute, which will expand the storage range. For example, when the field has the AUTO_INCREMENT attribute, we can add the unsigned attribute to the int type.

The above is the detailed content of the full analysis of MySQL INT type. For more information about MySQL INT type, please pay attention to other related articles on 123WORDPRESS.COM!