创建一个表
CREATE TABLE `test` (
`id` tinyint(4) NOT NULL AUTO_INCREMENT COMMENT '取值范围[-128,127]',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
自增插入一条记录
insert into test (id) values(null);
查询结果:
select * from test;
+---------------+
| id |
+---------------+
| 1 |
+---------------+
插入越界数据
- 插入大于max的数据
insert into test (id) values(128);
mysql warning:1 row(s) affected, 1 warning(s): 1264 Out of range value for column ‘id’ at row 1
查询结果:
select * from test;
+---------------+
| id |
+---------------+
| 1 |
| 127 |
+---------------+
- 插入小于min的数据
insert into test (id) values(-129);
mysql warning:1 row(s) affected, 1 warning(s): 1264 Out of range value for column ‘id’ at row 1
查询结果:
select * from test;
+---------------+
| id |
+---------------+
| 1 |
| 127 |
| -128 |
+---------------+
结论:id插入值越界后,数据库报警,数据插入值为距离最近的边界值。
id自增到最大后,再插入一条数据
- 往最大值方向插入
insert into test (id) values(null);
mysql error:Error Code: 1062. Duplicate entry ‘127’ for key ‘PRIMARY’
- 往最小值方向插入
insert into test (id) values(-129);
Error Code: 1062. Duplicate entry ‘-128’ for key ‘PRIMARY’
结论:id边界值使用后,越过此边界值插入数据会失败