MySQL自增长键理解

• 自增长键

  # 查看自增长键配置
  >>> SHOW VARIABLES LIKE 'auto_inc%';
  auto_increment_increment  1     # 步长
  auto_increment_offset     1     # 初始值
  

  假设，某次要插入的值是X，当前的自增值是Y

  1. 如果X<Y，那么这个表的自增值不变

  2. 如果X>=Y，就需要把当前自增值修改为新的自增值

  新的自增值生成算法是：从auto_increment_offset（初始值）开始，以auto_increment_increment（步长）为步长，持续叠加，直到找到第一个大于X的值，作为新的自增值.

  自增字段必须是主键。

• 使用案例

• 创建新表时指定自增长键

  create table test(id int primary key auto_increment,name VARCHAR(20));
  INSERT INTO test(name) values ('111'),('123');
  

• 修改已建好的表

  ALTER TABLE <table_name> CHANGE ID id int auto_increment;  # id必须是主键
  alter table table_name modify id int auto_increment;
  ALTER TABLE <table_name> ADD COLUMN id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY; # 新建一列
  ALTER TABLE <table_name> MODIFY id INT(10) UNSIGNED AUTO_INCREMENT FIRST; # 将最后一列移动到第一列
  alter table bu_staff  auto_increment=10000;   # 给自增值设置初始值
  

  基本命令格式如下:
  ALTER TABLE tbl_name MODIFY col_name column_definition

  其中column_definition字段需要包括以下内容:
  1、该列的数据类型及AUTO_INCREMENT字段；
  2、如该列必须为NOT NULL及PRIMARY KEY（UNIQUE）属性。如果不是，需添加相应定义。

• 清空表内数据，包括自增id

  truncate table <table_name>;
  

• 如何避免断层

  使用自增主键，如果删除一段记录，新插入数据不考虑删除行为，会自动增长。这样自增主键就无法表示表中总体数据量了。

  想要实现自增主键不受删除影响，需要在每次删除以后都重置自动增长的位置。就是auto_increment_offset这个参数。

  ALTER TABLE <table_name> AUTO_INCREMENT=<num>;
  

• References

1. MySQL设置自增长值
2. MySQL表添加AUTO_INCREMENT列或者把现有的表字段设置为AUTO_INCREMENT
3. MySql 不存在则插入，存在则更新或忽略