Detailed explanation of the integer data type tinyint in MySQL

#### format id tinyint(M) [unsigned] [zerofill]
Field name Data type (display length, not specified) Unsigned Unsigned with leading zeros #### How is the maximum value of tinyint obtained? Tinyint occupies 1 byte, 1 byte occupies 8 bits, and after conversion (2 to the 8th power minus 1) is 255;

#### About adding unsigned After adding the unsigned attribute, it is unsigned (the range is an integer from 0 to 255. Because it is an integer, there will be no sign "-", so it is unsigned)

#### About adding zerofill The zerofill attribute will also bring the unsigned attribute, so it is unsigned (the range is 0~255, and the display length is 3). At the same time, leading zeros will be filled (the value does not reach the display length, for example: if you insert 1, 001 will be displayed).

#### Description field without unsigned and zerofill If neither of these two attributes is added after the field, it means it is signed (the range is -128~127, because there is a sign "-", so everything is signed).

#### Database version and default storage engine mysql> select @@version,@@default_storage_engine;
+------------+--------------------------+
| @@version | @@default_storage_engine |
+------------+--------------------------+
| 5.7.28-log | InnoDB |
+------------+--------------------------+
1 row in set (0.00 sec)
 
 
#### Create chenliang database mysql> create database if not exists chenliang;
Query OK, 1 row affected (0.03 sec)
 
mysql> show databases like "chenliang";
+----------------------+
| Database (chenliang) |
+----------------------+
| chenliang |
+----------------------+
1 row in set (0.03 sec)
 
 
#### Enter the chenliang database and check whether you have successfully entered the database. mysql> use chenliang;
Database changed
 
mysql> select database();
+------------+
| database() |
+------------+
| chenliang |
+------------+
1 row in set (0.01 sec)


#### Check whether the transaction is automatically committed mysql> select @@global.autocommit;
+---------------------+
| @@global.autocommit |
+---------------------+
| 1 |
+---------------------+
1 row in set (0.00 sec)

#### Set the sql_mode in session mode to include strict_trans_tables
mysql> set session sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
Query OK, 0 rows affected (0.00 sec)
 
mysql> select @@sql_mode\G
*************************** 1. row ***************************
@@sql_mode: STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)


#### Create the test1 test table (UNSIGNED is specified here, which means no sign)
mysql> CREATE TABLE IF NOT EXISTS test1(
    -> id tinyint UNSIGNED
    ->)engine=innodb character set utf8 collate utf8_general_ci;
Query OK, 0 rows affected (0.06 sec)
 　 
  ## The id field type is tinyint unsigned, the range is 0~255, and its length is 3; because the length of 255 is 3;


#### View the table structure of test1 mysql> desc test1;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id | tinyint(3) unsigned | YES | | NULL | |
+-------+---------------------+------+-----+---------+-------+
1 row in set (0.03 sec)


#### Test inserting integers in the range 0 to 255 and integers not in this rangemysql> insert into test1 values(-1); # Inserting the value -1, error (not in the range 0 to 255)
ERROR 1264 (22003): Out of range value for column 'id' at row 1
 
mysql> insert into test1 values(0); # Insert value 0, normal (in the range of 0 to 255)
Query OK, 1 row affected (0.06 sec)
 
mysql> insert into test1 values(255); # insert the value 255, normal (in the range of 0 to 255)
Query OK, 1 row affected (0.05 sec)
 
mysql> insert into test1 values(256); # Insert value 256, error (not in the range of 0 to 255)
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> select * from test1;
+------+
|id|
+------+
| 0 |
| 255 |
+------+
2 rows in set (0.00 sec)

#### Set the session mode so that the sql_mode does not contain the strit_trans_tables variable mysql> set session sql_mode="NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select @@sql_mode\G
*************************** 1. row ***************************
@@sql_mode: NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)



#### Create the test11 table (UNSIGNED is specified here, which means no sign)
mysql> create table if not exists test11(
    -> id tinyint unsigned
    ->)engine=innodb character set utf8 collate utf8_general_ci;
Query OK, 0 rows affected (0.00 sec)
  ## The id field type is tinyint unsigned, the range is 0~255, and its length is 3; because the length of 255 is 3;



#### View the table structure of test11 mysql> desc test11;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id | tinyint(3) unsigned | YES | | NULL | |
+-------+---------------------+------+-----+---------+-------+
1 row in set (0.00 sec)



#### Test the insertion of integers in the range 0 to 255 and integers not in this range mysql> insert into test11(id) values(-1);
Query OK, 1 row affected, 1 warning (0.00 sec)
  ## Not in range, no error is reported when inserting (because strict mode is not enabled in sql_mode)
  ## The data inserted into the table is not -1, but 0
 
mysql> insert into test11(id) values(0);
Query OK, 1 row affected (0.01 sec)
    ## No error is reported, because within the range, the number inserted into the table is also 0
 
mysql> insert into test11(id) values(255);
Query OK, 1 row affected (0.01 sec)
    ## No error is reported, because it is within the range, and the number inserted into the table is also 255
 
mysql> insert into test11(id) values(256);
Query OK, 1 row affected, 1 warning (0.00 sec)
  ## Not in range, no error is reported when inserting (because strict mode is not enabled in sql_mode)
  ## The data inserted into the table is not 256, but 255
  
mysql> select * from test11;
+------+
|id|
+------+
| 0 |
| 0 |
| 255 |
| 255 |
+------+
4 rows in set (0.00 sec)

## Set the sql_mode in session mode to include strict_trans_tables
mysql> set session sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
Query OK, 0 rows affected (0.00 sec)
 
mysql> select @@sql_mode\G
*************************** 1. row ***************************
@@sql_mode: STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)
 
## Create the test2 test table (zerofill is specified here, leading zeros will be filled, and unsigned will also be included)
mysql> CREATE TABLE IF NOT EXISTS test2(
    -> id tinyint zerofill
    ->)engine=innodb character set utf8 collate utf8_general_ci;
Query OK, 0 rows affected (0.01 sec)
     ## The id field type is tinyint zerofill, the range is 0~255, and its length is 3; because the length of 255 is 3;

 ## View the table structure of test2 mysql> desc test2;
+-------+------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| id | tinyint(3) unsigned zerofill | YES | | NULL | |
+-------+------------------------------+------+-----+---------+-------+
1 row in set (0.00 sec)
 
## Test the insertion range of 0~255 range integers and integers not in this rangemysql> insert into test2 values(-1); # Insert the value -1, error (not in the range of 0~255)
ERROR 1264 (22003): Out of range value for column 'id' at row 1
 
mysql> insert into test2 values(0); # Insert value 0, normal (in the range of 0 to 255)
Query OK, 1 row affected (0.06 sec)
 
mysql> insert into test2 values(255); # insert the value 255, normal (in the range of 0 to 255)
Query OK, 1 row affected (0.05 sec)
 
mysql> insert into test2 values(256); # Insert value 256, error (not in the range of 0 to 255)
ERROR 1264 (22003): Out of range value for column 'id' at row 1
 
mysql> select * from test2;
+------+
|id|
+------+
| 000 |
| 255 |
+------+
2 rows in set (0.00 sec)

## Set the session mode to exclude the strit_trans_tables variable in sql_mode mysql> set session sql_mode="NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select @@sql_mode\G
*************************** 1. row ***************************
@@sql_mode: NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)

## Create the test22 table (zerofill is specified here, leading zeros will be filled, and unsigned will also be included)
mysql> create table if not exists test22(
    -> id tinyint zerofill
    ->)engine=innodb character set utf8 collate utf8_general_ci;
Query OK, 0 rows affected (0.00 sec)
  ## The id field type is tinyint unsigned, the range is 0~255, and its length is 3; because the length of 255 is 3;
 
## View the table structure of test22 mysql> desc test22;
+-------+------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| id | tinyint(3) unsigned zerofill | YES | | NULL | |
+-------+------------------------------+------+-----+---------+-------+
1 row in set (0.00 sec)
 
## Test the insertion of integers in the range 0 to 255 and integers not in this rangemysql> insert into test22(id) values(-1);
Query OK, 1 row affected, 1 warning (0.00 sec)
  ## Not in range, no error is reported when inserting (because strict mode is not enabled in sql_mode);
  ## But what is in the table is not -1, but 0. However, because of the zerofill parameter, it will be filled with leading zeros when displayed;
 
mysql> insert into test22(id) values(0);
Query OK, 1 row affected (0.01 sec)
  ## No error is reported, because within the range, the number inserted into the table is also 0
 
mysql> insert into test22(id) values(255);
Query OK, 1 row affected (0.01 sec)
  ## No error is reported, because it is within the range, and the number inserted into the table is also 255
 
mysql> insert into test22(id) values(256); 
Query OK, 1 row affected, 1 warning (0.00 sec)
  ## Not in range, no error reported when inserting (strict mode is not enabled in sql_mode)
  ## But the data in the table is not 256, but 255
 
mysql> select * from test22;
+------+
|id|
+------+
| 000 |
| 000 |
| 255 |
| 255 |
+------+
4 rows in set (0.00 sec)

## Set the sql_mode in session mode to include strict_trans_tables
mysql> set session sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
Query OK, 0 rows affected (0.00 sec)
 
mysql> select @@sql_mode\G
*************************** 1. row ***************************
@@sql_mode: STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)
 
## Create test3 table (without unsigned and zerofill)
mysql> CREATE TABLE test3(
    -> id tinyint
    ->)engine=innodb character set utf8 collate utf8_general_ci;
Query OK, 0 rows affected (0.06 sec)
   ## The type of the id field is tinyint, its range is -128 to 127, and its display length is 4 because the sign ("-") is displayed
 
## View the table structure of test3 mysql> desc test3;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | tinyint(4) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
1 row in set (0.01 sec)
 
## Test inserting integers in the range of -128 to 127 and integers not in the rangemysql> insert into test3(id) values(-129); # Inserting the value -129, error, not in the rangeERROR 1264 (22003): Out of range value for column 'id' at row 1
 
mysql> insert into test3(id) values(-128); # Insert value -128, correct, within the range Query OK, 1 row affected (0.00 sec)

mysql> insert into test3(id) values(127); # Insert value 127, correct, within the range Query OK, 1 row affected (0.01 sec)
 
mysql> insert into test3(id) values(128); # Insert value 128, error, not in range ERROR 1264 (22003): Out of range value for column 'id' at row 1
 
mysql> select * from test3;
+------+
|id|
+------+
|-128 |
| 127 |
+------+
2 rows in set (0.00 sec)

## Set the session mode to exclude the strit_trans_tables variable in sql_mode mysql> set session sql_mode="NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
mysql> select @@sql_mode\G
*************************** 1. row ***************************
@@sql_mode: NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)
 
## Create test33 table (without unsigned and zerofill)
mysql> CREATE TABLE test33(
    -> id tinyint
    ->)engine=innodb character set utf8 collate utf8_general_ci;
Query OK, 0 rows affected (0.00 sec)
  ## The type of the id field is tinyint, its range is -128 to 127, and its display length is 4 because the sign ("-") is displayed
 
## View the table structure of test33 mysql> desc test33;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | tinyint(4) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)
 

## Test inserting integers in the range of -128 to 127 and integers not in this rangemysql> insert into test33(id) values(-129);
Query OK, 1 row affected, 1 warning (0.00 sec)
  ## Not in range, no error is reported during insertion, because strict mode is not enabled in sql_mode## The value inserted into the table is not -129, but -128;
 
mysql> insert into test33(id) values(-128);
Query OK, 1 row affected (0.01 sec)
  ## If the value is within the range, no error will be reported when inserting. The value inserted is the same as the value in the range.mysql> insert into test33(id) values(127);
Query OK, 1 row affected (0.00 sec)
  ## If the value is within the range, no error will be reported. The value inserted is the same as the value in the range.mysql> insert into test33(id) values(128);
Query OK, 1 row affected, 1 warning (0.01 sec)
  ## Not in range, no error is reported during insertion, because strict mode is not enabled in sql_mode## The value inserted into the table is not 128, but 127;
 
mysql> select * from test33;
+------+
|id|
+------+
|-128 |
|-128 |
| 127 |
| 127 |
+------+
4 rows in set (0.00 sec)