mysql的bigint 和 bigint(20) 主键自增的区别解析

这是一个常见的MySQL概念混淆点，我来为你详细解析BIGINT和BIGINT(20)作为主键自增的区别。

核心结论

实际上两者在存储和功能上没有任何区别，(20)只是一个显示宽度提示，不影响实际存储大小和自增功能。

详细解析

1. 存储方面

BIGINT和BIGINT(20)都占用 8字节（64位） 存储空间
存储范围完全相同：-2^63 到 2^63-1（-9,223,372,036,854,775,808 到 9,223,372,036,854,775,807）
可存储约 922亿亿 条记录，对于绝大多数应用都足够

2. `(20)`的含义（显示宽度）

(20)是显示宽度，不是长度限制。它只在以下情况下起作用：

当使用ZEROFILL属性时，会显示前导零补齐到指定宽度
某些客户端工具可能根据这个值调整显示格式

-- 示例：有ZEROFILL时的区别
CREATE TABLE test1 (
    id BIGINT ZEROFILL AUTO_INCREMENT PRIMARY KEY
);

CREATE TABLE test2 (
    id BIGINT(20) ZEROFILL AUTO_INCREMENT PRIMARY KEY
);

-- 插入数据后：
-- test1.id 显示为: 00000000000000000001
-- test2.id 显示为: 00000000000000000001 (因为默认宽度就是20)

3. 实际使用中的最佳实践

-- 推荐的写法（简洁明了）
CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
);

-- 如果需要显示宽度提示（但通常不需要）
CREATE TABLE orders (
    id BIGINT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_no VARCHAR(50)
);

4. 重要注意事项

UNSIGNED的重要性

-- 有符号：-922亿亿 ~ +922亿亿
BIGINT AUTO_INCREMENT  -- 从1开始，最大到922亿亿

-- 无符号：0 ~ 1844亿亿
BIGINT UNSIGNED AUTO_INCREMENT  -- 从1开始，最大到1844亿亿

-- 建议使用 UNSIGNED，可以获得更大的自增范围

自增主键溢出问题

-- 监控自增ID使用情况
SELECT 
    table_name,
    auto_increment,
    pow(2, 63)-1 as max_signed,
    pow(2, 64)-1 as max_unsigned
FROM information_schema.tables 
WHERE table_schema = 'your_database';

5. 性能影响

无性能差异：显示宽度不影响索引性能
无存储差异：两者都占用8字节
无功能差异：自增机制完全相同

总结建议

使用BIGINT UNSIGNED AUTO_INCREMENT 作为标准写法 省略显示宽度（如BIGINT(20)中的20），除非有特殊显示需求优先使用UNSIGNED以获得更大范围监控自增ID增长，避免溢出（虽然概率极低）

-- 最终推荐的标准写法
CREATE TABLE example (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -- 其他字段...
);

记住：BIGINT(20)中的20就像是一个"格式建议"，而不是"长度限制"，数据库不会因为这个数字而分配更多或更少的存储空间。