1.1 tinyint description

 id        tinyint(M)    [UNSIGNED] [ZEROFILL]

Field name field type (length) [unsigned] [leading fill]

  unsigned:

01: tinyint(M) followed by the unsigned parameter is unsigned (tinyint range is 0 ~ 255)

02: tinyint(M) without unsigned parameter and zerofill parameter is signed (tinyint range is - 128 ~ 127)

  zerofill:

01: leading zero filling

After adding zerofile to tinyint(M), the unsigned parameter will also be brought (tinyint range 0-255)

1.2 tinying practice

1.2.1 environmental preparation

##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)

1.2.2 add unsigned parameter

##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)

^==surface test1 Of id Field added unsigned Parameter, then id The range of the field is 0~255

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~255 Positive integers of and greater than 255

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 (beyond tinyint range)

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 insertion range-1~-128Negative integer of range

mysql> INSERT INTO test1 values(-1);  #Insert negative integer-1, error reporting

ERROR 1264 (22003): Out of range value for column 'id' at row 1

mysql> INSERT INTO test1 values(-128); #Insert negative integer-128, error reporting

ERROR 1264 (22003): Out of range value for column 'id' at row 1

mysql> INSERT INTO test1 values(-129); #Insert negative integer - 129, error reporting

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.2.3 add zerofill parameter

##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)

^==surface test2 Of id Field added zerofill Parameter, then id Field range is 0~255，Because add

Up. zerofill After the parameter, the unsigned Parameters are also included

mysql> desc test2;

+-------+------------------------------+------+-----+---------+-------+

| Field | Type                         | Null | Key | Default | Extra |

+-------+------------------------------+------+-----+---------+-------+

| id    | tinyint(3) unsigned zerofill | YES  |     | NULL    |       |

+-------+------------------------------+------+-----+---------+-------+

1 row in set (0.00 sec)

****Test 01: Test insertion range 0~255The sum of positive integers for exceeds 255Integer

mysql> INSERT INTO test2 values(0); #Insert value 0, normal (not out of range)

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO test2 values(255); #Insert value 255, normal (not out of range)

Query OK, 1 row affected (0.08 sec)

mysql> INSERT INTO test2 values(256); #Insert value 256, error (out of range)

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 insertion-1~-128Negative integer of range

mysql> INSERT INTO test2 values(-1);   #Insert negative integer-1, error reporting

ERROR 1264 (22003): Out of range value for column 'id' at row 1

mysql> INSERT INTO test2 values(-128); #Insert negative integer-128, error reporting

ERROR 1264 (22003): Out of range value for column 'id' at row 1

mysql> INSERT INTO test2 values(-129); #Insert negative integer - 129, error reporting

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.2.4 without unsigned and zerofill parameters

##Establish test3 Table (No addition unsigned and zerofill)

mysql> CREATE TABLE test3(

-> id tinyint(3)

-> );

Query OK, 0 rows affected (0.06 sec)

^==surface test3 Of id Field not added unsigned and zerofill Parameter, then id The range of fields is

-128~127

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 0~127The sum of positive integers of exceeds 127Positive 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 insertion-1~-128Less than-128Negative integer

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