当前位置：网站首页>Detailed explanation of integer data type tinyint in MySQL

Detailed explanation of integer data type tinyint in MySQL

2022-04-23 11:06:00 【liming89】

Source of the article ： Learn through http://www.bdgxy.com/

Catalog

1.1tinyint Type specification
1.2 The practice environment explains
1.3 Add unsigned attribute
- 1.3.1SQL Mode turns on strict mode
- 1.3.2SQL Mode is not on strict mode
1.4 Add zerofill attribute
- 1.4.1SQL Mode turns on strict mode
- 1.4.2SQL Mode is not on strict mode
1.5 No addition unsigned and zerofill attribute
- 1.5.1SQL Mode turns on strict mode
- 1.5.2SQL Mode is not on strict mode

1.1 tinyint Type specification

data type	Display length	Occupied bytes	A signed	Unsigned
tinyint	add unsigned/zerofill：3 No addition unsigned/zerofill：4	1(8bit)	-128 to 127	0 to 255

####  Format 
id       tinyint(M)                 [unsigned]     [zerofill]
 Field name    data type ( Display length , Do not specify )      Unsigned            Unsigned and leading zero padding

About tinyint How to get the maximum value

tinyint Occupy 1 byte ,1 Byte usage 8 position , After conversion (2 Of 8 The power minus 1) Namely 255;

About adding unsigned Description after

add unsigned Attribute is followed by unsigned ( The scope is 0~255 The integer of , Because it's an integer , There will be no symbols "-", So it's unsigned )

About adding zerofill Description after

zerofill Attribute will unsigned Attributes are also brought with them , This is unsigned （ The scope is 0~255, The display length is 3）, At the same time
Fill leading zeros （ The value of display length is not reached , for example ： You insert 1, It is shown that 001）.

No addition unsigned and zerofill Explanation

Field is not followed by either of these two properties , It means there is a sign ( The scope is -128~127, Because there are signs "-", All are signed ).

1.2 The practice environment explains

####  The default version of the database and storage engine 
mysql> select @@version,@@default_storage_engine;
+------------+--------------------------+
| @@version  | @@default_storage_engine |
+------------+--------------------------+
| 5.7.28-log | InnoDB                   |
+------------+--------------------------+
1 row in set (0.00 sec)

establish chenliang library

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)

Get into chenliang library , And check whether you have successfully entered the library

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)

1.3 Add unsigned attribute

1.3.1 SQL Mode turns on strict mode

SQL_MODE The strict mode is on in , namely SQL_MODE Parameter contains STRICT_TRANS_TABLES Parameters

####  Set... In session mode sql_mode Contained in the 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)

establish test1 The test table （ It's specified here UNSIGNED, That is to say, there is 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)
　
　 ## id The field type is tinyint unsigned, The scope is 0~255, Its length is 3; because 255 Is the length of the 3;

see test1 Table structure of table

Test insertion range 0~255 Range integers and integers not in the range

mysql> insert into test1 values(-1); # Insert numerical -1, error ( be not in 0~255 Within the scope of )
ERROR 1264 (22003): Out of range value for column ‘id’ at row 1

mysql> insert into test1 values(0); # Insert numerical 0, normal ( stay 0~255 Within the scope of )
Query OK, 1 row affected (0.06 sec)

mysql> insert into test1 values(255); # Insert numerical 255, normal ( stay 0~255 Within the scope of )
Query OK, 1 row affected (0.05 sec)

mysql> insert into test1 values(256); # Insert numerical 256, error ( be not in 0~255 Within the scope of )
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)

1.3.2 SQL Mode is not on strict mode

SQL_MODE Strict mode is not turned on , namely SQL_MODE Parameter does not contain STRICT_TRANS_TABLES Parameters

####  Set session mode sql_mode Contains no 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)

establish test11 surface （ It's specified here UNSIGNED, That is to say, there is 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)
　## id The field type is tinyint unsigned, The scope is 0~255, Its length is 3; because 255 Is the length of the 3;

see test11 Table structure of table

Test insertion range 0~255 Range integers and integers not in the range

mysql> insert into test11(id) values(-1);
Query OK, 1 row affected, 1 warning (0.00 sec)
　　 ## Out of range , No error is reported during insertion ( because sql_mode Strict mode is not enabled in )
　　 ## The data inserted into the table is not -1, It is 0

mysql> insert into test11(id) values(0);
Query OK, 1 row affected (0.01 sec)
## No mistake , Because within the scope , What is inserted into the table is also 0

mysql> insert into test11(id) values(255);
Query OK, 1 row affected (0.01 sec)
## No mistake , Because within the scope , What is inserted into the table is also 255

mysql> insert into test11(id) values(256);
Query OK, 1 row affected, 1 warning (0.00 sec)
　　 ## Out of range , No error is reported during insertion ( because sql_mode Strict mode is not enabled in )
　　 ## The data inserted into the table is not 256, It is 255

mysql> select * from test11;
±-----+
| id |
±-----+
| 0 |
| 0 |
| 255 |
| 255 |
±-----+
4 rows in set (0.00 sec)

1.4 Add zerofill attribute

1.4.1 SQL Mode turns on strict mode

SQL_MODE The strict mode is on in , namely SQL_MODE Parameter contains STRICT_TRANS_TABLES Parameters

##  Set... In session mode sql_mode Contained in the 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)

establish test2 The test table （ It's specified here zerofill, Leading zeros will be filled , And bring unsigned）

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)
## id The field type is tinyint zerofill, The scope is 0~255, Its length is 3; because 255 Is the length of the 3;

see test2 Table structure of table

Test insertion range 0~255 Range integers and integers not in the range

mysql> insert into test2 values(-1); # Insert numerical -1, error ( be not in 0~255 Within the scope of )
ERROR 1264 (22003): Out of range value for column ‘id’ at row 1

mysql> insert into test2 values(0); # Insert numerical 0, normal ( stay 0~255 Within the scope of )
Query OK, 1 row affected (0.06 sec)

mysql> insert into test2 values(255); # Insert numerical 255, normal ( stay 0~255 Within the scope of )
Query OK, 1 row affected (0.05 sec)

mysql> insert into test2 values(256); # Insert numerical 256, error ( be not in 0~255 Within the scope of )
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)

1.4.2 SQL Mode is not on strict mode

SQL_MODE Strict mode is not turned on , namely SQL_MODE Parameter does not contain STRICT_TRANS_TABLES Parameters

##  Set session mode sql_mode Contains no 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)

establish test22 surface （ It's specified here zerofill, Leading zeros will be filled , And bring unsigned）

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)
　## id The field type is tinyint unsigned, The scope is 0~255, Its length is 3; because 255 Is the length of the 3;

see test22 Table structure of table

Test insertion range 0~255 Range integers and integers not in the range

mysql> insert into test22(id) values(-1);
Query OK, 1 row affected, 1 warning (0.00 sec)
　　## Not in scope , No error is reported during insertion ( because sql_mode Strict mode is not enabled in );
　　## But not in the table -1, It is 0, But because of zerofill Parameters , Therefore, leading zero sequence will be filled in during display ;

mysql> insert into test22(id) values(0);
Query OK, 1 row affected (0.01 sec)
　　## No mistake , Because within the scope , What is inserted into the table is also 0

mysql> insert into test22(id) values(255);
Query OK, 1 row affected (0.01 sec)
　　## No mistake , Because within the scope , What is 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 scope , No error is reported during insertion （sql_mode Strict mode is not enabled in ）
　　## But the data in the table is not 256, It is 255

mysql> select * from test22;
±-----+
| id |
±-----+
| 000 |
| 000 |
| 255 |
| 255 |
±-----+
4 rows in set (0.00 sec)

1.5 No addition unsigned and zerofill attribute

1.5.1 SQL Mode turns on strict mode

SQL_MODE The strict mode is on in , namely SQL_MODE Parameter contains STRICT_TRANS_TABLES Parameters

##  Set... In session mode sql_mode Contained in the 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)

establish test3 surface （ No addition 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)
　　## id The type of field is tinyint, Its scope is -128 to 127, Its display length is 4, Because you want to display symbols (“-”)

see test3 Table structure of table

Test insert -128~127 Integers in the range and integers not in the range

mysql> insert into test3(id) values(-129); # Insert numerical -129, error , Out of range
ERROR 1264 (22003): Out of range value for column ‘id’ at row 1

mysql> insert into test3(id) values(-128); # Insert numerical -128, correct , In scope
Query OK, 1 row affected (0.00 sec)

mysql> insert into test3(id) values(127); # Insert numerical 127, correct , In scope
Query OK, 1 row affected (0.01 sec)

mysql> insert into test3(id) values(128); # Insert numerical 128, error , Out of 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)

1.5.2 SQL Mode is not on strict mode

SQL_MODE Strict mode is not enabled in , namely SQL_MODE Parameter does not contain STRICT_TRANS_TABLES Parameters

##  Set session mode sql_mode Contains no 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)

establish test33 surface （ No addition 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)
　　## id The type of field is tinyint, Its scope is -128 to 127, Its display length is 4, Because you want to display symbols (“-”)

see test33 Table structure of table

Test insert -128~127 Integers in the range and integers not in the range

mysql> insert into test33(id) values(-129);
Query OK, 1 row affected, 1 warning (0.00 sec)
　　## Out of range , Insert without error , because sql_mode Strict mode is not enabled in
　　## What is inserted into the table is not -129, It is -128;

mysql> insert into test33(id) values(-128);
Query OK, 1 row affected (0.01 sec)
　　## In scope , Insert without error , Insert as much as you want

mysql> insert into test33(id) values(127);
Query OK, 1 row affected (0.00 sec)
　　## In scope , Insert without error , Insert as much as you want

mysql> insert into test33(id) values(128);
Query OK, 1 row affected, 1 warning (0.01 sec)
　　## Out of range , Insert without error , because sql_mode Strict mode is not enabled in
　　## What is inserted into the table is not 128, It is 127;

mysql> select * from test33;
±-----+
| id |
±-----+
| -128 |
| -128 |
| 127 |
| 127 |
±-----+
4 rows in set (0.00 sec)

This is about mysql Medium integer data type tinyint This is the end of the detailed article , More about mysql Integer data type tinyint Please search rookie tutorial www.piaodoo.com Previous articles or continue to browse the relevant articles below. I hope you can support rookie tutorials in the future www.piaodoo.com！

版权声明
本文为[liming89]所创，转载请带上原文链接，感谢
https://yzsam.com/2022/04/202204231102124639.html