Shulou(Shulou.com)06/02 Report--

This article mainly explains "how to use the integer data type tinyint in mysql". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn how to use the integer data type tinyint in mysql.

1.1 tinyint type description data type display length occupies bytes signed unsigned tinyint plus unsigned/zerofill:3

Do not add unsigned/zerofill:41 (8bit)-128to 1270 to 255mm tinyint format id tinyint (M) [unsigned] [zerofill] Field name data type (display length, not specified) unsigned unsigned and leading zero padding # about how the maximum value of tinyint is obtained tinyint occupies 1 byte and 1 byte occupies 8 bits After conversion (2 to the 8th minus 1), it is 255. # the instructions after adding unsigned and the unsigned attribute are unsigned (the range is an integer of 0x255, because it is an integer, there is no sign "-", so it is unsigned) # about the instructions after adding zerofill, the zerofill attribute will also bring the unsigned attribute, so it is unsigned (the range is 0per255 and the display length is 3). At the same time, leading zeros will be filled (the value that does not reach the display length is not reached) For example, if you insert 1, it shows 001). The description field without unsigned and zerofill without either of these attributes indicates that it is signed (the range is-128-127, because there is a symbol "-", all are signed). 1.2 practice environment description # 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 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) # enter the chenliang library and check whether you have successfully entered the mysql > use chenliang;Database changed mysql > select database () under the library +-+ | 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.Plus unsigned attribute 1.3.1 SQL mode enables strict mode

Strict mode is turned on in SQL_MODE, that is, the SQL_MODE parameter contains the STRICT_TRANS_TABLES parameter

# set sql_mode in session mode to include strict_trans_tablesmysql > 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\ gateway * 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_SUBSTITUTION1 row in set (0.00 sec) # create a test1 test table (UNSIGNED is specified here Unsigned) 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 field type is tinyint unsigned, the range is 0x255, and its length is 3, because the length of 255is 3. View the table structure of the test1 table mysql > desc test1 +-+ | Field | Type | Null | Key | Default | Extra | +-- -- + | id | tinyint (3) unsigned | YES | | NULL | | +-+-+ 1 row in set (0.03 sec) # Test Insert range 0,255 range integers and integers that are not in this range mysql > insert into test1 values (- 1) # insert 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 test1 values (0); # insert value 0, normal (in range 0,255) Query OK, 1 row affected (0.06 sec) mysql > insert into test1 values (255); # insert value 255, normal (in range 0,255) Query OK, 1 row affected (0.05 sec) mysql > insert into test1 values # ERROR 1264 (22003): Out of range value for column 'id' at row 1mysql > select * from test1;+-+ | id | +-+ | 0 | | 255 | + 2 rows in set (0.00 sec) 1.3.2 strict mode is not enabled in SQL mode

Strict mode is not enabled for SQL_MODE, that is, STRICT_TRANS_TABLES parameter is not included in the SQL_MODE parameter.

# set 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" in sql_mode in session mode Query OK, 0 rows affected, 1 warning (0.00 sec) mysql > select @ @ sql_mode\ gateway * 1. Row * * @ sql_mode: NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER NO_ENGINE_SUBSTITUTION1 row in set (0.00 sec) # create the test11 table (UNSIGNED is specified here Unsigned) 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 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 the test11 table mysql > desc test11 +-+ | Field | Type | Null | Key | Default | Extra | +-- -+ | id | tinyint (3) unsigned | YES | | NULL | | +-+-+ 1 row in set (0.00 sec) # Test Insert range 0,255 range integers and integers that are not in this range mysql > insert into test11 (id) values (- 1) Query OK, 1 row affected, 1 warning (0.00 sec) # # does not report an error (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 in the range, the data 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 in the range, the data inserted into the table is also 255mysql > insert into test11 (id) values; Query OK, 1 row affected, 1 warning (0.00sec) # # not in range, insert no error (because strict mode is not turned on in sql_mode) # # the data inserted into the table is not 256, but 255mysql > select * from test11 +-+ | id | +-+ | 0 | 0 | 0 | 255 | 255 | +-+ 4 rows in set (0.00 sec) 1.4 plus zerofill attribute 1.4.1 SQL mode is enabled for strict mode

Strict mode is turned on in SQL_MODE, that is, the SQL_MODE parameter contains the STRICT_TRANS_TABLES parameter

# # setting strict_trans_tablesmysql > 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" in sql_mode in session mode Query OK, 0 rows affected (0.00 sec) mysql > select @ @ sql_mode\ gateway * 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_SUBSTITUTION1 row in set (0.00 sec) # # create a test2 test table (zerofill is specified here, leading zero padding will be accompanied by 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 field type is tinyint zerofill, the range is 0x255, and its length is 3; because the length of 255is 3; # # View the table structure mysql > desc test2 of the test2 table +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | id | tinyint (3) unsigned zerofill | YES | | NULL | | +-+-- -+-+ 1 row in set (0.00 sec) # # Test inserting range 0,255 integers and integers that are not in that range mysql > insert into test2 values (- 1) # insert 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 range 0,255) Query OK, 1 row affected (0.06 sec) mysql > insert into test2 values (255); # insert value 255, normal (in range 0,255) Query OK, 1 row affected (0.05 sec) mysql > insert into test2 values # 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 strict mode is not enabled in SQL mode

Strict mode is not enabled for SQL_MODE, that is, STRICT_TRANS_TABLES parameter is not included in the SQL_MODE parameter.

# # setting 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" in session mode Query OK, 0 rows affected, 1 warning (0.00 sec) mysql > select @ @ sql_mode\ gateway * 1. Row * * @ sql_mode: NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER NO_ENGINE_SUBSTITUTION1 row in set (0.00 sec) # # create test22 table (zerofill is specified here, leading zero padding will be accompanied by 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 field type is tinyint unsigned, the range is 0000255, and its length is 3; because the length of 255is 3; # # View the table structure mysql > desc test22 of the test22 table +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | id | tinyint (3) unsigned zerofill | YES | | NULL | | +-+-- -+-+ 1 row in set (0.00 sec) # # Test insert range 0,255 integers and integers not in that range mysql > insert into test22 (id) values (- 1) Query OK, 1 row affected, 1 warning (0.00 sec) # # out of range, insert correctly (because strict mode is not enabled in sql_mode); # # but not-1, but 0 into the table, but leading zero padding when displayed because of the zerofill parameter; mysql > insert into test22 (id) values (0) Query OK, 1 row affected (0.01sec) # # did not report an error, because in the range, 0 mysql > insert into test22 (id) values is also inserted into the table; Query OK, 1 row affected (0.01sec) # No error is reported, because in the range, 255mysql > insert into test22 (id) values is also inserted into the table Query OK, 1 row affected, 1 warning (0.00 sec) # # out of range, insert error (strict mode is not enabled in sql_mode) # # but the data into the table is not 256, but 255mysql > select * from test22 +-+ | id | +-+ | 000 | | 000 | 255 | + 4 rows in set (0.00 sec) 1.5 strict mode is enabled without unsigned and zerofill attributes 1.5.1 SQL

Strict mode is turned on in SQL_MODE, that is, the SQL_MODE parameter contains the STRICT_TRANS_TABLES parameter

# # setting strict_trans_tablesmysql > 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" in sql_mode in session mode Query OK, 0 rows affected (0.00 sec) mysql > select @ @ sql_mode\ gateway * 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_SUBSTITUTION1 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, the type of 0 rows affected (0.06 sec) # # id field is tinyint, which ranges from-128to 127and its display length is 4, because you want to display the symbol ("-") # # to view the table structure of the test3 table mysql > desc test3 +-+ | Field | Type | Null | Key | Default | Extra | +-+-+ | Id | tinyint (4) | YES | | NULL | | +-+-+ 1 row in set # # Test inserts integers in the range of-128 to 127 and integers that are not in this range mysql > insert into test3 (id) values (- 129) # insert value-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 value-128, correct, in range Query OK, 1 row affected (0.00sec) mysql > insert into test3 (id) values (127127correct, in range Query OK, 1 row affected (01sec) mysql > insert into test3 (id) values (127) # ERROR 1264 (22003): Out of range value for column 'id' at row 1 mysql > select * from test3;+-+ | id | +-+ |-128 | | 127th | +-+ 2 rows in set (0.00 sec) 1.5.2 SQL mode is not enabled

Strict mode is not enabled in SQL_MODE, that is, the STRICT_TRANS_TABLES parameter is not included in the SQL_MODE parameter

# # setting 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" in session mode Query OK, 0 rows affected, 1 warning (0.00 sec) mysql > select @ @ sql_mode\ gateway * 1. Row * * @ sql_mode: NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER NO_ENGINE_SUBSTITUTION1 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, the type of 0 rows affected (0.00 sec) # # id field is tinyint, which ranges from-128to 127and its display length is 4 because you want to display the symbol ("-") # # to view the table structure of the test33 table mysql > desc test33 +-+ | Field | Type | Null | Key | Default | Extra | +-+-+ | Id | tinyint (4) | YES | | NULL | | +-+ 1 row in set (0.00 sec) # # Test inserts integers in the range of-128 to 127 and integers that are not in this range mysql > insert into test33 (id) values (- 129) Query OK, 1 row affected, 1 warning (0.00 sec) # # does not report an error because strict mode is not turned on in sql_mode # # insert into the table is not-129, but-128; mysql > insert into test33 (id) values (- 128); Query OK, 1 row affected (0.01sec) # # in the range, the number of inserts is mysql > insert into test33 (id) values Query OK, 1 row affected (0.00 sec) # # in the range, insert no error, insert as many as mysql > insert into test33 (id) values; Query OK, 1 row affected, 1 warning (0.01 sec) # # is not in the range, insert no error, because strict mode is not open in sql_mode # # insert into the table is not 128, but 127; mysql > select * from test33 +-+ | id | +-+ |-128| 127th | +-+ 4 rows in set (0.00 sec) so far, I believe you have a better understanding of "how to use the integer data type tinyint in mysql". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.