mysql数据库auto_MySQL数据库之auto_increment【转】

最新推荐文章于 2023-04-20 10:29:45 发布
最新推荐文章于 2023-04-20 10:29:45 发布
阅读量710 收藏 1
点赞数
文章标签： mysql数据库auto
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_33992214/article/details/113218040
版权

一、概述

在数据库应用中，我们经常需要用到自动递增的唯一编号来标识记录。在MySQL中，可通过数据列的auto_increment属性来自动生成。可在建表时可用“auto_increment=n”选项来指定一个自增的初始值。可用“alter table table_name auto_increment=n”命令来重设自增的起始值，当然在设置的时候Mysql会取数据表中auto_increment列的最大值 + 1与n中的较大者作为新的auto_increment值。

Myql的auto_increment属性具有以下特性：

具有auto_increment属性的数据列应该是一个正数序列，如果把该数据列声明为UNSIGNED，这样序列的编号个数可增加一倍。比如tinyint数据列的最大编号是127，如果加上UNSIGNED，那么最大编号变为255

auto_increment数据列必须有唯一索引，以避免序号重复；必须具备NOT NULL属性

实际应用中发现，在delete掉某张innoDB表的全部数据并重启Mysql会导致该表的auto_increment列变为1。特测试多种情况下auto_increment列的变化并记录如下。

二、实验

1、innoDB与MyISAM对比

(1)首先，创建一张引擎为innoDB的表测试一下delete掉所有数据然后重启Mysql之后，auto_increment的情况：

48304ba5e6f9fe08f3fa1abda7d326ab.png

mysql> CREATE TABLE`table1` (

-> `id` bigint(20) NOT NULLauto_increment,

-> `create_time` datetime DEFAULT NULL,

-> PRIMARY KEY(`id`)

-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK, 0rows affected

mysql> insert into table1(create_time) values(now());

Query OK, 1row affected

mysql> insert into table1(create_time) values(now());

Query OK, 1row affected

mysql> insert into table1(create_time) values(now());

Query OK, 1row affected

mysql> insert into table1(create_time) values(now());

Query OK, 1row affected

mysql> insert into table1(create_time) values(now());

Query OK, 1row affected

mysql> select * fromtable1;

+----+---------------------+

| id | create_time |

+----+---------------------+

| 1 | 2017-02-28 16:25:11 |

| 2 | 2017-02-28 16:25:21 |

| 3 | 2017-02-28 16:25:23 |

| 4 | 2017-02-28 16:25:23 |

| 5 | 2017-02-28 16:25:24 |

| 6 | 2017-02-28 16:25:26 |

+----+---------------------+

6 rows in setmysql> delete fromtable1;

Query OK, 6rows affected

mysql> select * fromtable1;

Empty setmysql> select auto_increment from information_schema.tables where table_schema = database() and table_name='table1';

+----------------+

| auto_increment |

+----------------+

| 7 |

+----------------+

1 row in set

48304ba5e6f9fe08f3fa1abda7d326ab.png

可见，执行delete操作清空表之后，表table1的auto_increment值仍然是正常的。重启数据库之后：

48304ba5e6f9fe08f3fa1abda7d326ab.png

mysql> select auto_increment from information_schema.tables where table_schema = database() and table_name='table1';

+----------------+

| auto_increment |

+----------------+

| 1 |

+----------------+

1 row in set

48304ba5e6f9fe08f3fa1abda7d326ab.png

可见，table1表的auto_increment值变成了1。

结论：innoDB引擎的表，在执行delete清空操作之后，表的auto_increment值不会受到影响；一旦重启Mysql数据库，那么auto_increment值将变成1！

(2)下面我们创建一个引擎为MyISAM的表，测试delete掉所有数据，并重启数据库之后auto_increment的值如何变化：

48304ba5e6f9fe08f3fa1abda7d326ab.png

mysql> CREATE TABLE`table2` (

-> `id` bigint(20) NOT NULLauto_increment,

-> `create_time` datetime DEFAULT NULL,

-> PRIMARY KEY(`id`)

-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Query OK, 0rows affected

mysql> insert into table2(create_time) values(now());

Query OK, 1row affected

mysql>mysql>mysql> insert into table2(create_time) values(now());

Query OK, 1row affected

mysql> insert into table2(create_time) values(now());

Query OK, 1row affected

mysql> insert into table2(create_time) values(now());

Query OK, 1row affected

mysql> insert into table2(create_time) values(now());

Query OK, 1row affected

mysql> insert into table2(create_time) values(now());

Query OK, 1row affected

mysql> select * fromtable2;

+----+---------------------+

| id | create_time |

+----+---------------------+

| 1 | 2017-02-28 17:05:22 |

| 2 | 2017-02-28 17:05:25 |

| 3 | 2017-02-28 17:05:26 |

| 4 | 2017-02-28 17:05:27 |

| 5 | 2017-02-28 17:05:28 |

| 6 | 2017-02-28 17:05:29 |

+----+---------------------+

6 rows in setmysql> delete fromtable2;

Query OK, 6rows affected

mysql> select * fromtable2;

Empty setmysql> select auto_increment from information_schema.tables where table_schema = database() and table_name='table2';

+----------------+

| auto_increment |

+----------------+

| 7 |

+----------------+

1 row in set

48304ba5e6f9fe08f3fa1abda7d326ab.png

delete清空操作并不会对table2的auto_increment产生任何影响。重启数据库之后：

48304ba5e6f9fe08f3fa1abda7d326ab.png

mysql> select auto_increment from information_schema.tables where table_schema = database() and table_name='table2';

+----------------+

| auto_increment |

+----------------+

| 7 |

+----------------+

1 row in set

48304ba5e6f9fe08f3fa1abda7d326ab.png

可见，表table2的auto_increment仍然为7。

结论：MyISAM引擎的表，在执行delete操作之后，表的auto_increment值不会受到影响；重启Mysql数据库，auto_increment值也不会受到影响！

2、创建表时指定auto_increment

本节我们测试创建innoDB引擎的表时指定auto_increment会不会对auto_increment产生影响：

48304ba5e6f9fe08f3fa1abda7d326ab.png

mysql> CREATE TABLE`table3` (

-> `id` bigint(20) NOT NULLauto_increment,

-> `create_time` datetime DEFAULT NULL,

-> PRIMARY KEY(`id`)

-> ) ENGINE=InnoDB auto_increment=1000 DEFAULT CHARSET=utf8;

Query OK, 0rows affected

mysql> select auto_increment from information_schema.tables where table_schema = database() and table_name='table3';

+----------------+

| auto_increment |

+----------------+

| 1000 |

+----------------+

1 row in setmysql> insert into table3(create_time) values(now());

Query OK, 1row affected

mysql> insert into table3(create_time) values(now());

Query OK, 1row affected

mysql> insert into table3(create_time) values(now());

Query OK, 1row affected

mysql> insert into table3(create_time) values(now());

Query OK, 1row affected

mysql> insert into table3(create_time) values(now());

Query OK, 1row affected

mysql> select * fromtable3;

+------+---------------------+

| id | create_time |

+------+---------------------+

| 1000 | 2017-02-28 17:15:13 |

| 1001 | 2017-02-28 17:15:14 |

| 1002 | 2017-02-28 17:15:15 |

| 1003 | 2017-02-28 17:15:15 |

| 1004 | 2017-02-28 17:15:16 |

+------+---------------------+

5 rows in setmysql> delete fromtable3;

Query OK, 5rows affected

mysql> select * fromtable3;

Empty setmysql> select auto_increment from information_schema.tables where table_schema = database() and table_name='table3';

+----------------+

| auto_increment |

+----------------+

| 1005 |

+----------------+

1 row in set

48304ba5e6f9fe08f3fa1abda7d326ab.png

可见，delete操作并不会影响到表table3的auto_increment值。重启数据库之后：

48304ba5e6f9fe08f3fa1abda7d326ab.png

mysql> select auto_increment from information_schema.tables where table_schema = database() and table_name='table3';

+----------------+

| auto_increment |

+----------------+

| 1 |

+----------------+

1 row in set

48304ba5e6f9fe08f3fa1abda7d326ab.png

表table3的auto_increment变成了1。

结论：在创建innoDB表时，无论指定或不指定auto_increment，delete清空+重启数据库都会使表的auto_increment值变成1。

3、delete的时候添加where 1

本节讨论在执行delete操作时，加where 1：

48304ba5e6f9fe08f3fa1abda7d326ab.png

mysql> CREATE TABLE`table4` (

-> `id` bigint(20) NOT NULLauto_increment,

-> `create_time` datetime DEFAULT NULL,

-> PRIMARY KEY(`id`)

-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK, 0rows affected

mysql> insert into table4(create_time) values(now());

Query OK, 1row affected

mysql>mysql> insert into table4(create_time) values(now());

Query OK, 1row affected

mysql> insert into table4(create_time) values(now());

Query OK, 1row affected

mysql> insert into table4(create_time) values(now());

Query OK, 1row affected

mysql> insert into table4(create_time) values(now());

Query OK, 1row affected

mysql> insert into table4(create_time) values(now());

Query OK, 1row affected

mysql> insert into table4(create_time) values(now());

Query OK, 1row affected

mysql> select * fromtable4;

+----+---------------------+

| id | create_time |

+----+---------------------+

| 1 | 2017-02-28 17:21:33 |

| 2 | 2017-02-28 17:21:34 |

| 3 | 2017-02-28 17:21:35 |

| 4 | 2017-02-28 17:21:36 |

| 5 | 2017-02-28 17:21:36 |

| 6 | 2017-02-28 17:21:37 |

| 7 | 2017-02-28 17:21:38 |

+----+---------------------+

7 rows in setmysql> delete from table4 where 1;

Query OK, 7rows affected

mysql> select auto_increment from information_schema.tables where table_schema = database() and table_name='table4';

+----------------+

| auto_increment |

+----------------+

| 8 |

+----------------+

1 row in set

48304ba5e6f9fe08f3fa1abda7d326ab.png

重启数据库之后：

48304ba5e6f9fe08f3fa1abda7d326ab.png

mysql> select auto_increment from information_schema.tables where table_schema = database() and table_name='table4';

+----------------+

| auto_increment |

+----------------+

| 1 |

+----------------+

1 row in set

48304ba5e6f9fe08f3fa1abda7d326ab.png

可见，网上的所流传的delete清空操作时添加where 1并没用。

结论：delete innoDB表时，添加或不添加where 1，在数据库重启之后auto_increment都会被重置为1。

4、如果表中有数据，但是数据id小于auto_increment会怎么样？

本节测试当innoDB表中有数据，但是auto_increment列最大的那个值小于表的auto_increment值会怎样。我们先插入一些数据到表中，然后删除末尾的几条数据：

48304ba5e6f9fe08f3fa1abda7d326ab.png

mysql> CREATE TABLE`table5` (

-> `id` bigint(20) NOT NULLauto_increment,

-> `create_time` datetime DEFAULT NULL,

-> PRIMARY KEY(`id`)

-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK, 0rows affected

mysql> insert into table5(create_time) values(now());

Query OK, 1row affected

mysql> insert into table5(create_time) values(now());

Query OK, 1row affected

mysql> insert into table5(create_time) values(now());

Query OK, 1row affected

mysql> insert into table5(create_time) values(now());

Query OK, 1row affected

mysql> insert into table5(create_time) values(now());

Query OK, 1row affected

mysql> insert into table5(create_time) values(now());

Query OK, 1row affected

mysql> insert into table5(create_time) values(now());

Query OK, 1row affected

mysql> insert into table5(create_time) values(now());

Query OK, 1row affected

mysql> select * fromtable5;

+----+---------------------+

| id | create_time |

+----+---------------------+

| 1 | 2017-02-28 17:29:29 |

| 2 | 2017-02-28 17:29:30 |

| 3 | 2017-02-28 17:29:30 |

| 4 | 2017-02-28 17:29:30 |

| 5 | 2017-02-28 17:29:31 |

| 6 | 2017-02-28 17:29:31 |

| 7 | 2017-02-28 17:29:32 |

| 8 | 2017-02-28 17:29:32 |

+----+---------------------+

8 rows in set

mysql> delete from table5 where id > 4;

Query OK, 4 rows affected

mysql> select * from table5;

+----+---------------------+

| id | create_time |

+----+---------------------+

| 1 | 2017-02-28 17:29:29 |

| 2 | 2017-02-28 17:29:30 |

| 3 | 2017-02-28 17:29:30 |

| 4 | 2017-02-28 17:29:30 |

+----+---------------------+

4 rows in set

mysql> select auto_increment from information_schema.tables where table_schema = database() and table_name='table5';

+----------------+

| auto_increment |

+----------------+

| 9 |

+----------------+

1 row in set

48304ba5e6f9fe08f3fa1abda7d326ab.png

重启数据库之后：

48304ba5e6f9fe08f3fa1abda7d326ab.png

mysql> select auto_increment from information_schema.tables where table_schema = database() and table_name='table5';

+----------------+

| auto_increment |

+----------------+

| 5 |

+----------------+

1 row in set

48304ba5e6f9fe08f3fa1abda7d326ab.png

哇塞，奇迹发生了，table5的auto_increment居然变成了5。由此我们可以得出以下结论。

结论：Mysql数据库在重启之后，innoDB表的auto_increment值将会被设置为表中auto_increment列的最大值 + 1。

三、深究

为什么会出现上述情况呢？

这是因为，Mysql数据库的的auto_increment值是保存在内存中的，innoDB引擎的表的auto_increment在数据库服务停止时并不会做持久化操作，Mysql会在下次数据库重启的时候，相当于通过执行语句：

select max(id) maxId from table;

alter table auto_increment = maxId + 1;

来设置表table的auto_increment值。

严格意义上来说这是Mysql的一个bug。这个bug将会在8.0版本中得到修复。关于8.0版本的内容的情况，详见：

因为目前8.0版本稳定版尚未发布，所以目前为了避免被这个bug坑到，只能将引擎换为MyISAM或者从程序上去控制。

江左沉酣
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql建表auto_increment_mysql create table auto_increment
weixin_32329555的博客
02-02 4108
当插入记录时，没有为AUTO_INCREMENT明确指定值，则等同插入NULL值把0插入AUTO_INCREMENT数据列的效果与插入NULL值一样。但不建议这样做，还是以插入NULL值为好。如果把一个NULL插入到一个AUTO_INCREMENT数据列里去，MySQL将自动生成下一个序 列编号。默认编号从1开始(如果未指定auto_increment开始值，如果指定开始值，则从指定值开始， 比如...
mysql auto_increment 重置_如何重置MySQL中的AUTO_INCREMENT？
weixin_39543478的博客
02-26 2095
你可以用下面的方法重置计数器ALTER TABLE tablename AUTO_INCREMENT = 1对于InnoDB，您不能将auto_increment值设置为低于或等于最高当前索引。 (引自ViralPatel )：请注意，您不能将计数器重置为小于或等于任何已使用的值。 对于MyISAM，如果该值小于或等于当前AUTO_INCREMENT列中的最大值，则将该值重置为当前最大值加1。 ...
mysql数据库 auto_increment_MYSQL数据库中的auto_increment
weixin_39603622的博客
02-02 1585
您的位置：首页 - MYSQL - 正文MYSQL数据库中的auto_increment在MySQL中，经常使用 AUTO_INCREMENT对主键建立自增id的行为，MySQL会自行保证主键ID的自增与不重复，使程序不需对ID进行关注。但是在使用时，MySQL对auto_increment对管理的一些特性可能会导致一些问题，所以需要手工对其进行修改。修改AUTO_INCREMENT，MySQL只...
mysql数据库 auto_increment_mysql学习笔记（二：中的auto_increment 理解
weixin_29306011的博客
01-18 1万+
1、auto_increment理解1auto_increment是用于主键自动增长的，从1开始增长，当你把第一条记录删除时，再插入第二跳数据时，主键值是2，不是1。例如：create table `test`(`id` int(10) not null auto_increment, -- 表示自增列`name` varchar(20) not null,primary key(`id`))au...
MySQL数据库——MySQL AUTO_INCREMENT：主键自增长
热门推荐
Itmastergo的博客
04-20 4万+
在 MySQL 中，当主键定义为自增长后，这个主键的值就不再需要用户输入数据了，而由数据库系统根据定义自动赋值。每增加一条记录，主键会自动以相同的步长进行增长。
Mysql设置auto_increment_increment和auto_increment_offset
xinyuan的专栏
06-27 2万+
查看与设置: show variables like '%auto_inc%'; show session variables like '%auto_inc%'; -- //session会话变量 show global variables like '%auto_inc%'; -- //全局变量 SET @auto_increment_in...
mysql的auto_increment
大菜菜的博客
12-11 1万+
auto_increment作用：用来指定一个自增的初始值; 如果不指定值默认初始值为1 建表语句 插入数据 插入第一个数据可以看到id是从2开始 自增主键不能保证连续递增 在空表执行insert into aa values(null,1,1); 插入一条数据后,再执行show create table命令 表定义中AUTO_INCREMENT=3,表示下一次插入数据时，如果要自动生成自增值，会生成id=3。 自增值保存在表结构定义里。实际上，表结构定义存放在后缀名为.frm的文件中，但
MySQL数据库自增长AUTO_INCREMENT探讨
初八
07-06 3011
文章中所有操作均是在 MySQL 5.7 版本下进行的 MySQL 的自增列（AUTO_INCREMENT）和其它数据库的自增列对比，有很多特性和不同点（甚至不同存储引擎、不同版本也有一些不同的特性），让人感觉有点稍微复杂。下面我们从一些测试开始，了解一下这方面的特殊知识点。 自增列持久化问题 自增长在不同的存储引擎下，呈现的问题是不一样的，下面我们举例说明（MySQL 5.7.30 版本中实验）。 InnoDB存储引擎的问题 /* 把之前的测试表删掉，重启服务器，为了更好的展示效果 */ -- 创建..
MySQL中的auto_increment
耿进
08-30 6760
文章目录一、AUTO_INCREMENT1.1 测试二、auto_increment_increment和auto_increment_offset2.1 解释2.2 测试auto_increment_incrementauto_increment_offset当两者同时更改三.组复制中的group_replication_auto_increment_increment1.解释2.操作2.1 配置2.2 插数据 一、AUTO_INCREMENT AUTO_INCREMENT 属性可用于为新行生成唯一标识
Mysql数据库 AUTO_INCREMENT 自增序列号 跳号处理
chuangxin的专栏
12-20 5212
Mysql数据库产生序列号通常使用big int类型，并设置AUTO_INCREMENT为true，这样每次insert记录的时候，序列号会自动递增。该方式当insert执行完事务发生回滚时会产生跳号，如果项目有要求，就需要处理。本文讨论自增序列号发生跳号，如何处理。
MySQL查询和修改auto_increment的方法
12-15
本文实例讲述了MySQL查询和修改auto_increment的方法。分享给大家供大家参考。具体如下： 查询表名为tableName的auto_increment值： 代码如下:SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_...
mysql中自增auto_increment功能的相关设置及问题
09-10
请注意，如果`auto_increment_offset`的值大于`auto_increment_increment`，则`auto_increment_offset`会被忽略。 3. **自增行为** 当插入新记录时，系统会基于当前的`auto_increment_offset`和`auto_increment_...
MySQL 序列 AUTO_INCREMENT详解及实例代码
09-09
在MySQL数据库中，序列主要用于生成唯一的标识符，特别是在需要自动递增的主键字段上。`AUTO_INCREMENT`是MySQL中实现序列化的一种方式，它允许你在插入新记录时自动为某个字段提供下一个可用的唯一整数值。这个特性...
Mysql auto_increment 重新计数(让id从1开始)
12-15
alter table `test` auto_increment=1 （好处，可以设置AUTO_INCREMENT 为任意值开始） 提示：如果表列和数据很多，速度会很慢，如90多万条，会在10分钟以上。 方法二 truncate table test （好处，简单，AUTO_...
Proteus 8 Professional.lnk
06-29
Proteus 8 Professional.lnk
wx131智能停车场管理系统-ssm+vue+uniapp-小程序.zip（可运行源码+sql文件+文档）
06-29
本智能停车场管理系统以ssm作为框架，b/s模式以及MySql作为后台运行的数据库，同时使用Tomcat用为系统的服务器。本系统主要包括首页、个人中心、用户管理、车位信息管理、车位预定管理、系统管理等功能，通过这些功能的实现能够基本满足日常智能停车场管理的操作。 关键词：智能停车场管理系统; ssm;MySql数据库;Tomcat 前台功能：用户进入小程序可以实现首页、地图、我的；在我的页面可以对个人中心和车位预定等功能进行操作； 后台主要是管理员，管理员功能包括首页、个人中心、用户管理、车位信息管理、车位预定管理、系统管理等； 管理员登陆系统后，可以对首页、个人中心、用户管理、车位信息管理、车位预定管理、系统管理等功能进行相应操作
毕设项目：基于BS结构下的OA流程可视化的研究与实现(Java+源代码+文档).zip
06-29
1 引言 1 1.1 课题背景 1 1.2 技术可行性研究 1 1.2.1 Java Applet技术的可行性研究 1 1.2.2 XML技术的可行性研究 1 1.2.3 Microsoft Office Access 2003数据库的可行性研究 1 2 相关基础理论技术以及开发技术 1 2.1工作流的定义与存在问题描述 2 2.1.1 OA中工作流的定义 2 2.1.2工作流中的流程定义问题 3 2.2 java applet和applet绘图技术的介绍 3 2.2.1 applet的介绍 3 2.2.2 Applet的AWT绘制 4 2.3系统环境与平台基础 5 3 系统需求分析与总体设计 5 3.1系统需求分析 5 3.2 系统功能介绍 5 3.3 系统模块功能和设计思想 6 3.4数据库设计 7 3.4.1 E-R图设计 7 3.4.2 表的构建 8 3.4.3 数据库连接实现 9 4 系统功能模块实现 9 4.2流程可视化设计实现 10 4.2.1界面可视化 10 4.2.2 绘图功能设计 11 4.2.3 流程图保存和读取方法设计 17 4.2.4 节点间关系保存的实现 2
学术答辩 (20).pptx
06-29
学术答辩 (20)
文艺高逼格14.pptx
最新发布
06-29
文艺风格ppt模板文艺风格ppt模板 文艺风格ppt模板 文艺风格ppt模板 文艺风格ppt模板 文艺风格ppt模板 文艺风格ppt模板 文艺风格ppt模板 文艺风格ppt模板 文艺风格ppt模板 文艺风格ppt模板 文艺风格ppt模板 文艺风格ppt模板 文艺风格ppt模板 文艺风格ppt模板
数据库AUTO_INCREMENT怎么设置
04-28
在 MySQL 中，可以使用 AUTO_INCREMENT 关键字来设置自增长字段。在创建表时，需要指定该字段为整数类型，并将其设置为主键，然后在字段类型后面添加 AUTO_INCREMENT 关键字即可。 例如，创建一个名为 `users` 的表，其中包含一个自增长的 ID 字段，可以使用以下 SQL 语句： ``` CREATE TABLE users ( id INT(11) NOT NULL AUTO_INCREMENT, name VARCHAR(50) NOT NULL, email VARCHAR(50) NOT NULL, PRIMARY KEY (id) ); ``` 在这个例子中，`id` 字段被指定为整数类型，并使用 `AUTO_INCREMENT` 关键字来设置自增长。主键约束通过 `PRIMARY KEY` 关键字来指定，确保每个 ID 值都是唯一的。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值