mysql数据插入失败或者事务回滚后解决主键自增的方法

问题:

有时我们在service层操作数据库有异常时会事务回滚,或者直接操作数据库失败时,下次再向数据库同一张表中增加数据时,如果该表的id主键自增,那么此时新增数据的id仍然会按照失败时缓存在内存字典中的Id自增,并不是按照表中实际id自增.

解决办法:

方法一:

例如: 我使用的role表,需新增表数据

mysql 语句自增数字 mysql数据自增_主键自增

可以这样实现:

INSERT into t_role (id,name,keyword,description) 
    SELECT (IFNULL(max(id),0) + 1),'zs','zs','zs' from t_role

这里MySQL中用的是IFNULL,如果是orcale需使用NVL

方法二:

每次插入前都先查询一下该表中Id的最大值, 然后在插入数据时把  "最大值+1"  作为id插入到表中;

方法三:

查资料也有人说通过临时表的方式, 还没试过; 还有一些是通过把id单独作为一张表的方式;

补充资料:

mysql 从一个表中查数据并插入另一个表实现方法

类别一、 如果两张张表（导出表和目标表）的字段一致，并且希望插入全部数据，可以用这种方法：

INSERT INTO 目标表 SELECT * FROM 来源表 ;

例如，要将 articles 表插入到 newArticles 表中，则可以通过如下SQL语句实现：

INSERT INTO newArticles SELECT * FROM articles ;

类别二、 如果只希望导入指定字段，可以用这种方法：

INSERT INTO 目标表 (字段1, 字段2, …) SELECT 字段1, 字段2, … FROM 来源表 ;

请注意以上两表的字段必须一致，否则会出现数据转换错误。

注意: 请注意以上两表的字段必须一致，否则会出现数据转换错误。

类别三、 如果目标表不存在,希望新建目标表，可以用这种方法：

select * into 目标表 from 原表

MySql中重新设置AUTO_INCREMENT自增值方法

查询表名为tableName的auto_increment值：

	修改表名为tableName的auto_increment值：

	ALTER TABLE tableName auto_increment=number ;

扩充单表容量
ALTER TABLE tbl_nameMAX_ROWS=1000000000 AVG_ROW_LENGTH=1500
一般来说，自增值主要是数据表主键或者具有唯一性的字段，在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编辑器打开配置文件，默认位置

#vi /etc/my.cnf
找到 auto_increment_increment 变量设置的地方；VI里面可以用 “/auto_increment_increment” 找到。
找到后设置
 auto_increment_increment=1;即可其他全局变量也可在此配置文件里面永久设置好。
 配置文件修改好后，要重启mysql服务才会生效。
 #/etc/init.d/mysql restart