1.1 tinyint description
type |
length |
When not specified, the length is |
Occupying byte |
Range |
Signed |
Unsigned |
tinyint |
3 |
4 |
1(8bit) |
-128~255 |
-128~127 |
0~255 |
id tinyint(M) [UNSIGNED] [ZEROFILL]
Field name field type length unsigned leading fill
unsigned:
01: after tinyint(M) and unsigned, there is no sign (the range of tinyint is 0-255)
02: tinyint(M) is signed without unsigned (the range of tinyint is - 128 ~ 127)
zerofill:
01: leading zero filling
02: tinyint(M) with zerofile will also bring the unsigned parameter (tinyint range 0-255)
1.2 practical inspection
##Establish chenlianglibrary
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 chenlianglibrary
mysql> use chenliang;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| chenliang |
+------------+
1 row in set (0.01 sec)
Test 1: test field plus UNSIGNEDInsert data after
##Establish test1Test table(It's specified here UNSIGNED，That is, no sign)
mysql> CREATE TABLE IF NOT EXISTS test1(
-> id tinyint(3) UNSIGNED
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> desc test1;
+-------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| id | tinyint(3) unsigned | YES | | NULL | |
+-------+----------------------+------+-----+---------+-------+
1 row in set (0.03 sec)
****Test 01: Test insertion range 0~255The sum of integers of 255Integer
mysql> INSERT INTO test1 values(0); <==Insert value 0, normal (not out of range)
Query OK, 1 row affected (0.06 sec)
mysql> INSERT INTO test1 values(255); <==Insert value 255, normal (not out of range)
Query OK, 1 row affected (0.05 sec)
mysql> INSERT INTO test1 values(256); <=Insert value 256,error(Exceeded tinyint Scope)
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)
***Test 02: Test-128~127 Whether the range data can be inserted normally
mysql> INSERT INTO test1 values(-1); <==Insert a negative number and report an error
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> INSERT INTO test1 values(-128); <==insert-128,Report errors
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> INSERT INTO test1 values(-129); <==insert-129,Report errors
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)
Test 2: test field plus ZEROFILLInsert data in the upper and lower case
##Establish test2 Table,It's specified here zerofill,Leading zero filling)
mysql> CREATE TABLE IF NOT EXISTS test2(
-> id tinyint(3) ZEROFILL
-> );
Query OK, 0 rows affected (0.07 sec)
mysql> desc test2;
+-------+------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| id | tinyint(3) unsigned zerofill | YES | | NULL | |
+-------+------------------------------+------+-----+---------+-------+
1 row in set (0.00 sec)
^Designated only unsigned，There will be no zerofill
^Designated only zerofill When you bring it unsigned
****Test 01: Test insertion range 0~255The sum of integers of 255Integer
mysql> INSERT INTO test2 values(0);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO test2 values(255);
Query OK, 1 row affected (0.08 sec)
mysql> INSERT INTO test2 values(256);
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)
***Test 02: test-128~127 Whether the range data can be inserted normally
mysql> INSERT INTO test2 values(-1);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> INSERT INTO test2 values(-128);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> INSERT INTO test2 values(-129);
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)
Test 1: test field is not addedunsigned and ZEROFILLInsert data in the upper and lower case
##Establish test3 Table (no addition) unsigned and zerofill)
mysql> CREATE TABLE test3(
-> id tinyint(3)
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> desc test3;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | tinyint(3) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
1 row in set (0.01 sec)
****Test 01: Test insert integer
mysql> INSERT INTO test3 values(0);
Query OK, 1 row affected (0.05 sec)
mysql> INSERT INTO test3 values(128);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> select * from test3;
+------+
| id |
+------+
| 0 |
+------+
1 row in set (0.01 sec)
***Test 02: Test insert negative
mysql> INSERT INTO test3 values(-1);
Query OK, 1 row affected (0.10 sec)
mysql> INSERT INTO test3 values(-128);
Query OK, 1 row affected (0.02 sec)
mysql> INSERT INTO test3 values(-129);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> select * from test3;
+------+
| id |
+------+
| 0 |
| -1 |
| -128 |
+------+
3 rows in set (0.00 sec)
1.3 tinyint summary
Format:
id tinyint(M) [UNSIGNED] [ZEROFILL]
Field name field type (length) [unsigned] [leading fill]
unsigned:
01: after tinyint(M) and unsigned, there is no sign (the range of tinyint is 0-255)
02: tinyint(M) is signed without unsigned (the range of tinyint is - 128 ~ 127)
zerofill:
01: perform leading zero filling (insert the value 1, the table shows 001, because the length of tinyint is 3)
02: tinyint(M) with zerofile will also bring the unsigned parameter (tinyint range 0-255)