Mysql非主键自增字段

墨蓝 2023-02-23 08:07 36阅读 0赞

### Mysql非主键自增字段 ###

*  需求
 *  错误的方式
 *  正确的方式
 *  原因分析

# 需求 #

菜单表涉及到人为干预排序，设计表结构时，需要单独的字段（seq）用于排序。此字段仅用于排序，非主键字段。

# 错误的方式 #

SET NAMES utf8mb4;
    SET FOREIGN_KEY_CHECKS = 0;
    
    -- ----------------------------
    -- Table structure for t_menu
    -- ----------------------------
    DROP TABLE IF EXISTS `t_menu`;
    CREATE TABLE `t_menu` (
      `id` bigint NOT NULL COMMENT '主键',
      `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '菜单名称',
      `seq` int auto_increment COMMENT '序号（用于排序）',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='菜单表';
    
    SET FOREIGN_KEY_CHECKS = 1;

![在这里插入图片描述][2020070610111318.png]

> 出现错误 there can be only one auto column and **it must be defined as a key**, Time: 0.000000s

# 正确的方式 #

SET NAMES utf8mb4;
    SET FOREIGN_KEY_CHECKS = 0;
    -- ----------------------------
    -- Table structure for t_menu
    -- ----------------------------
    DROP TABLE IF EXISTS `t_menu`;
    CREATE TABLE `t_menu` (
      `id` bigint NOT NULL COMMENT '主键',
      `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '菜单名称',
      `seq` int auto_increment COMMENT '序号（用于排序）',
      key(seq),
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='菜单表';
    
    SET FOREIGN_KEY_CHECKS = 1;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlc2h1bmNoZW5n_size_16_color_FFFFFF_t_70]

**测试插入数据**

insert INTO  t_menu(`id`,`name`) VALUES ('2',"2");
    insert INTO  t_menu(`id`,`name`) VALUES ('21',"23");

**结果**  
![在这里插入图片描述][20200706102001710.png]

# 原因分析 #

there can be only one auto column and **it must be defined as a key**, Time: 0.000000s

这里的key 不是primary key

> 参考  
> [mysql中key 、primary key 、unique key 与index区别][mysql_key _primary key _unique key _index]

[2020070610111318.png]: https://img-blog.csdnimg.cn/2020070610111318.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlc2h1bmNoZW5n_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200706101626464.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hlc2h1bmNoZW5n,size_16,color_FFFFFF,t_70
[20200706102001710.png]: https://img-blog.csdnimg.cn/20200706102001710.png
[mysql_key _primary key _unique key _index]: https://www.iteye.com/blog/zccst-1697043