mysql数据类型--[整数类型]--bigint类型

bigint说明

类   型：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)

实践环境准备

-- 创建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参数】

-- 创建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参数】

-- 创建表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参数】

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