MySQL 8 的整数类型用于保存没有小数部分的精确数值,包括 TINYINT、SMALLINT、MEDIUMINT、INT 和 BIGINT。它们的主要区别是存储空间和取值范围;选择时既要避免溢出,也不应无依据地全部使用 BIGINT。
一、五种整数类型
| 类型 | 字节 | 有符号范围 | 无符号范围 |
|---|---|---|---|
| TINYINT | 1 | -128 ~ 127 | 0 ~ 255 |
| SMALLINT | 2 | -32768 ~ 32767 | 0 ~ 65535 |
| MEDIUMINT | 3 | -8388608 ~ 8388607 | 0 ~ 16777215 |
| INT / INTEGER | 4 | -2147483648 ~ 2147483647 | 0 ~ 4294967295 |
| BIGINT | 8 | -263 ~ 263-1 | 0 ~ 264-1 |
INTEGER 是 INT 的同义词。上述范围由类型和是否使用 UNSIGNED 决定,与 INT(11) 之类的括号数字无关。
二、有符号与 UNSIGNED
整数类型默认是有符号类型,可以保存负数。添加 UNSIGNED 后不能保存负数,同样的存储空间会用于扩大正数上限。
CREATE TABLE integer_demo ( signed_value INT NOT NULL, unsigned_value INT UNSIGNED NOT NULL );
例如 INT 的有符号上限为 2147483647,INT UNSIGNED 的上限为 4294967295。
UNSIGNED 适合明确不会为负数的计数、内部 ID 等字段,但不要只为了“范围更大”就使用。涉及相减、跨系统传输或数据库迁移时,还要确认应用语言和目标数据库是否能正确处理无符号整数。
三、INT(11) 不是 11 位整数
旧建表语句中经常出现:
CREATE TABLE old_example ( id INT(11) NOT NULL );
11 曾表示最小显示宽度,不会改变 INT 的 4 字节存储空间,也不会把范围限制为 11 位数字。例如 INT(3) 仍然可以保存 10000,只要数值没有超过 INT 的范围。
从 MySQL 8.0.17 开始,整数显示宽度和 ZEROFILL 已弃用。新建表直接写基础类型:
CREATE TABLE new_example ( id INT UNSIGNED NOT NULL );
如果业务需要把编号显示为固定宽度,应在应用层格式化,或查询时使用 LPAD():
SELECT LPAD(CAST(id AS CHAR), 8, '0') AS display_id FROM new_example;
四、AUTO_INCREMENT 自增列
整数列经常与 AUTO_INCREMENT 一起作为主键:
CREATE TABLE users ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, username VARCHAR(100) NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_username (username) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
插入时省略自增列:
INSERT INTO users (username)
VALUES ('zhangsan');
SELECT LAST_INSERT_ID() AS new_user_id;
LAST_INSERT_ID() 返回当前连接最近生成的自增值。应用程序应使用数据库驱动提供的同连接方法获取,不要使用 SELECT MAX(id),否则并发写入时可能拿到其他请求的 ID。
自增 ID 不保证连续
事务回滚、插入失败、批量插入和服务器重启都可能产生空洞。自增值的目标是生成唯一键,而不是生成无间断业务编号。
START TRANSACTION;
INSERT INTO users (username) VALUES ('temporary_user');
ROLLBACK;
上面分配过的自增值以后不一定被重新使用。订单号、发票号等需要特定连续性或格式的编号,应使用独立的业务编号方案。
五、溢出与严格模式
当写入值超出类型范围时,严格 SQL 模式通常会报错。先查看当前会话配置:
SELECT @@SESSION.sql_mode;
下面的值超过了有符号 TINYINT 的上限 127:
CREATE TABLE tiny_demo ( value TINYINT NOT NULL ); INSERT INTO tiny_demo (value) VALUES (128);
生产环境不应依赖非严格模式下的截断或调整行为。应用程序应校验输入范围,并正确处理数据库返回的溢出错误。
六、如何选择整数类型
TINYINT
适合范围非常小的数值,例如评分、状态或小型枚举:
status TINYINT UNSIGNED NOT NULL DEFAULT 0
但是状态值如果只有有限几个选项,还应通过应用校验或 CHECK 约束限制合法范围:
CREATE TABLE tasks ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, status TINYINT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (id), CONSTRAINT chk_task_status CHECK (status IN (0, 1, 2)) );
SMALLINT
适合端口号、较小计数或有限范围的编号。端口范围为 0~65535,可使用:
port SMALLINT UNSIGNED NOT NULL
MEDIUMINT
占 3 字节,适合明确不会超过约 1677 万的无符号数值。不过它不是所有数据库都支持的标准类型,如果未来可能迁移到其他数据库,应评估兼容性。
INT
常用于普通计数、外键和规模中等的数据表主键。无符号上限约为 42.9 亿,但要根据数据增长速度和系统寿命评估是否足够。
BIGINT
适合大型业务表主键、累计量很大的计数器和需要更大范围的整数。它占 8 字节,相应索引也比 INT 更大,会影响缓存和索引空间。
七、主键类型与外键必须一致
关联字段应使用相同的整数类型和符号属性。父表是 BIGINT UNSIGNED,子表外键也应一致:
CREATE TABLE orders (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (id),
KEY idx_user_id (user_id),
CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users (id)
) ENGINE=InnoDB;
不要让主键使用 INT UNSIGNED、外键却使用有符号 INT 或 BIGINT。类型不一致会造成建外键失败,也可能增加隐式转换和维护成本。
八、TINYINT(1) 与 BOOLEAN
MySQL 中 BOOL 和 BOOLEAN 是 TINYINT 的同义写法,并不是真正只能存 0 和 1 的独立布尔类型。
CREATE TABLE feature_flags ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, is_enabled BOOLEAN NOT NULL DEFAULT FALSE, PRIMARY KEY (id), CONSTRAINT chk_is_enabled CHECK (is_enabled IN (0, 1)) );
某些驱动会把 TINYINT(1) 映射为布尔值,但括号中的 1 仍不是取值范围。需要严格限制时,应使用 CHECK 或应用校验。
九、BIGINT 与 JavaScript 精度
JavaScript 的普通 Number 无法安全表示所有 64 位整数,安全整数上限为 9007199254740991。MySQL BIGINT 可能远超该范围。
后端 API 返回大整数 ID 时,通常应序列化为字符串:
const response = {
id: String(databaseRow.id)
};
前端不要对这类 ID 做普通浮点运算;确实需要整数计算时,应评估 BigInt 及其 JSON 序列化方式。
十、整数不适合存储哪些数据
- 金额:通常使用
DECIMAL,或在明确规则下以最小货币单位保存整数。 - 手机号:应使用字符串,可能包含国家码、前导零和加号。
- 身份证号、银行卡号:属于标识符,不参与算术,应使用字符串。
- IP 地址:IPv4 可用专门转换函数和二进制类型,IPv6 更不能用普通
INT。 - 带小数的测量值:根据精度要求选择
DECIMAL或浮点类型。
十一、查看现有字段定义
SHOW CREATE TABLE users;
批量检查数据库中的整数列:
SELECT TABLE_NAME,
COLUMN_NAME,
COLUMN_TYPE,
IS_NULLABLE,
COLUMN_DEFAULT,
EXTRA
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
AND DATA_TYPE IN (
'tinyint', 'smallint', 'mediumint', 'int', 'bigint'
)
ORDER BY TABLE_NAME, ORDINAL_POSITION;
COLUMN_TYPE 可以显示 unsigned 等属性,适合排查主键与外键定义不一致的问题。
十二、修改整数类型的注意事项
修改前先检查已有数据是否落在新范围内:
SELECT MIN(id) AS min_id,
MAX(id) AS max_id,
COUNT(*) AS row_count
FROM users;
例如将 INT 改成 BIGINT UNSIGNED:
ALTER TABLE users MODIFY id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;
还必须同步修改所有关联表的外键列。大表上的 ALTER TABLE 可能重建表、占用额外磁盘并产生锁或复制延迟,应先在测试环境评估并安排维护窗口。
十三、选型建议
- 先估算字段在系统生命周期内的最大值,再留出合理增长空间。
- 明确不会出现负数时再使用
UNSIGNED。 - 主键与所有外键保持类型和符号属性一致。
- 不要使用整数显示宽度和
ZEROFILL设计新表。 - 自增键只保证生成唯一值,不保证连续。
- 大整数通过 JSON 传给 JavaScript 时优先使用字符串。
- 不要为了节省几个字节而选择很快会溢出的类型。
- 也不要无依据把所有整数都定义成
BIGINT。
十四、完整示例
CREATE TABLE products ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, category_id INT UNSIGNED NOT NULL, stock MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, status TINYINT UNSIGNED NOT NULL DEFAULT 1, sales_count BIGINT UNSIGNED NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_category_id (category_id), CONSTRAINT chk_product_status CHECK (status IN (0, 1, 2)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
该示例中:
- 主键使用
BIGINT UNSIGNED,为数据增长保留空间。 - 分类 ID 使用
INT UNSIGNED,应与分类表主键一致。 - 库存使用
MEDIUMINT UNSIGNED,前提是业务上限确实适合。 - 状态使用
TINYINT UNSIGNED并用CHECK限制合法值。 - 累计销量使用
BIGINT UNSIGNED,避免长期累计溢出。
十五、官方文档
十六、总结
MySQL 整数类型的核心差异是存储空间和取值范围。UNSIGNED 会把范围移动到非负区间;INT(11) 不代表 11 位整数;自增主键不保证连续。实际设计时,应根据最大值、增长速度、关联字段、应用语言和索引空间选择合适类型。