MySQL data type-integer type

1. Background

* MySQL supports SQL standard integer type integers (or INT) and SMALLINT. As a standard extension, MySQL also supports integer types TINYINT, MEDIUMINT, and BIGINT

two。 The range of storage and values required for integer types

Type occupancy minimum value maximum value

(byte) (signed/Unsigned) (signed/Unsigned) TINYINT1-128

one hundred and twenty seven

0255SMALLINT2-3276832767

065535MEDIUMINT3-83886088388607

016777215INT4-21474836482147483647

04294967295BIGINT8-92233720368547758089223372036854775807

018446744073709551615

3. Zerofill attribute encounters integer type application

* create zerofill test table zerofill_test

Mysql > CREATE TABLE zerofill_test (- > `id` INT (6) ZEROFILL->) ENGINE=INNODB CHARSET=utf8mb4;Query OK, 0 rows affected (0.08 sec)

* insert values with a numeric length less than 6

Mysql > INSERT INTO zerofill_test SELECT 11111 witch query OK, 1 row affected (0.03 sec) Records: 1 Duplicates: 0 Warnings: 0

* insert values with a numeric length greater than 6

Mysql > INSERT INTO zerofill_test SELECT 222222 * * query OK, 1 row affected (0.04 sec) Records: 1 Duplicates: 0 Warnings: 0

* View all the data in the zerofill_ test table

Mysql > SELECT * FROM zerofill_test;+-+ | id | +-+ | 011111 | | 2222222 | +-+ 2 rows in set (sec)

* zerofill summary

* when the length of the displayed value is less than the length defined by INT, the high position complement 0 is displayed.

* when the length of the displayed value is greater than the length defined by INT, it will be displayed as usual.

* zerofill displays properties without any modification to the value

4. Unsigned type overflow phenomenon

* create unsigned test table unsigned_test

Mysql > CREATE TABLE unsigned_test (- > id BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,-> data INT UNSIGNED->) ENGINE=INNODB CHARSET=utf8mb4;Query OK, 0 rows affected (0.12 sec)

* insert two rows of values, one large and one small

Mysql > INSERT INTO unsigned_test SELECT null, 1 INSERT INTO unsigned_test SELECT null query OK, 1 row affected (0.03 sec) Records: 1 Duplicates: 0 Warnings: 0mysql > INSERT INTO unsigned_test SELECT null, 2 politics query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0

* View the data values inserted in the table

Mysql > SELECT * FROM unsigned_test;+----+-+ | id | data | +-+-+ | 1 | 1 | | 2 | 2 | +-+-- + 2 rows in set (0.00 sec)

* use decimal values to subtract large values

Mysql > SELECT data-(SELECT data FROM unsigned_test where id=2) FROM unsigned_test where id=1;ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in'(`mytest`.`unsigned _ test`.`data`-(select `mytest`.`unsigned _ test`.`data`from `mytest`.`unsigned _ test`test`1)'

5. Summary of the application of integer types

* signed/Unsigned: whether there is a symbol

* ZEROFILL: displays the attribute without any modification to the value

* it is recommended not to use UNSIGNED. There is no significant change in scope.

* UNSIGNED may be overflowed

* BIGINT is recommended for self-adding INT type primary key

6. Summary

In order to demand-driven technology, there is no difference in technology itself, only in business.