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

BIGINT和BIGINT(20)作为主键自增字段的主要区别在于显示宽度，而非存储大小或范围。下面是详细解析：

1. 核心区别：显示宽度

BIGINT

• 默认显示宽度为21位（符号位+20位数字）
• 实际存储范围：-9,223,372,036,854,775,808 到 9,223,372,036,854,775,807

BIGINT(20)

• 指定显示宽度为20位
• 注意：括号中的数字只是显示宽度，不是最大值限制
• 实际存储范围与普通BIGINT完全相同

2. 存储特性相同

-- 两种定义方式在存储上完全一样
CREATE TABLE t1 (
    id BIGINT AUTO_INCREMENT PRIMARY KEY
);

CREATE TABLE t2 (
    id BIGINT(20) AUTO_INCREMENT PRIMARY KEY
);

-- 两者都占用8字节存储空间
-- 两者都有相同的自增范围

3. 显示宽度的影响

在非ZEROFILL情况下

• 显示宽度基本没有影响
• MySQL客户端可能用指定宽度格式化输出，但不会截断数据

-- 创建一个测试表
CREATE TABLE test_table (
    id BIGINT(5) AUTO_INCREMENT PRIMARY KEY,
    value VARCHAR(10)
);

-- 插入超过显示宽度的值
INSERT INTO test_table (value) VALUES ('test');
INSERT INTO test_table (value) VALUES ('test');
-- id会正常自增为1, 2... 即使显示宽度设为5，也能存储很大的数

在ZEROFILL情况下

显示宽度才有实际意义：

CREATE TABLE zerofill_test (
    id BIGINT(10) ZEROFILL AUTO_INCREMENT PRIMARY KEY
);

-- 插入数据
INSERT INTO zerofill_test VALUES (1), (123), (123456);

-- 查询结果会自动补零到10位：
-- 0000000001
-- 0000000123
-- 0000123456

4. 实际建议

推荐使用 BIGINT（不带宽度）

CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    -- 其他字段...
);

原因：

5. 自增主键的最佳实践

-- 最佳实践示例
CREATE TABLE orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -- 使用UNSIGNED将范围翻倍：0 ~ 18,446,744,073,709,551,615
    -- 这对于自增主键非常合适
    order_no VARCHAR(32) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) AUTO_INCREMENT = 100000;  -- 可以设置起始值

-- 或者使用更通用的方式
CREATE TABLE products (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    product_code VARCHAR(50) NOT NULL UNIQUE
);

6. 重要注意事项

自增溢出：

-- 当达到最大值时，继续插入会报错
-- 2147483647 (INT最大值) 或 9223372036854775807 (BIGINT最大值)

复制环境：

• 在MySQL复制中，自增值的步长可能需要特殊配置
• 使用auto_increment_increment和auto_increment_offset控制

分库分表考虑：

-- 在分布式系统中，可能需要使用特定算法生成ID
-- 而不是依赖数据库自增

总结

结论：对于自增主键，建议使用BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY，显示宽度参数可以省略，因为它不影响实际存储和功能。