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

int类型说明

类   型：int
长   度：10
占用字节：4字节
有 符 号：-2147483648~2147483647
无 符 号：0~4294967295
不指定长度时的长度为：11(实际长度还是9)

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)

实践环境准备

-- 创建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 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,正确（没有超过范围0~4294967295）

mysql> insert into test1 values(4294967295);
Query OK, 1 row affected (0.01 sec)
<==插入数值4294967295,正确(没有超过范围0~4294967295)

mysql> insert into test1 values(4294967296);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
<==插入数值4294967296,错误(超出范围0~4294967295)

mysql> select * from test1;
+------------+
| id         |
+------------+
|          0 |
| 4294967295 |
+------------+
2 rows in set (0.00 sec)
-- 测试02：测试-1~-2147483648范围的整数和小于-2147483658的整数是否可插入
mysql> insert into test1 values(-1);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
<==插入负数报错(因为建表时,id字段加了unsigned参数,int的范围为0~4294967295)

mysql> insert into test1 values(-2147483648);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
<==插入负数报错(因为建表时,在id字段加了unsigned参数，int的范围为0~4294967295)

mysql> insert into test1 values(-2147483269);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
<==插入负数报错(因为建表时,在id字段加了unsigned参数，int的范围为0~4294967295)

mysql> select * from test1;
+------------+
| id         |
+------------+
|          0 |
| 4294967295 |
+------------+
2 rows in set (0.00 sec)

测试2【加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，在0~4294967295范围内，正确

mysql> insert into test2 values(4294967295);
Query OK, 1 row affected (0.02 sec)
<==插入整数4294967295,在0~4294967295范围内,正确

mysql> insert into test2 values(4294967296);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
<==插入整数4294967296,不在0~4294967295范围内,错误

mysql> select * from test2;
+------------+
| id         |
+------------+
| 0000000000 |
| 4294967295 |
+------------+
2 rows in set (0.02 sec)
-- 测试02：测试-1~-2147483648围的数据是否能够正常插入
mysql> insert into test2 values(-1);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
<=插入负整数-1，错误（因为在id字段加了zerofill参数，它会把unsigned也带上，所以id字段的范围为0~4294967295）

mysql> insert into test2 values(-2147483648);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
<==插入负整数-2147483648,错误（因为在id字段加了zerofill参数，它会把unsigned也带上，所以id字段的范围为0~4294967295)

mysql> insert into test1 values(-2147483269);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
<==插入负数报错(因为建表时，在id字段加了unsigned参数，int的范围为0~4294967295)

mysql> select * from test2;
+------------+
| id         |
+------------+
| 0000000000 |
| 4294967295 |
+------------+
2 rows in set (0.03 sec)

测试3【不加unsigned和zerofill参数】

-- 创建test3表（不加unsigned和zerofill）
mysql> create table if not exists test3(
    -> id int(10)
    -> );
Query OK, 0 rows affected (0.02 sec)
<==表test3的id字段没有加什么的参数（unsigned\zerofill）,那么id字段的范围为-2147483648~2147483647

mysql> desc test3;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(10) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)1 row in set (0.01 sec)
-- 测试01：测试插入整数0~2147483647和超过2147483647的整数
mysql> insert into test3 values(0); 
Query OK, 1 row affected (0.00 sec)
<==插入整数0，正确（在范围-2147483648~2147483647范围内）

mysql> insert into test3 values(2147483647);
Query OK, 1 row affected (0.02 sec)
<==插入整数2147483647，正确（在范围-2147483648~2147483647范围内）

mysql> insert into test3 values(2147483648);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
<==插入整数2147483648，错误（不在范围-2147483648~2147483647范围内）

mysql> select * from test3;
+------------+
| id         |
+------------+
|          0 |
| 2147483647 |
+------------+
2 rows in set (0.00 sec)
-- 测试02：测试插入负数-1~-2147483648和小于-2147483648的负数
mysql> INSERT INTO test3 values(-1);
Query OK, 1 row affected (0.10 sec)
<==插入负整数-1,正确（在范围-2147483648~2147483647范围内）

mysql> insert into test3 values(-2147483648);
Query OK, 1 row affected (0.00 sec)
<==插入负整数-2147483648,正确（在范围-2147483648~2147483647范围内）

mysql> insert into test3 values(-2147483649);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
<==插入负整数-2147483649,错误（不在范围-2147483648~2147483647范围内）

mysql> select * from test3;
+-------------+
| id          |
+-------------+
|           0 |
|  2147483647 |
|          -1 |
| -2147483648 |
+-------------+
4 rows in set (0.00 sec)

总结

格式：

id         int(M)        [UNSIGNED]  [ZEROFILL]

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

unsigned：

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

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

是-2147483648~2147483647）

zerofill：

01：进行前导零填充（插入数值1,字段显示的是0000000001,因为int的长度为10）

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

转载于:https://www.cnblogs.com/chenliangc/articles/11107338.html