mysql 数据类型 tinyint,MySQL数据类型数值型之整数型之tinyint

##创建chenliang库

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)

##进入chenliang库

mysql> use chenliang;

Database changed

mysql> select database();

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

| database()  |

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

| chenliang   |

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

1 row in set (0.01 sec)

测试一：测试字段加上UNSIGNED后情况下插入数据的情况

##创建test1测试表(这里指定了UNSIGNED，也就是无符号)

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)

****测试01：测试插入范围0~255的整数和走近255的整数

mysql> INSERT INTO test1 values(0); <==插入数值0，正常(没有超过范围)

Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO test1 values(255); <==插入数值255，正常(没有超过范围)

Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO test1 values(256); <=插入数值256,错误(超过了tinyint的范围)

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)

***测试02：测试-128~127范围的数据是否能够正常插入

mysql> INSERT INTO test1 values(-1); <==插入负数，报错

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

mysql> INSERT INTO test1 values(-128); <==插入-128,报错

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

mysql> INSERT INTO test1 values(-129); <==插入-129,报错

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)

测试二：测试字段加ZEROFILL上后情况下插入数据的情况

##创建test2表，(这里指定了zerofill,也就是前导零填充)

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)

^只指定unsigned，不会有zerofill

^只指定zerofill时，会带上unsigned

****测试01：测试插入范围0~255的整数和走近255的整数

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)

***测试02：测试-128~127范围的数据是否能够正常插入

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)

测试一：测试字段不加unsigned和ZEROFILL上后情况下插入数据的情况

##创建test3表(不加unsigned和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)

****测试01：测试插入整数

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)

***测试02：测试插入负数

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)