主键自增设置

2019独角兽企业重金招聘Python工程师标准>>>

AUTO_INCREMENT是主键的自增起始值，默认是1，它的设置方法有两种:

1, 在建表之时在sql语句中进行设置，如：

CREATE TABLE `archives_sms_templets` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `uid` int(10) unsigned NOT NULL COMMENT '企业用户ID',
  `parent_id` int(10) unsigned NOT NULL COMMENT '父帐号ID',
  `top_id` int(10) unsigned NOT NULL COMMENT '顶级帐号ID',
  `msg_temp_name` varchar(70) NOT NULL COMMENT '短信模板名称',
  `msg_temp_content` varchar(255) NOT NULL COMMENT '短信模板内容',
  `status` tinyint(1) NOT NULL DEFAULT 0 COMMENT '短信模板状态：0审核中，1审核通过，2审核不通过，3已删除',
  `created_at` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '模板插入时间',
  `updated_at` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '模板更新时间',
  `used_at` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '模板最近使用时间',
  PRIMARY KEY (`id`)
  UNIQUE KEY `unique_uid_content` (`uid`,`content`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='人才库发短信功能模板表';

2, 在表已建好的情况下，进行更改，但是更改的设置值必须大于等于已有的AUTO_INCREMENT值，如：

ALTER TABLE XXX AUTO_INCREMENT=100;

另外还有两个可设置值:

1, AUTO_INCREMENT_OFFSET: 起始值的个位数，默认值为1;

2, AUTO_INCREMENT_INCREMENT: 自增的步进，即每次自增增加的值，默认值为1;

这两个设置也是可在建表之时或建表之后进行设置，这两项设置会在重启MySQL之后，恢复为初始值1

以下是官方文档

https://dev.mysql.com/doc/refman/5.6/en/replication-options-master.html

• These two variables affect AUTO_INCREMENT column behavior as follows:

  If either of these variables is changed, and then new rows inserted into a table containing an AUTO_INCREMENT column, the results may seem counterintuitive because the series of AUTO_INCREMENT values is calculated without regard to any values already present in the column, and the next value inserted is the least value in the series that is greater than the maximum existing value in the AUTO_INCREMENT column. The series is calculated like this:

  auto_increment_offset + N × auto_increment_increment

  where N is a positive integer value in the series [1, 2, 3, ...]. For example:

  mysql> SHOW VARIABLES LIKE 'auto_inc%';
  +--------------------------+-------+
  | Variable_name            | Value |
  +--------------------------+-------+
  | auto_increment_increment | 10    |
  | auto_increment_offset    | 5     |
  +--------------------------+-------+
  2 rows in set (0.00 sec)
  
  mysql> SELECT col FROM autoinc1;
  +-----+
  | col |
  +-----+
  |   1 |
  |  11 |
  |  21 |
  |  31 |
  +-----+
  4 rows in set (0.00 sec)
  
  mysql> INSERT INTO autoinc1 VALUES (NULL), (NULL), (NULL), (NULL);
  Query OK, 4 rows affected (0.00 sec)
  Records: 4  Duplicates: 0  Warnings: 0
  
  mysql> SELECT col FROM autoinc1;
  +-----+
  | col |
  +-----+
  |   1 |
  |  11 |
  |  21 |
  |  31 |
  |  35 |
  |  45 |
  |  55 |
  |  65 |
  +-----+
  8 rows in set (0.00 sec)

  The values shown for auto_increment_increment and auto_increment_offset generate the series 5 + N × 10, that is, [5, 15, 25, 35, 45, ...]. The highest value present in the col column prior to the INSERT is 31, and the next available value in the AUTO_INCREMENT series is 35, so the inserted values for col begin at that point and the results are as shown for the SELECT query.

  It is not possible to restrict the effects of these two variables to a single table; these variables control the behavior of all AUTO_INCREMENT columns in all tables on the MySQL server. If the global value of either variable is set, its effects persist until the global value is changed or overridden by setting the session value, or until mysqld is restarted. If the local value is set, the new value affects AUTO_INCREMENT columns for all tables into which new rows are inserted by the current user for the duration of the session, unless the values are changed during that session.

  The default value of auto_increment_increment is 1. See Section 17.4.1.1, “Replication and AUTO_INCREMENT”.

  • auto_increment_increment controls the interval between successive column values. For example:

    mysql> SHOW VARIABLES LIKE 'auto_inc%';
    +--------------------------+-------+
    | Variable_name            | Value |
    +--------------------------+-------+
    | auto_increment_increment | 1     |
    | auto_increment_offset    | 1     |
    +--------------------------+-------+
    2 rows in set (0.00 sec)
    
    mysql> CREATE TABLE autoinc1
        -> (col INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
      Query OK, 0 rows affected (0.04 sec)
    
    mysql> SET @@auto_increment_increment=10;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SHOW VARIABLES LIKE 'auto_inc%';
    +--------------------------+-------+
    | Variable_name            | Value |
    +--------------------------+-------+
    | auto_increment_increment | 10    |
    | auto_increment_offset    | 1     |
    +--------------------------+-------+
    2 rows in set (0.01 sec)
    
    mysql> INSERT INTO autoinc1 VALUES (NULL), (NULL), (NULL), (NULL);
    Query OK, 4 rows affected (0.00 sec)
    Records: 4  Duplicates: 0  Warnings: 0
    
    mysql> SELECT col FROM autoinc1;
    +-----+
    | col |
    +-----+
    |   1 |
    |  11 |
    |  21 |
    |  31 |
    +-----+
    4 rows in set (0.00 sec)

  • auto_increment_offset determines the starting point for the AUTO_INCREMENT column value. Consider the following, assuming that these statements are executed during the same session as the example given in the description for auto_increment_increment:

    mysql> SET @@auto_increment_offset=5;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SHOW VARIABLES LIKE 'auto_inc%';
    +--------------------------+-------+
    | Variable_name            | Value |
    +--------------------------+-------+
    | auto_increment_increment | 10    |
    | auto_increment_offset    | 5     |
    +--------------------------+-------+
    2 rows in set (0.00 sec)
    
    mysql> CREATE TABLE autoinc2
        -> (col INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
    Query OK, 0 rows affected (0.06 sec)
    
    mysql> INSERT INTO autoinc2 VALUES (NULL), (NULL), (NULL), (NULL);
    Query OK, 4 rows affected (0.00 sec)
    Records: 4  Duplicates: 0  Warnings: 0
    
    mysql> SELECT col FROM autoinc2;
    +-----+
    | col |
    +-----+
    |   5 |
    |  15 |
    |  25 |
    |  35 |
    +-----+
    4 rows in set (0.02 sec)

    When the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored.

• auto_increment_offset

  System Variable	Name	auto_increment_offset
  Scope	Global, Session
  Dynamic	Yes
  Permitted Values	Type	integer
  Default	1
  Minimum	1
  Maximum	65535

  This variable has a default value of 1. For more information, see the description for auto_increment_increment.

  Note

  auto_increment_offset is also supported for use with NDB tables.

转载于:https://my.oschina.net/u/3412738/blog/1601137