设为首页 加入收藏

TOP

数据类型之【数值类型】的整数型tinyint(一)
2019-01-27 04:08:15 】 浏览:60
Tags:数据 类型 数值 整数 tinyint
1.1 tinyint说明 1.png

id tinyint(M) [UNSIGNED] [ZEROFILL]

字段名 字段类型(长度) [无符号] [前导填充]

unsigned:

01:tinyint(M)后面加上unsigned参数后,就是无符号(tinyint范围就是0~255)

02:tinyint(M)后面不加unsigned参数,且不加zerofill参数,就是有符号(tinyint范围就是-128~127)

zerofill:

01:进行前导零填充

02:tinyint(M)加上zerofile后,同时也会把unsigned参数也带上(tinyint范围0~255)

1.2 tinying实践

1.2.1 环境准备

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

1.2.2 加unsigned参数

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

mysql> CREATE TABLE IF NOT EXISTS test1(

-> id tinyint(3) UNSIGNED

-> );

Query OK, 0 rows affected (0.06 sec)

^==表test1的id字段加了unsigned参数,那么id字段的范围就是0~255

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:测试插入范围-1~-128范围的负整数

mysql> INSERT INTO test1 values(-1); #插入负整数-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)

1.2.3 加zerofill参数

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

mysql> CREATE TABLE IF NOT EXISTS test2(

-> id tinyint(3) ZEROFILL

-> );

Query OK, 0 rows affected (0.07 sec)

^==表test2的id字段加了zerofill参数,那么id字段的范围为0~255,因为加

上了zerofill参数后,会同时把unsigned参数也带上

mysql> desc test2;

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

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

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

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

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

1 row in set (0.00 sec)

****测试01:测试插入范围0~255的正整数和超过255的整数

mysql> INSERT INTO test2 values(0); #插入数值0,正常(没有超过范围)

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO test2 values(255); #插入数值255,正常(没有超过范围)

Query OK, 1 row affected (0.08 sec)

mysql> INSERT INTO test2 values(256); #插入数值256,错误(超过范围)

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

mysql> select * from test2;

+------+

| id |

+------+

| 00
编程开发网

首页 上一页 1 2 下一页 尾页 1/2/2
】【打印繁体】【投稿】【收藏】 【推荐】【举报】【评论】 【关闭】 【返回顶部
上一篇mysql修改binlog日志保留时间 下一篇MySQL数据库备份实例详解

评论

帐  号: 密码: (新用户注册)
验 证 码:
表  情:
内  容:

array(4) { ["type"]=> int(8) ["message"]=> string(24) "Undefined variable: jobs" ["file"]=> string(32) "/mnt/wp/cppentry/do/bencandy.php" ["line"]=> int(214) }