设为首页 加入收藏

TOP

数据类型之【数值类型】的整数型int(一)
2019-01-27 04:08:19 】 浏览:170
Tags:数据 类型 数值 整数 int

1.1 int说明 4.png

id int(M) [UNSIGNED] [ZEROFILL]

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

unsigned:

01:int(M)后面加上unsigned,就是无符号(int的范围就是0~4294967295)

02:int(M)后面不加上unsigned,并且不加ZEROFILL参数,就是有符号(int的范围就是

-2147483648~2147483647)

zerofill:

01:进行前导零填充

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

1.2 int实践

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 int(10) unsigned

-> );

Query OK, 0 rows affected (0.02 sec)

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

mysql> desc test1;

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

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

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

| id | int(10) unsigned | YES | | NULL | |

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

1 row in set (0.01 sec)

**测试01:测试插入范围0~4294967295的整数以及超过4294967295的整数

mysql> insert into test1 values(0);

Query OK, 1 row affected (0.05 sec)

^==插入整数0,正确(没有超过id字段的范围0~4294967295)

mysql> insert into test1 values(4294967295);

Query OK, 1 row affected (0.01 sec)

^==插入数值4294967295,正确(没有超过id字段的范围0~4294967295)

mysql> insert into test1 values(4294967296);

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

^==插入数值4294967296,错误(超过id字段的范围0~4294967295)

mysql> select * from test1;

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

| id |

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

| 0 |

| 4294967295 |

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

2 rows in set (0.00 sec)

**测试02:测试-1~-2147483648范围的数据是否能够正常插入

mysql> insert into test1 values(-1);

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

^==插入负数报错(因为建表时,id字段加了unsigned参数,id字段的范围为0~4294967295)

mysql> insert into test1 values(-2147483648);

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

^==插入负数报错(因为建表时,在id字段加了unsigned参数,id字段的范围为0~4294967295)

mysql> select * from test1;

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

| id |

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

| 0 |

| 4294967295 |

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

2 rows in set (0.00 sec)

1.2.3 加zerofill参数

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

mysql> create table if not exists test2(

-> id int(10) zerofill

-> );

Query OK, 0 rows affected (0.03 sec)

^==表test2的id字段加了zerofill参数,它会把unsigned参数也带上,所以id字段的范围就是0~4294967295

mysql> desc test2;

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

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

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

| id | int(10) unsigned zerofill | YES | | NULL | |

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

1 row in set (0.03 sec)

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

mysql> insert into test2 values(0);

Query OK, 1 row affected (0.04 sec)

^==插入整数0,正确(没有超过id字段的范围0~4294967295)

mysql> insert into test2 values(4294967295);

Query OK, 1 row affected (0.02 sec)

^==插入整数4294967295,正确(没有超过id字段的范围0~4294967
编程开发网

首页 上一页 1 2 下一页 尾页 1/2/2
】【打印繁体】【投稿】【收藏】 【推荐】【举报】【评论】 【关闭】 【返回顶部
上一篇Greenplum--数据迁移(成倍增加计.. 下一篇mysql主从复制延迟问题