Shulou(Shulou.com)06/01 Report--

Foreword:

Integer is one of the most commonly used field types in MySQL, which is usually used to store integers, of which int is the most commonly used in integers. Do you really know the int type? This article will familiarize you with int types, as well as the use of other integer fields.

1. Integer classification and storage range integer type byte signed range TINYINT1-128 ~ 1270 ~ 255SMALLINT2-32768 ~ 327670 ~ 65535MEDIUMINT3-8388608 ~ 83886070 ~ 16777215INT/INTEGER4-2147483648 ~ 21474836470 ~ 4294967295BIGINT8-9223372036854775808 ~ 92233720368547758070 ~ 1844674407709551615

The table has four columns: field type, number of bytes occupied, signed range, and unsigned range. We take the int type as an example: int type, which occupies 4byte. Students who have studied computer principles should know that byte is not the smallest unit of computer storage, but there are smaller units than byte, that is, bit, where a bit represents a 0 or 1; 8 bits constitute a byte; general bytes are represented by uppercase B and bit by lowercase b.

Conversion of computer storage units:

1B=8b

1KB=1024B

1MB=1024KB

Then according to the number of bytes allowed to be stored in the int type, we can convert the minimum storage value of the int UNSIGNED (unsigned) type to 0 and the maximum value to 4294967295 (that is, 4B=32b, the maximum value is 32 1s, that is, 4294967295 translates to 32 1s in binary).

two。 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 mysql > show create table test_int\ sec * 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 (123456 12345678) 12345678 901) Query OK, 1 row affected, 5 warnings (0.00 sec) mysql > insert into test_int values (- 1234 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.01sec) mysql > select * from test_int +-- + | col1 | col2 | col3 | col4 | col5 | + -+ | 32767 | 8388607 | 2147483647 | 9223372036854775807 | |-128128 |-32768 |-8388608 |-2147483648 |-9223372036854775808 | +-

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

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

We often hear this sentence: M in int (M) represents the maximum display width, and "maximum display width" our first reaction is that the value of this field is the width of the maximum value that can be stored, thinking that if we have built int (1), we will not be able to store data 10, but that is not what we mean. The display width of an integer column has nothing to do with how many characters mysql needs to display the column value, and has nothing to do with the amount of storage space required by the integer. For example, no matter how many characters the display width is set, int occupies 4 bytes and bigint takes 8 bytes. That is, int (5) and int (10) can be stored in the same range. The integer field has a ZEROFILL property (0 padding) that fills 0 in front of data that is not long enough to reach the set length. M behaves differently after adding ZEROFILL, and when using ZEROFILL, the unsigned (unsigned) attribute is automatically added by default. For example, INT (3) ZEROFILL, if you insert 10 into the database, the actual insert is 010, that is, the previous addition of a 0, 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\ gateway * 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 Query OK, 1 row affected (0.00 sec) mysql > select * from test_int_zerofill;+-+ | col1 | col2 | col3 | +-+ | 00012 | 0000000012 | 12 | +-+ 1 row in set (0.00 sec)

So some students may ask zerofill what application scenarios there are, the more commonly used should be the month or date before the complement 0, so that the display will be more standardized

CREATE TABLE `tzerofill` (`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 sec) mysql > insert into t_zerofill values (20119 sec) mysql > insert into t_zerofill values (20119 sec) mysql > insert into t_zerofill values (20119 sec) mysql > insert into t_zerofill values (20119 sec) mysql > insert into t_zerofill values (2019 sec) mysql > insert into t_zerofill values Query OK, 1 row affected (0.01sec) mysql > select * from t_zerofill +-+ | year | month | day | +-+ | 2019 | 06 | 05 | 2019 | 06 | 18 | 2019 | 10 | 01 | 2019 | 11 | 11 | +-+ 4 rows in set (sec) 4. Type selection

After the above introduction, it is much easier to select different integer fields. In line with the principle of minimizing storage, of course, you can choose TINYINT rather than SMALLINT, and you can choose MEDIUMINT rather than INT, but try to choose the type that takes up fewer bytes under the premise of satisfying the business. For fields that are sure to store only positive integers, you can add the unsigned attribute, which makes the storage range wider. For example, when the field has an AUTO_INCREMENT attribute, we can add the unsigned attribute to the int type.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.