MySQL里AUTO_INCREMENT表里插入0值的问题

转：http://blog.youkuaiyun.com/nova_pegasus/article/details/6538984

昨天在搞给注册的玩家自动发放avatar item的问题，最后还是决定在DB这一级来做。

自己写了一个trigger, 目前是在AccountStat这个表插入时来触发。当然是不是放在Account表，CharacterAttr表或者GameCharacter表上会更好，还是要了解清楚注册的流程才能定。

主要的问题不在这里，在调试的过程中，发现男性的默认脸部的ItemID是0，往PlayerItem里插入里也是写的0这个ID。

可是到Item表里一看，奇怪，根本就没有ID为0的数据，怎么回事呢？查出一下Item的生成脚本和插入数据的脚本：

 

 

 

 

 注意这里每3行里的"`ItemID` int(10) unsigned NOT NULL auto_incre

[c-sharp]  view plain copy print ?

1. DROP TABLE IF EXISTS `Item`;  
2. CREATE TABLE `Item` (  
3.   `ItemID` int(10) unsigned NOT NULL auto_increment,  
4.   `ItemTypeID` tinyint(1) NOT NULL,  
5.   `Gender` tinyint(1) default NULL COMMENT 'indicate which gender the item is used by. 0:man 1:woman 2:both',  
6.   `Description` varchar(64) default NULL,  
7.   `ItemName` varchar(64) default NULL,  
8.   PRIMARY KEY  (`ItemID`,`ItemTypeID`),  
9.   KEY `ItemTypeID` (`ItemTypeID`)  
10. ) ENGINE=InnoDB AUTO_INCREMENT=90 DEFAULT CHARSET=utf8;  
11. INSERT INTO `Item` VALUES ('0','0','0',' ','MFace1'), ('1','0','0',' ','MFace2'), ('2','0','0',' ','MFace3'), ('3','0','1','','FFace1'), ('4','0','1','','FFace2'), ('5','0','1','','FFace3'), ('7','2','1','','FHair1'), ('9','2','1','','FHair2'), ('11','2','1','','FHair3'), ('13','2','1','','FHair4'), ('15','2','0',' ','MHair1'), ('17','2','0',' ','MHair2'), ('19','2','0',' ','MHair3'), ('21','3','1','','FCloth1'), ('22','3','1','','FCloth2'), ('23','3','1','','FCloth3'), ('24','3','1','','FCloth4'), ('28','3','0','  ','MCloth1'), ('29','3','0',' ','MCloth2'), ('30','3','0',' ','MCloth3'), ('31','3','0',' ','MCloth4'), ('39','0','0',' ','MFace1'), ('42','4','1',' ','FGlass1'), ('43','4','1',' ','FGlass2'), ('44','4','1',' ','FGlass3'), ('45','4','1',' ','FGlass4'), ('46','4','0',' ','MGlass1'), ('47','4','0',' ','MGlass2'), ('48','4','0',' ','MGlass3'), ('49','1','1','','FHat1'), ('50','1','1','','FHat2'), ('51','1','1','','FHat3'), ('52','1','1','','FHat4'), ('53','1','0',' ','MHat1'), ('54','1','0',' ','MHat2'), ('55','1','0',' ','MHat3'), ('56','2','1','','FHair5'), ('58','2','1','','FHair6'), ('89','1','0',' ','MHat4');  

ment"和第10行里的"ENGINE=InnoDB AUTO_INCREMENT=90 DEFAULT CHARSET=utf8“的不同。

在表列定义里的这一个是指默认从数字1开始自增计数；

第10行里这一个是指再有新的数据插入里自90开始自增；这个属性在不同的数据库engine上的定义有所不同。在使用前最好查清楚。

这里要说的是第3行里的这一个auto_increment决定了一默认状态下第一条数据的ItemID是从1而不是从0开始写入的。这里就有了一个问题：第11行里按脚本开始插入第一条数据时，itemID是0，但是这一条纪录在不存在的：

[c-sharp]  view plain copy print ?

1. select * from item;  
2. select FOUND_ROWS();  
3.   
4. 1   0   0       MFace2  
5. 2   0   0       MFace3  
6. 3   0   1       FFace1  
7. 4   0   1       FFace2  
8. 5   0   1       FFace3  
9. 7   2   1       FHair1  
10. 9   2   1       FHair2  
11. 11  2   1       FHair3  
12. 13  2   1       FHair4  
13. 15  2   0       MHair1  
14. 17  2   0       MHair2  
15. 19  2   0       MHair3  
16. 21  3   1       FCloth1  
17. 22  3   1       FCloth2  
18. 23  3   1       FCloth3  
19. 24  3   1       FCloth4  
20. 28  3   0       MCloth1  
21. 29  3   0       MCloth2  
22. 30  3   0       MCloth3  
23. 31  3   0       MCloth4  
24. 39  0   0       MFace1  
25. 42  4   1       FGlass1  
26. 43  4   1       FGlass2  
27. 44  4   1       FGlass3  
28. 45  4   1       FGlass4  
29. 46  4   0       MGlass1  
30. 47  4   0       MGlass2  
31. 48  4   0       MGlass3  
32. 49  1   1       FHat1  
33. 50  1   1       FHat2  
34. 51  1   1       FHat3  
35. 52  1   1       FHat4  
36. 53  1   0       MHat1  
37. 54  1   0       MHat2  
38. 55  1   0       MHat3  
39. 56  2   1       FHair5  
40. 58  2   1       FHair6  
41. 89  1   0       MHat4  
42. 90  0   0       MFace1  

 总的记录数还是39条，没有少，不过原本希望的ItemID应该是0的MFace1的ItemID变成了90。 这里有一个变量的值需要修改一下才能达到我们预想的效果：

注意下面代码的第1行。

把item表删除后重新建表，同样的脚本再插入数据，结果就变成了：

[c-sharp]  view plain copy print ?

1. SET sql_mode='NO_AUTO_VALUE_ON_ZERO';  
2. DROP TABLE IF EXISTS `Item`;  
3. CREATE TABLE `Item` (  
4.   `ItemID` int(10) unsigned NOT NULL auto_increment,  
5.   `ItemTypeID` tinyint(1) NOT NULL,  
6.   `Gender` tinyint(1) default NULL COMMENT 'indicate which gender the item is used by. 0:man 1:woman 2:both',  
7.   `Description` varchar(64) default NULL,  
8.   `ItemName` varchar(64) default NULL,  
9.   PRIMARY KEY  (`ItemID`,`ItemTypeID`),  
10.   KEY `ItemTypeID` (`ItemTypeID`)  
11. ) ENGINE=InnoDB AUTO_INCREMENT=90 DEFAULT CHARSET=utf8;  
12.   
13. INSERT INTO `Item` VALUES ('0','0','0',' ','MFace1'), ('1','0','0',' ','MFace2'), ('2','0','0',' ','MFace3'), ('3','0','1','','FFace1'), ('4','0','1','','FFace2'), ('5','0','1','','FFace3'), ('7','2','1','','FHair1'), ('9','2','1','','FHair2'), ('11','2','1','','FHair3'), ('13','2','1','','FHair4'), ('15','2','0',' ','MHair1'), ('17','2','0',' ','MHair2'), ('19','2','0',' ','MHair3'), ('21','3','1','','FCloth1'), ('22','3','1','','FCloth2'), ('23','3','1','','FCloth3'), ('24','3','1','','FCloth4'), ('28','3','0','  ','MCloth1'), ('29','3','0',' ','MCloth2'), ('30','3','0',' ','MCloth3'), ('31','3','0',' ','MCloth4'), ('39','0','0',' ','MFace1'), ('42','4','1',' ','FGlass1'), ('43','4','1',' ','FGlass2'), ('44','4','1',' ','FGlass3'), ('45','4','1',' ','FGlass4'), ('46','4','0',' ','MGlass1'), ('47','4','0',' ','MGlass2'), ('48','4','0',' ','MGlass3'), ('49','1','1','','FHat1'), ('50','1','1','','FHat2'), ('51','1','1','','FHat3'), ('52','1','1','','FHat4'), ('53','1','0',' ','MHat1'), ('54','1','0',' ','MHat2'), ('55','1','0',' ','MHat3'), ('56','2','1','','FHair5'), ('58','2','1','','FHair6'), ('89','1','0',' ','MHat4');  
14.   
15. SELECT * FROM Item;  

[c-sharp]  view plain copy print ?

1. 0   0   0       MFace1  
2. 1   0   0       MFace2  
3. 2   0   0       MFace3  
4. 3   0   1       FFace1  
5. 4   0   1       FFace2  
6. 5   0   1       FFace3  
7. 7   2   1       FHair1  
8. 9   2   1       FHair2  
9. 11  2   1       FHair3  
10. 13  2   1       FHair4  
11. 15  2   0       MHair1  
12. 17  2   0       MHair2  
13. 19  2   0       MHair3  
14. 21  3   1       FCloth1  
15. 22  3   1       FCloth2  
16. 23  3   1       FCloth3  
17. 24  3   1       FCloth4  
18. 28  3   0       MCloth1  
19. 29  3   0       MCloth2  
20. 30  3   0       MCloth3  
21. 31  3   0       MCloth4  
22. 39  0   0       MFace1  
23. 42  4   1       FGlass1  
24. 43  4   1       FGlass2  
25. 44  4   1       FGlass3  
26. 45  4   1       FGlass4  
27. 46  4   0       MGlass1  
28. 47  4   0       MGlass2  
29. 48  4   0       MGlass3  
30. 49  1   1       FHat1  
31. 50  1   1       FHat2  
32. 51  1   1       FHat3  
33. 52  1   1       FHat4  
34. 53  1   0       MHat1  
35. 54  1   0       MHat2  
36. 55  1   0       MHat3  
37. 56  2   1       FHair5  
38. 58  2   1       FHair6  
39. 89  1   0       MHat4  

前后两次的区别就在于如果没有'NO_AUTO_VALUE_ON_ZERO'，MySQL会从1开始自增，对于想要插入的itemID为0的数据，会按照等等同于NULL值来处理，即按照'AUTO_INCREMENT=90'这一语句来记成90；如果设置了'NO_AUTO_VALUE_ON_ZERO'，对于想要插入的itemID为0的数据就会记成'0'.

记得这个设置是针对每次会话的，重新开始一人SQL会话如果不单独设置又会使用默认的SQL MODE属性。

其它关于AUTO_INCREMENT的一些注意事项：

在数据库应用，我们经常要用到唯一编号，以标识记录。在MySQL中可通过数据列的AUTO_INCREMENT属性来自动生成。MySQL支持多种数据表，每种数据表的自增属性都有差异，这里将介绍各种数据表里的数据列自增属性。

• ISAM表

  • 如果把一个NULL插入到一个AUTO_INCREMENT数据列里去，MySQL将自动生成下一个序列编号。编号从1开始，并1为基数递增。

  • 把0插入AUTO_INCREMENT数据列的效果与插入NULL值一样。但不建议这样做，还是以插入NULL值为好。

  • 当插入记录时，没有为AUTO_INCREMENT明确指定值，则等同插入NULL值。

  • 当插入记录时，如果为AUTO_INCREMENT数据列明确指定了一个数值，则会出现两种情况，情况一，如果插入的值与已有的编号重复，则会出现出错信息，因为AUTO_INCREMENT数据列的值必须是唯一的；情况二，如果插入的值大于已编号的值，则会把该插入到数据列中，并使在下一个编号将从这个新值开始递增。也就是说，可以跳过一些编号。

  • 如果自增序列的最大值被删除了，则在插入新记录时，该值被重用。

  • 如果用UPDATE命令更新自增列，如果列值与已有的值重复，则会出错。如果大于已有值，则下一个编号从该值开始递增。

  • 如果用replace命令基于AUTO_INCREMENT数据列里的值来修改数据表里的现有记录，即AUTO_INCREMENT数据列出现在了 replace命令的where子句里，相应的AUTO_INCREMENT值将不会发生变化。但如果replace命令是通过其它的PRIMARY KEY OR UNIQUE索引来修改现有记录的(即AUTO_INCREMENT数据列没有出现在replace命令的where子句中)，相应的 AUTO_INCREMENT值--如果设置其为NULL(如没有对它赋值)的话--就会发生变化。

  • last_insert_id()函数可获得自增列自动生成的最后一个编号。但该函数只与服务器的本次会话过程中生成的值有关。如果在与服务器的本次会话中尚未生成AUTO_INCREMENT值，则该函数返回0。

  其它数据表的自动编号机制都以ISAM表中的机制为基础。

• MyISAM数据表

  • 删除最大编号的记录后，该编号不可重用。

  • 可在建表时可用“AUTO_INCREMENT=n”选项来指定一个自增的初始值。

  • 可用alter table table_name AUTO_INCREMENT=n命令来重设自增的起始值。

  • 可使用复合索引在同一个数据表里创建多个相互独立的自增序列，具体做法是这样的：为数据表创建一个由多个数据列组成的PRIMARY KEY OR UNIQUE索引，并把AUTO_INCREMENT数据列包括在这个索引里作为它的最后一个数据列。这样，这个复合索引里，前面的那些数据列每构成一种独一无二的组合，最末尾的AUTO_INCREMENT数据列就会生成一个与该组合相对应的序列编号。

• HEAP数据表

  • HEAP数据表从MySQL4.1开始才允许使用自增列。

  • 自增值可通过CREATE TABLE语句的 AUTO_INCREMENT=n选项来设置。

  • 可通过ALTER TABLE语句的AUTO_INCREMENT=n选项来修改自增始初值。

  • 编号不可重用。

  • HEAP数据表不支持在一个数据表中使用复合索引来生成多个互不干扰的序列编号。

• BDB数据表

  • 不可通过CREATE TABLE OR ALTER TABLE的AUTO_INCREMENT=n选项来改变自增初始值。

  • 可重用编号。

  • 支持在一个数据表里使用复合索引来生成多个互不干扰的序列编号。

• InnDB数据表

  • 不可通过CREATE TABLE OR ALTER TABLE的AUTO_INCREMENT=n选项来改变自增初始值。

  • 不可重用编号。

  • 不支持在一个数据表里使用复合索引来生成多个互不干扰的序列编号。

在使用AUTO_INCREMENT时，应注意以下几点：

• AUTO_INCREMENT是数据列的一种属性，只适用于整数类型数据列。

• 设置AUTO_INCREMENT属性的数据列应该是一个正数序列，所以应该把该数据列声明为UNSIGNED，这样序列的编号个可增加一倍。

• AUTO_INCREMENT数据列必须有唯一索引，以避免序号重复。

• AUTO_INCREMENT数据列必须具备NOT NULL属性。

• AUTO_INCREMENT数据列序号的最大值受该列的数据类型约束，如TINYINT数据列的最大编号是127,如加上UNSIGNED，则最大为255。一旦达到上限，AUTO_INCREMENT就会失效。

• 当进行全表删除时，AUTO_INCREMENT会从1重新开始编号。全表删除的意思是发出以下两条语句时：

  delete from table_name;ortruncate table table_name

  这是因为进行全表操作时，MySQL实际是做了这样的优化操作：先把数据表里的所有数据和索引删除，然后重建数据表。如果想删除所有的数据行又想保留序列编号信息，可这样用一个带where的delete命令以抑制MySQL的优化：

  delete from table_name where 1;

  这将迫使MySQL为每个删除的数据行都做一次条件表达式的求值操作。

• 强制MySQL不复用已经使用过的序列值的方法是：另外创建一个专门用来生成AUTO_INCREMENT序列的数据表，并做到永远不去删除该表的记录。当需要在主数据表里插入一条记录时，先在那个专门生成序号的表中插入一个NULL值以产生一个编号，然后，在往主数据表里插入数据时，利用 LAST_INSERT_ID()函数取得这个编号，并把它赋值给主表的存放序列的数据列。如：

  insert into id set id = NULL;insert into main set main_id = LAST_INSERT_ID();

• 可用alter命令给一个数据表增加一个具有AUTO_INCREMENT属性的数据列。MySQL会自动生成所有的编号。

• 要重新排列现有的序列编号，最简单的方法是先删除该列，再重建该，MySQL会重新生连续的编号序列。

• 在不用AUTO_INCREMENT的情况下生成序列，可利用带参数的LAST_INSERT_ID()函数。如果用一个带参数的 LAST_INSERT_ID(expr)去插入或修改一个数据列，紧接着又调用不带参数的LAST_INSERT_ID()函数，则第二次函数调用返回的就是expr的值。下面演示该方法的具体操作：

  先创建一个只有一个数据行的数据表：create table seq_table (id int unsigned not null);insert into seq_table values (0);接着用以下操作检索出序列号：
  update seq_table set seq = LAST_INSERT_ID( seq + 1 );select LAST_INSERT_ID();
  通过修改seq+1中的常数值，可生成不同步长的序列，如seq+10可生成步长为10的序列。

  该方法可用于计数器，在数据表中插入多行以记录不同的计数值。再配合LAST_INSERT_ID()函数的返回值生成不同内容的计数值。这种方法的优点是不用事务或LOCK，UNLOCK表就可生成唯一的序列编号。不会影响其它客户程序的正常表操作。