技术 · 思考 · 实践 · 成长

首页/MySQL8专栏/正文

MySQL 8 整数类型详解:范围、UNSIGNED、AUTO_INCREMENT 与选型

MySQL8专栏253 次阅读

MySQL 8 的整数类型用于保存没有小数部分的精确数值,包括 TINYINT、SMALLINT、MEDIUMINT、INT 和 BIGINT。它们的主要区别是存储空间和取值范围;选择时既要避免溢出,也不应无依据地全部使用 BIGINT。

一、五种整数类型

类型字节有符号范围无符号范围
TINYINT1-128 ~ 1270 ~ 255
SMALLINT2-32768 ~ 327670 ~ 65535
MEDIUMINT3-8388608 ~ 83886070 ~ 16777215
INT / INTEGER4-2147483648 ~ 21474836470 ~ 4294967295
BIGINT8-263 ~ 263-10 ~ 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 位整数;自增主键不保证连续。实际设计时,应根据最大值、增长速度、关联字段、应用语言和索引空间选择合适类型。

生活的强者,不是指能搞定一切困难,也不是指没有恐惧,而是就算心里藏着无尽的疲惫和委屈,还是会认真地做好手头上的事情;就算自己被生活锤得心灰意冷,还是会尽心尽力地负起责任;就算发现现实与理想的差距有十万里,虽然鞭长莫及,却依然马不停蹄。

评论交流

0 条评论 · 支持多级回复
验证码
暂无评论,来说说你的想法吧

相关文章

继续阅读