后使用快捷导航没有帐号？
查看: 2536|回复: 2
Mysql中自增字段（AUTO_INCREMENT)的一些常识
金牌会员, 积分 1374, 距离下一级还需 1626 积分
论坛徽章:5
本帖最后由 hong1830 于
00:56 编辑
在系统开发过程中，我们经常要用到唯一编号。使用过my的人都应该知道，mysql有一个定义列为自增的属性：AUTO_INCREMENT。
指定了AUTO_INCREMENT的列必须要建索引，不然会报错，索引可以为主键索引，当然也可以为非主键索引。（不一定要做主键）mysql& create table t4 (id int auto_increment);
ERROR ): Incorr there can be only one auto column and it must be defined as a key
mysql&复制代码下面的定义把t5表的主键定义为了name，而非自增的id字段mysql&
mysql& create table t5 (id int auto_increment,name varchar(20) primary key,key(id));
Query OK, 0 rows affected (0.01 sec)复制代码指定了auto_increment的列，在插入时：如果把一个NULL插入到一个AUTO_INCREMENT数据列里去，MySQL将自动生成下一个序列编号。编号从1开始，并1为基数递增。当插入记录时，没有为AUTO_INCREMENT明确指定值，则等同插入NULL值。mysql& insert into t5 (id,name) values (null,'test');
Query OK, 1 row affected (0.00 sec)
& &
mysql& select * from t5;
+----+------+
| id | name |
+----+------+
|&&2 | test |
+----+------+
1 row in set (0.00 sec)复制代码
上面语句等同于下面语句：mysql& insert into t5 (name) values ('test');复制代码
当插入记录时，如果为AUTO_INCREMENT字段明确指定了一个数值，则会出现两种情况：
情况一，如果插入的值与已有的编号重复，则会出现出 错信息，因为AUTO_INCREMENT数据列的值必须是唯一的；&&情况二，如果插入的值大于已编号的值，则会把该插入到数据列中，并使在下一个编号将从这个新值开始递增。## 初始表
mysql& show create table t2G;
*************************** 1. row ***************************
& && & Table: t2
Create Table: CREATE TABLE `t2` (
&&`id` int(11) NOT NULL AUTO_INCREMENT,
&&PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
& &
## 插入数据
mysql& insert into t2 values (null),(null),(null);&&
Query OK, 3 rows affected (0.00 sec)
& &
## auto_increment变成4
mysql& show create table t2G;
*************************** 1. row ***************************
& && & Table: t2
Create Table: CREATE TABLE `t2` (
&&`id` int(11) NOT NULL AUTO_INCREMENT,
&&PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
& &
## 插入7
mysql& insert into t2 values (7);
Query OK, 1 row affected (0.00 sec)
& &
## auto_increment变成8
mysql& show create table t2G;
*************************** 1. row ***************************
& && & Table: t2
Create Table: CREATE TABLE `t2` (
&&`id` int(11) NOT NULL AUTO_INCREMENT,
&&PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8复制代码换句话说，就是自增字段可以跳过一些编号对于MyISAM表，如果用UPDATE命令更新自增列，如果列值与已有的值重复，则会出错。如果大于已有值，则下一个编号从该值开始递增。但是对于innodb表，update auto_increment字段，会导致发生报错
MyISAM表的update如下所示
## 当前状态
mysql& show create table t2G;
*************************** 1. row ***************************
& && & Table: t2
Create Table: CREATE TABLE `t2` (
&&`id` int(11) NOT NULL AUTO_INCREMENT,
&&PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
& &
## 将id=7的数据update为10
mysql& update t2 set id=10 where id=7;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1&&Changed: 1&&Warnings: 0
& &
## 最新的auto_increment变为11
mysql& show create table t2G;
*************************** 1. row ***************************
& && & Table: t2
Create Table: CREATE TABLE `t2` (
&&`id` int(11) NOT NULL AUTO_INCREMENT,
&&PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)复制代码Innodb表的update操作如下所示
（可以看到在update前后，表定义语句没有变化），接着执行insert会导致主键错误！
mysql& show create table t3G;
*************************** 1. row ***************************
& && & Table: t3
Create Table: CREATE TABLE `t3` (
&&`id` int(11) NOT NULL AUTO_INCREMENT,
&&PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
& &
## updae更新操作
mysql& update t3 set id=10 where id=7;
Query OK, 1 row affected (0.27 sec)
Rows matched: 1&&Changed: 1&&Warnings: 0
& &
mysql& show create table t3G;
*************************** 1. row ***************************
& && & Table: t3
Create Table: CREATE TABLE `t3` (
&&`id` int(11) NOT NULL AUTO_INCREMENT,
&&PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)复制代码Innodb表继续插入会导致报错，但是只会报错一次，跳过10之后会正常插入
mysql& insert into t3 values (null);
Query OK, 1 row affected (0.46 sec)
& &
mysql& insert into t3 values (null);
Query OK, 1 row affected (0.11 sec)
& &
mysql& insert into t3 values (null);
ERROR ): Duplicate entry '10' for key 'PRIMARY'复制代码
被delete语句删除的id值，除非sql中将id重新插入，否则前面空余的id不会复用。delete from t3该语句不会引起auto_increment的变化
mysql& delete from t3;
Query OK, 8 rows affected (0.34 sec)
&&
mysql& show create table t3G;
*************************** 1. row ***************************
& && & Table: t3
Create Table: CREATE TABLE `t3` (
&&`id` int(11) NOT NULL AUTO_INCREMENT,
&&PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)复制代码truncate table t3 该语句会引起auto_increment的变化，从头开始。
mysql& truncate table t3;
Query OK, 0 rows affected (0.53 sec)
&&
mysql& show create table t3G;
*************************** 1. row ***************************
& && & Table: t3
Create Table: CREATE TABLE `t3` (
&&`id` int(11) NOT NULL AUTO_INCREMENT,
&&PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)复制代码
last_insert_id()函数可获得自增列自动生成的最后一个编号。但该函数只与服务器的本次会话过程中生成的值有关。如果在与服务器的本次会话中尚未生成AUTO_INCREMENT值，则该函数返回0。
中级会员, 积分 485, 距离下一级还需 15 积分
论坛徽章:2
以前只知道 自增的列必须要建为主键&&看来不是那么回事& & 呵呵& &学习了！
扫一扫加入本版微信群怎么重置mysql的自增列AUTO_INCREMENT初时值
字体：[ ] 类型：转载 时间：
怎么重置mysql的自增列想必有很多的朋友都不会吧，下面与大家分享下常用的几种方法，不懂的朋友可以了解下哈，希望对大家有所帮助
重置 MySQL 自增列 AUTO_INCREMENT 初时值 注意, 使用以下任意方法都会将现有数据删除. 方法一: delete from tb1; ALTER TABLE tbl AUTO_INCREMENT = 100; （好处, 可以设置 AUTO_INCREMENT 为任意值开始） 提示:如果表列和数据很多, 速度会很慢, 如90多万条, 会在10分钟以上. 方法二: truncate tb1; （好处, 简单, AUTO_INCREMENT 值重新开始计数.） 怎么重置mysql的自增列 1. 支持设置自增列的值 ALTER TABLE table_name AUTO_INCREMENT = 1; 不过这种方式自能设置大于当前使用的值，不能设置小于等于当前已经使用的自增列的值。myisam如果设置小于等于，则自增列的值会自动设置为 当前最大值加1。innodb则不会改变。 2.通过TRUNCATE把自增列设置为0，从MySQL 5.0.13开始TRUNCATE就能重置自增列为0.myisam和innode都是如此。 TRUNCATE TABLE table_ 3.drop和create重建表方式重置自增列为0 DROP TABLE table_ CREATE TABLE table_name { ... };
您可能感兴趣的文章:
大家感兴趣的内容
12345678910
最近更新的内容
常用在线小工具MySql中重新设置AUTO_INCREMENT自增值方法-mysql教程-数据库-壹聚教程网MySql中重新设置AUTO_INCREMENT自增值方法
在mysql应用中AUTO_INCREMENT通常是设置为主自增id了，但有时我们会测试数据测试数据之后再使用delete删除了数据AUTO_INCREMENT自增值会有空间了，那么我们要如何重新设置AUTO_INCREMENT自增值呢？下面来看看吧．
一般来说，自增值主要是数据表主键或者具有唯一性的字段，在MySQL中可通过数据列的AUTO_INCREMENT属性来自动生成。
可在建表时使用&AUTO_INCREMENT=n&来指定一个自增的初始值，比如：
CREATE TABLE test
id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(15) NOT NULL
)AUTO_INCREMENT = 100;
此例建立一个TEST表，主键ID设置为自增，初始值为 100。
当我们需要修改自增起始值时，可用 alter table table_name AUTO_INCREMENT=n 命令来重设。
比如表 test 的ID最大值为150，但是因为某种原因导致自增值已经到了250，所以需要重设为151以便于下次ID从151开始往上增，语法如下：
ALTER TABLE test AUTO_INCREMENT=151
（1）如果把一个NULL插入到一个AUTO_INCREMENT数据列里去，MySQL将自动生成下一个序列编号。编号从1开始，并1为基数递增。
（2）把0插入AUTO_INCREMENT数据列的效果与插入NULL值一样。但不建议这样做，还是以插入NULL值为好。
（3）当插入记录时，没有为AUTO_INCREMENT明确指定值，则等同插入NULL值。
（4）当插入记录时，如果为AUTO_INCREMENT数据列明确指定了一个数值，则会出现两种情况，情况一，如果插入的值与已有的编号重复，则会出现出错信息，因为AUTO_INCREMENT数据列的值必须是唯一的；情况二，如果插入的值大于已编号的值，则会把该值插入到数据列中，并使在下一个编号将从这个新值开始递增。也就是说，可以跳过一些编号。
（5）如果用UPDATE命令更新自增列，如果列值与已有的值重复，则会出错。如果大于已有值，则下一个编号从该值开始递增。
如何修改Mysql的Auto_increment_increment全局变量，自增步长
先了解下查看全局变量的命令，以下命令都是登陆mysql后操作：
#查看auto_inc开头的全局变量，也就是auto_increment_increment和auto_increment_offset这2个变量
mysql& SHOW VARIABLES LIKE 'auto_inc%';&
设置全局变量
#设置auto_increment_increment自增步长为n，也就是每插入一条数据，就加n，这个N必须是一个数字。默认是1
mysql& SET @@auto_increment_increment=n;
#设置auto_increment_offset自增开始数字为m，在新建表的自增熟悉时候，且表示空的。自增数字默认从m开始
mysql& SET @@auto_increment_offset=m;
如果以上方法还不生效，或者重启mysql后，又变回来了。那肯定是在my.cnf里面设置了全局变量。这个必须到配置文件里面去修改了；这种修改永久有效。而且无法通过上面的操作再次被修改。
用vi编辑器打开配置文件，默认位置
找到 auto_increment_increment 变量设置的地方；VI里面可以用 &/auto_increment_increment& 找到。
找到后设置
auto_increment_increment=1;即可
其他全局变量也可在此配置文件里面永久设置好。
配置文件修改好后，要重启mysql服务才会生效。
#/etc/init.d/mysql restart
上一页： &&&&&下一页：相关内容
暂无与此文章相关内容最新内容MySQL添加自增列失败 - 推酷
MySQL添加自增列失败
想往一个表里添加一个自增列做主键，居然失败报告无法读取，这是怎么回事？
1、问题描述
有位朋友在升级discuz论坛数据库时遇到问题了，想给一个表添加自增列做主键，结果发生下面的报错：
mysql& ALTER TABLE pre_common_credit_log ADD `logid` mediumint(8) unsigned NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (logid) ;
ERROR 1467 (HY000): Failed to read auto-increment value from storage engine
2、原因分析
从报错信息来看，第一反应是：数据表损坏了。不过，再仔细想想，肯定不是啊，否则报错也不是这个了，而是像下面这样的了：
1030 Got error -1 from storage engine
再仔细一想，更大的可能性是：
该表里的总数据量，超过了mediumint最大值所致。
让朋友把自增列数据类型改成int或者bigint，果真就好了。
3、其他建议
建议使用discuz的同学们可以考虑这么做：
如果启用了抢楼功能，那么就把post相关的表继续保留使用MyISAM引擎。或者自己动手改造代码，把抢楼功能中的楼梯值用redis来存储；
如果没有启用抢楼功能，那么就可以放心的把所有表引擎改成InnoDB了。
顺便，再次吐槽一下discuz的某些功能设计，比如把session表用HEAP引擎，其他表默认引擎还是MyISAM。不过，这并不影响discuz成为国内最优秀的论坛解决方案（没办法，没更好的了，占了先机）。
已发表评论数()
请填写推刊名
描述不能大于100个字符!
权限设置： 公开
仅自己可见
正文不准确
标题不准确
排版有问题
主题不准确
没有分页内容
图片无法显示
视频无法显示
与原文不一致