Mysql database integer field design example

As long as you do the development, you must have created a table, what type of tables, how long, and so on. Feel nothing to say, but summed up, there is something to say.

Data Type	There are symbols	No sign	Storage
bigint	-2^63 (-9223372036854775808) to 2^63-1 (9223372036854775807)	0 to 2^64-1 (18446744073709551615)	8 bytes
Int	-2^31 (-2147483648) to 2^31-1 (2147483647)	0 to 2^32-1 (4294967295)	4 bytes
Mediumint	-2^31 (-8388608) to 2^23-1 (8388607)	0 to 2^24-1 (16777215)	3 bytes
smallint	-2^15 (-32768) to 2^15-1 (32767)	0 to 2^16-1 (65535)	2 bytes
tinyint	-2^7 (-127) to 2^7-1 (127)	0 to 2^8-1 (255)	1 bytes

Knowing the range value of a type is helpful to us, for example, if you use tinyint, the maximum value of the field will not exceed 255, the field is set to tinyint (10), in the case of a symbol, the maximum can only be 127, on the surface seems to be able to deposit 10 bits, but the actual can only be stored in 3 digits.
Depending on the size of the number you want to save, choose a different data type, for example, if it is the identity bit, the general situation is set to tinyint (1), the most appropriate storage, small, and will not be saved.

MARIADB [test]> CREATE TABLE ' test ' (
-> ' big_test ' bigint DEFAULT 0,
-> ' int_test ' int (one) DEFAULT 0,
-> ' Medium_test ' Mediumint (8) DEFAULT 0,
-> ' small_test ' smallint (6) DEFAULT 0,
-> ' tiny_test ' tinyint (4) DEFAULT 0
->) Engine=myisam DEFAULT Charset=utf8;
Query OK, 0 rows affected (0.09 sec)

MARIADB [test]> INSERT into Test (big_test, Int_test, Medium_test, Small_test, Tiny_test) VALUES (9223372036854775807, 2147483647, 8388607, 32767, 127);
Query OK, 1 row Affected (0.00 sec)

MARIADB [test]> SELECT * from test;
+---------------------+------------+-------------+------------+-----------+
| Big_test | Int_test | Medium_test | Small_test | Tiny_test |
+---------------------+------------+-------------+------------+-----------+
| 9223372036854775807 | 2147483647 | 8388607 | 32767 | 127 |
+---------------------+------------+-------------+------------+-----------+
1 row in Set (0.00 sec)

MARIADB [test]> INSERT into Test (big_test, Int_test, Medium_test, Small_test, Tiny_test) VALUES (9223372036854775807, 2147483647, 8388607, 32767, 128);
Query OK, 1 row affected, 1 Warning (0.00 sec)//128 over, signed tinyint maximum, reported warning out, but still in storage.

MARIADB [test]> SELECT * from test;
+---------------------+------------+-------------+------------+-----------+
| Big_test | Int_test | Medium_test | Small_test | Tiny_test |
+---------------------+------------+-------------+------------+-----------+
| 9223372036854775807 | 2147483647 | 8388607 | 32767 | 127 |
| 9223372036854775807 | 2147483647 | 8388607 | 32767 | 127 |
+---------------------+------------+-------------+------------+-----------+
2 rows in Set (0.00 sec)

MARIADB [test]> ALTER TABLE ' test ' change ' tiny_test ' tiny_test ' TINYINT (Ten) NULL defaultɔ '
Query OK, 0 rows Affected (0.00 sec)
records:0 duplicates:0 warnings:0//change length to 10, no error

MARIADB [test]> INSERT into Test (big_test, Int_test, Medium_test, Small_test, Tiny_test) VALUES (9223372036854775807, 2147483647, 8388607, 32767, 128);
Query OK, 1 row affected, 1 Warning (0.00 sec)//In the Insert data, same report warning, but still warehousing

MARIADB [test]> SELECT * from test;
+---------------------+------------+-------------+------------+-----------+
| big_test | int_test | medium_ Test | Small_test | Tiny_test |
+---------------------+------------+-------------+------------+-----------+
| 9223372036854775807 | 2147483647 | 8388607 | 32767 | 127 |
| 9223372036854775807 | 2147483647 | 8388607 | 32767 | 127 |
| 9223372036854775807 | 2147483647 | 8388607 | 32767 | 127 |
+---------------------+------------+-------------+------------+-----------+
3 rows in Set (0.00 sec)