bigint说明
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17类 型：bigint 长 度：20 占用字节：8字节 有 符 号：-9223372036854775808至9223372036854775807 无 符 号：0至18446744073709551615 不指定长度时的长度为：21(实际长度还是20) id int(M) [UNSIGNED] [ZEROFILL] 字段名 字段类型(长度) [无符号] [前导填充] unsigned： 01：bigint(M)后面加上unsigned后,就是无符号（bigint的范围就是0至18446744073709551615） 02：bigint(M)后面不加unsigned和zerofill参数,就是有符号（bigint的范围就是-9223372036854775808至9223372036854775807） zerofill： 01：进行前导零填充 02：bigint(M)加上zerofile后，同时也会把unsigned参数也带上(bigint的范围就是0至18446744073709551615)
实践环境准备
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24-- 创建chenliang库,字符集为utf8 mysql> create database if not exists chenliang character set utf8 collate utf8_general_ci; Query OK, 1 row affected (0.01 sec) mysql> show databases like "chenliang"; +----------------------+ | Database (chenliang) | +----------------------+ | chenliang | +----------------------+ 1 row in set (0.00 sec) -- 进入chenliang库，并查看是否成功进入到了chenliang库 mysql> use chenliang; Database changed mysql> select database(); +------------+ | database() | +------------+ | chenliang | +------------+ 1 row in set (0.00 sec)
测试1【加unsigned参数】
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52-- 创建test1表（这里加unsigned参数,也就是无符号） mysql> create table if not exists test1( -> id bigint(20) unsigned -> )engine=innodb character set utf8 collate utf8_general_ci; Query OK, 0 rows affected (0.01 sec) mysql> desc test1; +-------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------+------+-----+---------+-------+ | id | bigint(20) unsigned | YES | | NULL | | +-------+---------------------+------+-----+---------+-------+ 1 row in set (0.00 sec) -- 测试01：测试插入范围0~18446744073709551615的整数以及超过18446744073709551615的整数 mysql> insert into test1 values(0); Query OK, 1 row affected (0.00 sec) mysql> insert into test1 values(18446744073709551615); Query OK, 1 row affected (0.00 sec) mysql> insert into test1 values(18446744073709551616); ERROR 1264 (22003): Out of range value for column 'id' at row 1 mysql> select * from test1; +----------------------+ | id | +----------------------+ | 0 | | 18446744073709551615 | +----------------------+ 2 rows in set (0.00 sec) -- 测试02：测试插入范围-9233372036854775808至-9223372036854775807的整数以及小于-9223372036854775807的整数 mysql> insert into test1 values(-1); ERROR 1264 (22003): Out of range value for column 'id' at row 1 mysql> insert into test1 values(-9223372036854775807); ERROR 1264 (22003): Out of range value for column 'id' at row 1 mysql> insert into test1 values(-9223372036854775808); ERROR 1264 (22003): Out of range value for column 'id' at row 1 mysql> select * from test1; +----------------------+ | id | +----------------------+ | 0 | | 18446744073709551615 | +----------------------+ 2 rows in set (0.00 sec)
测试2【加zerofill参数】
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52-- 创建表test2（这里指定了zerofill参数,也就是前导0填充） mysql> create table if not exists test2( -> id bigint(20) zerofill -> )engine=innodb character set utf8 collate utf8_general_ci; Query OK, 0 rows affected (0.01 sec) mysql> desc test2; +-------+------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------------------+------+-----+---------+-------+ | id | bigint(20) unsigned zerofill | YES | | NULL | | +-------+------------------------------+------+-----+---------+-------+ 1 row in set (0.01 sec) -- 测试01：测试插入范围0~18446744073709551615的整数以及超过18446744073709551615的整数 mysql> insert into test2 values(0); Query OK, 1 row affected (0.00 sec) mysql> insert into test2 values(18446744073709551615); Query OK, 1 row affected (0.00 sec) mysql> insert into test2 values(18446744073709551616); ERROR 1264 (22003): Out of range value for column 'id' at row 1 mysql> select * from test2; +----------------------+ | id | +----------------------+ | 00000000000000000000 | | 18446744073709551615 | +----------------------+ 2 rows in set (0.00 sec) -- 测试02：测试插入范围-9233372036854775808至-9223372036854775807的整数以及小于-9223372036854775807的整数 mysql> insert into test2 values(-1); ERROR 1264 (22003): Out of range value for column 'id' at row 1 mysql> insert into test2 values(-9223372036854775807); ERROR 1264 (22003): Out of range value for column 'id' at row 1 mysql> insert into test2 values(-9223372036854775808); ERROR 1264 (22003): Out of range value for column 'id' at row 1 mysql> select * from test2; +----------------------+ | id | +----------------------+ | 00000000000000000000 | | 18446744073709551615 | +----------------------+ 2 rows in set (0.00 sec)
测试3【不加unsigned和zerofill参数】
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54-- 创建表test3（不指定unsigned和zerofill参数，也就是有符号） mysql> create table if not exists test3( -> id bigint(20) -> )engine=innodb character set utf8 collate utf8_general_ci; Query OK, 0 rows affected (0.00 sec) mysql> desc test3; +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | id | bigint(20) | YES | | NULL | | +-------+------------+------+-----+---------+-------+ 1 row in set (0.00 sec) -- 测试01：测试插入范围0至9223372036854775807的整数以及超过9223372036854775807的整数 mysql> insert into test3 values(0); Query OK, 1 row affected (0.00 sec) mysql> insert into test3 values(9223372036854775807); Query OK, 1 row affected (0.00 sec) mysql> insert into test3 values(9223372036854775808); ERROR 1264 (22003): Out of range value for column 'id' at row 1 mysql> select * from test3; +---------------------+ | id | +---------------------+ | 0 | | 9223372036854775807 | +---------------------+ 2 rows in set (0.00 sec) -- 测试02：测试插入范围-1至-9223372036854775808的整数以及小于-9223372036854775808的整数 mysql> insert into test3 values(-1); Query OK, 1 row affected (0.01 sec) mysql> insert into test3 values(-9223372036854775808); Query OK, 1 row affected (0.01 sec) mysql> insert into test3 values(-9223372036854775809); ERROR 1264 (22003): Out of range value for column 'id' at row 1 mysql> select * from test3; +----------------------+ | id | +----------------------+ | 0 | | 9223372036854775807 | | -1 | | -9223372036854775808 | +----------------------+ 4 rows in set (0.00 sec)
转载于:https://www.cnblogs.com/chenliangc/articles/11610093.html
最后
以上就是秀丽荔枝最近收集整理的关于mysql数据类型--[整数类型]--bigint类型的全部内容，更多相关mysql数据类型--[整数类型]--bigint类型内容请搜索靠谱客的其他文章。
本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
![mysql数据类型--[整数类型]--bigint类型](/uploads/reation/bcimg1.png)
发表评论 取消回复