MYSQL field type tinyint, SMALLINT, Mediumint, INT, BIGINT

1. Tinyint: Only 128 to 127 of the size of the number, do not care about the following definition, such as tinyint (9). Requires 1 bytes of storage space. When unsigned is added, the number can be increased from 0 to 255 size. [email protected]: Test 22:36:25> CREATE TABLE Ti (ID tinyint (9), no tinyint (1)); Query OK, 0 rows affected (0.03 sec) [email protected]: Test 22:36:44> insert INTO TI values (129,130); Query OK, 1 row affected, 2 warnings (0.00 sec) [email protected]: Test 22:37:31> show warnings;+---------+------ +---------------------------------------------+| Level | Code | Message |+---------+------+---------------------------------------------+| Warning | 1264 | Out of range value for column ' ID ' at row 1 | | Warning | 1264 |  Out of range value for column ' No ' on row 1 |+---------+------+---------------------------------------------+2 rows in Set (0.00 sec) The warning is reported here when inserting data because of the sql_mode relationship. MySQL is set to null here by default, allowing out-of-range data insertions, giving only one warning. You can query the following table: [email protected]: Test 22:37:39> SELECT * FROM ti;+------+------+| ID |  No |+------+------+|  127 | 127 |+------+------+1 row in Set (0.00 sec) is allowed to insert negative numbers at this time. [email protected]: Test 22:49:40> insert INTO TI values ( -9,9); Query OK, 1 row Affected (0.00 sec) [email protected]: Test 22:50:06> SELECT * FROM ti;+------+------+| ID |  No |+------+------+|  127 |   127 | |    -9 | 9 |+------+------+2 rows in Set (0.00 sec) to keep the database from generating negative values, we can modify the type parameters for this field: [email protected]: Test 22:46:25> alte R Table ti Modify no tinyint unsigned; Query OK, 1 row affected (0.01 sec) records:1 duplicates:0 warnings:0 [email protected]: Test 22:50:18> Inser T into TI values ( -9,-9); Query OK, 1 row affected, 1 Warning (0.00 sec) [email protected]: Test 22:51:20> show warnings;+---------+------+ ---------------------------------------------+| Level | Code | Message |+---------+------+---------------------------------------------+| Warning | 1264 | Out of range value for column ' No ' at row 1 |+---------+------+---------------------------------------------+1 Row in Set (0.00 sec) [email protected]: Test 22:51:27> SELECT * FROM ti;+------+------+| ID |  No |+------+------+|  127 |   127 | |    -9 |   9 | |    -9 | 0 |+------+------+3 rows in Set (0.00 sec) can see the No field inserted-9, is truncated directly to 0. After adding unsigned to the tiinyint, from 128 to 127 it becomes 0 to 255. [email protected]: Test 22:51:37> insert INTO TI values (1,258); Query OK, 1 row affected, 1 Warning (0.00 sec) [email protected]: Test 22:54:08> SELECT * FROM ti;+------+------+ | ID |  No |+------+------+|  127 |   127 | |    -9 |   9 | |    -9 |    0 | |  1 | 255 |+------+------+4 rows in Set (0.00 sec) This type of field scenario: You can use this type of field if you have a business field that stores only a small number of values, such as gender, State, type, and so on. 2. SmallInt: Only 32768 to 32767 of the size of the number, do not care about the following definition, such as smallint (9). Requires 2 bytes of storage space. When you add unsigned to a field, the number size increases from 0 to 65535. [email protected]: Test 23:00:21> CREATE TABLE TS (ID smallint (1), no smallint (ten) unsigned); Query OK, 0 rows affected (0.01 sec) [email protected]: Test 23:01:51> INSERT INTO TS values ( -32768,32768); Query OK, 1 row Affected (0.00 sec) [email protected]: Test 23:02:30> SELECT * from ts;+--------+-------+| ID | No |+--------+-------+| -32768 | 32768 |+--------+-------+1 row in Set (0.00 sec) from the above we can see that we store a signed value, which is a negative number and a length of 1, which is beyond our setup. In addition, after adding unsigned to the field, the number can store more than 32767 values.  Scenario: This type can be used when the desired value is more than tinyint and does not require more than 65535. 3. Mediumint: Only 8388608 to 8388607 of the size of the number, do not care about the following definition, such as mediumint (9). Requires 3 bytes of storage space. When you add unsigned to a field, the number size increases from 0 to 16777215. [email protected]: Test 23:13:10> CREATE TABLE TM (ID mediumint (1), no Mediumint (ten) unsigned); Query OK, 0 rows affected (0.01 sec) [email protected]: Test 23:13:21> INSERT INTO TM values ( -8388608,16777216); Q Uery OK, 1 row affected, 1 Warning (0.00 sec) [email protected]: Test 23:14:44> SELECT * from tm;+----------+---- ------+| ID | No |+----------+----------+| -8388608 | 16777215 |+----------+----------+1 row in Set (0.00 sec) as can be seen from the above, the field length is 1, and the stored value is far more than 1 bits. field plus unsigned, you can store numbers from 0 to 16777215. Application scenario: When the desired value is greater than the SMThis type can be used when the value of the allint is more, and it does not need to be more than 16777215 in size. 4. Int/integer: can only save 2147483648 to 2147483647 of the size of the number, do not care about the subsequent definition, such as int (9). Requires 4 bytes of storage space. When you add unsigned to a field, the number size increases from 0 to 4294967295. [email protected]: Test 23:14:52> CREATE TABLE tn (ID int (1), no int (ten) unsigned); Query OK, 0 rows affected (0.01 sec) [email protected]: Test 23:22:20> insert INTO TN values ( -2147483648,42949672 96); Query OK, 1 row affected, 1 Warning (0.00 sec) [email protected]: Test 23:22:22> SELECT * from tn;+-------------+ ------------+| ID | No |+-------------+------------+| -2147483648 | 4294967295 |+-------------+------------+1 row in Set (0.00 sec) as can be seen from the above, the field length is 1, and the stored value is far more than 1 bits. field plus unsigned, you can store numbers from 0 to 4294967295. Application scenario: This type is available when the desired value is more than the value of mediumint and does not require more than 4294967295 size. Typically, the primary key field is in the form of an int type increment. 5. bigint: can only save 9223372036854775808 to 9223372036854775807 of the size of the number, do not care about the subsequent definition, such as bigint (9). Requires 8 bytes of storage space. When you add unsigned to a field, the number size increases from 0 to 18446744073709551615. [email protected]: Test 23:22:28> CREATE TABLE TB (ID bigint (1), no bigint (ten) unsigned); Query OK, 0 rows affected (0.01 sec) [email protected]: Test 23:27:40> INSERT into TB values (-9223372036854775808 , 18446744073709551616); Query OK, 1 row affected, 1 warning (0.01 sec) [email protected]: Test 23:28:41> SELECT * from tb;+-------------- --------+----------------------+| ID | No |+----------------------+----------------------+| -9223372036854775808 | 18446744073709551615 |+----------------------+----------------------+1 row in Set (0.00 sec) as can be seen from the above, the field length is 1, The value stored is far more than 1 bits. field plus unsigned, you can store numbers from 0 to 18446744073709551615. Scenario: This type is available when the desired value is more than the value of int and does not require more than 18446744073709551615 size. This type is rarely used and can be saved to a 20-bit numeric size. Summary: In MySQL, we ask for the shorter the better, because the storage capacity is small, to fundamentally reduce the scan of Io, if the key index on this field or build a secondary index, can effectively reduce storage space, and IO pressure.

　　

MYSQL field type tinyint, SMALLINT, Mediumint, INT, BIGINT