表结构设计-取值范围导致的踩坑

踩坑记录-表结构设计

背景：

游戏开展一个预注册活动，只要是参与预注册的用户，登录游戏后每人有且仅可以领取一个大礼包。

需求：

已知参与预注册用户的人数有3万人，那么需要设计一张表，来存储所有预注册用户的领奖记录。

开发：

设计的表结构如下：

CREATE TABLE 表名 (
id tinyint(5) NOT NULL AUTO_INCREMENT,
playerId varchar(64) DEFAULT NULL,
awardTime date DEFAULT NULL COMMENT ‘领奖时间’,
type varchar(20) DEFAULT NULL COMMENT ‘类型，
activityCode varchar(20) DEFAULT NULL COMMENT ‘活动代码’,
rewardCode varchar(20) DEFAULT NULL COMMENT ‘礼品码’,
deleted tinyint(1) DEFAULT ‘0’,
gameCode varchar(20) DEFAULT NULL,
PRIMARY KEY (id),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

思考一下，这张表这样子设计是否有致命问题。

如果已经猜到了，那么接下来继续看下去：

熟悉这个报错吗？

    org.springframework.dao.DuplicateKeyException: PreparedStatementCallback; SQL [INSERT INTO 表名 ( activityCode,rewardCode,gameCode,type,awardTime,playerId) VALUES (?,?,?,?,?,?)]; Duplicate entry '127' for key 'PRIMARY'; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '127' for key 'PRIMARY'

报错原因

当该表中存储满127条记录后，就无法再次插入了。因为主键id的数据类型是tinyInt，最大数是127。所以这个会导致第128个用户以后的领奖记录无法被记录！

解决

你会怎么样？当你已经遇到这个问题了。先思考一下。

修改1

1.经过一番搜索后，修改主键数据类型的操作是：

Alter table 表名 change 字段 字段 int(10) not null auto_increment;

2.执行后。完美，数据类型改回来了！

又一个坑

1.修改数据类型的操作，会将原有的数据全部清除。

正确操作

1.导出数据
2.修改数据类型
3.导入数据

总结

各种int的取值范围

tinyint（127）
有符号的范围是 -128 -127，无符号的范围是 从 0 到 255 的整型数据。一位大小为 1 字节

smallint（3万2）
一个小整数。有符号的范围是-2^15 (-32,768) 到 2^15 - 1 (32,767) 的整型数据，无符号的范围是0到65535。一位大小为 2 个字节。

mediumint（8百万）
一个中等大小整数，有符号的范围是-8388608到8388607，无符号的范围是0到16777215。 一位大小为3个字节。

int（20亿）
一个正常大小整数。有符号的范围是-2^31 (-2,147,483,648) 到 2^31 - 1 (2,147,483,647) 的整型数据（所有数字），无符号的范围是0到4294967295。一位大小为 4 个字节。
int 的 SQL-92 同义词为 integer。

bigint
从 -2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807)* 的整型数据（所有数字），无符号的范围是0到18446744073709551615。一位为 8 个字节。