1.1 smallint description

1.2 smallint practice

1.2.1 environmental preparation

1.2.2 plus UNSIGNED parameter

1.2.3 plus ZEROFILL parameter

1.2.4 without UNSIGNED and ZEROFILL parameters

1.3 summary of smallint

1.1 smallint description

1.2 smallint 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 smallint(5) UNSIGNED

-> );

Query OK, 0 rows affected (0.03 sec)

mysql> desc test1;

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

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

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

| id    | smallint(5) unsigned | YES  |     | NULL    |       |

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

1 row in set (0.00 sec)

**Test 01: Test insertion range 0~65535And over 65535Integer

mysql> insert into test1 values(0); <==Insert value 0, correct (not over range 0~65535)

Query OK, 1 row affected (0.03 sec)

mysql> insert into test1 values(65535); <==Insert value 65535, correct (not over range 0~65535)

Query OK, 1 row affected (0.03 sec)

mysql> insert into test1 values(65536); <=Insert value 65536,error(Exceeded smallint Scope 0~65535)

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

mysql> select * from test1;

+-------+

| id    |

+-------+

| 0     |

| 65535 |

+-------+

2 rows in set (0.00 sec)

**Test 02: test-32768~32767Whether the range data can be inserted normally

mysql> insert into test1 values(-1);

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

^==Insert a negative number and report an error, id Field added unsigned parameter,smallint The range of is 0~65535)

mysql> INSERT INTO test1 values(-128);

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

^==insert-128,Report errors(Because when the table is built, the id Field added unsigned Parameters, smllint The range of is 0~65535)

mysql> insert into test1 values(-32769);

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

^==insert-32769,Report errors(Because when the table is built, the id Field added unsigned Parameters, smllint The range of is 0~65535)

mysql> select * from test1;

+-------+

| id    |

+-------+

| 0     |

| 65535 |

+-------+

2 rows in set (0.01 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 smallint(5) zerofill

-> );

Query OK, 0 rows affected (0.12 sec)

mysql> desc test2;

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

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

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

| id    | smallint(5) 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~65535The sum of integers of exceeds 65535Integer

mysql> insert into test2 values(0); <==Insert integer 0 at 0~65535 In scope, correct

Query OK, 1 row affected (0.04 sec)

mysql> insert into test2 values(65535); <==Insert integer 65535 at 0~65535 In scope, correct

Query OK, 1 row affected (0.05 sec)

mysql> insert into test2 values(65536); <==Insert integer 65535, not at 0~65535 In range, error

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

mysql> select * from test2;

+-------+

| id    |

+-------+

| 00000 |

| 65535 |

+-------+

2 rows in set (0.00 sec)

**Test 02: test-32768~32767Whether 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

^==Insert negative integer-1，Error (because in id Field added zerofill Parameter, it will unsigned Take it, too, so

id Field range is 0~65535)

mysql> INSERT INTO test2 values(-128);

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

^==Insert negative integer-128，Error (because in id Field added zerofill Parameter, it will unsigned Take it, too

with id Field range is 0~65535)

mysql> insert into test2 values(-32769);

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

^==Insert negative integer-32769，Error (because in id Field added zerofill Parameter, it will unsigned Take it, too.

therefore id Field range is 0~65535,And the signed range is just-32768~32767)

mysql> select * from test2;

+-------+

| id    |

+-------+

| 00000 |

| 65535 |

+-------+

2 rows in set (0.00 sec)

1.2.4 without UNSIGNED and ZEROFILL parameters

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

mysql> create table if not exists test3(

-> id smallint(5)

-> );

Query OK, 0 rows affected (0.08 sec)

mysql> desc test3;

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

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

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

| id    | smallint(5) | YES  |     | NULL    |       |

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

1 row in set (0.01 sec)

**Test 01: Test insert integer 0~32767And over 32767Integer

mysql> insert into test3 values(0);

Query OK, 1 row affected (0.00 sec)

^==Insert integer 0, correct (in range-32768~32767 Within limits)

mysql> insert into test3 values(32767);

Query OK, 1 row affected (0.04 sec)

^==Insert integer 32767, correct (in range-32768~32767 Within limits)

mysql> insert into test3 values(32768);

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

^==Insert integer 32768, error (not in range-32768~32767 Within limits)

mysql> select * from test3;

+-------+

| id    |

+-------+

| 0     |

| 32767 |

+-------+

2 rows in set (0.00 sec)

**Test 02: Test insert negative-1~-32768And less than-32768Negative number

mysql> INSERT INTO test3 values(-1);

Query OK, 1 row affected (0.10 sec)

^==Insert negative integer-1，Correct (in range-32768~32767 Within limits)

mysql> INSERT INTO test3 values(-128);

Query OK, 1 row affected (0.02 sec)

^==Insert negative integer-128，Correct (in range-32768~32767 Within limits)

mysql> insert into test3 values(-32769);

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

^==Insert negative integer-32769，Error (not in range-32768~32767 Within limits)

mysql> select * from test3;

+-------+

| id    |

+-------+

| 0     |

| 32767 |

| -1    |

| -128  |

+-------+

4 rows in set (0.00 sec)

1.3 summary of smallint