MySQL数据库管理与开发实践教程 (清华电脑学堂)
上QQ阅读APP看书，第一时间看更新
上一章目录下一章

4.7 自增约束

自增约束是一种由系统自动增加并填入字段数值的约束。如将商品信息表中的商品编号字段设置为自增约束字段，那么系统默认对添加的第一条商品信息中，设置商品编号字段值为1；第二条记录的商品编号字段值为2，以此类推。

自增约束字段由系统自动填入数据，在节省用户工作量和时间的同时，避免因疏忽而加入错误数据。本节介绍自增约束的使用。

4.7.1 自增约束概述

自增约束使用AUTO_INCREMENT修饰符，只适用于INT类型的字段，使该字段的数值每次增加相同的量，通常每次增加1。如该字段的值可以是1、2、3，可以是2001、2002、2003，而不能是b1、b2、b3。在使用AUTO_INCREMENT时，应注意以下几点。

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

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

（3）AUTO_INCREMENT数据列必须有唯一约束，以避免序号重复。

（4）MySQL表中只能有一个AUTO_INCREMENT字段。

（5）自增字段必须创建索引，如主键索引、唯一索引等。

（6）有着外键约束的字段不能够设置为自增字段。

（7）AUTO_INCREMENT数据列必须具备NOT NULL属性。

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

（9）当进行全表数据删除时，AUTO_INCREMENT会从1重新开始编号。全表数据删除指删除表中的所有数据，保留表的结构及其字段定义。

（10）进行全表数据操作时，MySQL实际是做了这样的优化操作：先把数据表里的所有数据和约束删除，然后重建数据表。

（11）被DELETE语句删除的自增约束字段值，除非SQL语句中将该字段值重新插入，否则前面空余的字段值不会复用。

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

MySQL支持多种数据表，每种数据表的自增属性都有所不同。对于MyISAM表，如果用UPDATE语句更新自增列，如果列值与已有的值重复，则会出错。如果大于已有值，则下一个编号从该值开始递增。但是对于InnoDB表，UPDATEAUTO_INCREMENT字段会导致发生报错。以下列举几种常用的表及其自增约束的使用。

1．ISAM表

对于ISAM表，如果把一个NULL插入到一个AUTO_INCREMENT数据列里去，MySQL将自动生成下一个序列编号。编号从1开始，并以1为基数递增。在该表中使用自增约束，有以下两种情况。

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

当插入记录时，如果为AUTO_INCREMENT字段明确指定了一个数值，则会出现两种情况。

（1）如果插入的值与已有的编号重复，则会出现出错信息，因为AUTO_INCREMENT数据列的值必须是唯一的。

（2）如果插入的值大于已编号的值，则会把该值插入到数据列中，并使下一个编号从这个新值开始递增。也就是说，可以跳过一些编号。

对有着标识列的表数据进行操作，有以下几种情况。

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

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

（3）如果用REPLACE语句修改数据表里的现有记录相应的AUTO_INCREMENT值将不会发生变化。

last_insert_id()函数可获得自增列自动生成的最后一个编号。但该函数只与服务器的本次会话过程中生成的值有关。如果在与服务器的本次会话中尚未生成AUTO_INCREMENT值，则该函数返回0。其他数据表的自动编号机制都以ISAM表中的机制为基础。

2．MyISAM数据表

在MyISAM数据表中，其自动编号机制有以下几个特点。

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

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

（3）可用ALTER TABLE table_name AUTO_INCREMENT=n命令来重设自增的起始值。

（4）可使用复合约束在同一个数据表里创建多个相互独立的自增序列。

使用复合约束，在同一个表中创建多个自增字段，首先需要为数据表创建一个由多个数据列组成的PRIMARY KEY OR UNIQUE约束，接着把AUTO_INCREMENT数据列包括在这个约束里作为它的最后一个数据列。

这样，这个复合约束里，前面的那些数据列每构成一种独一无二的组合，最末尾的AUTO_INCREMENT数据列就会生成一个与该组合相对应的序列编号。

3．HEAP数据表

在HEAP数据表中，其自动编号机制有以下几个特点。

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

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

（3）编号不可重用。

（4）HEAP数据表不支持在一个数据表中使用复合约束来生成多个互不干扰的序列编号。

4．BDB数据表

在BDB数据表中，其自动编号机制有以下几个特点。

（1）BDB数据表不能通过CREATE TABLE OR ALTER TABLE的AUTO_ INCREMENT=n选项来改变自增初始值。

（2）可重用编号。

（3）支持在一个数据表里使用复合约束来生成多个互不干扰的序列编号。

5．InnoDB数据表

在InnoDB数据表中，其自动编号机制有以下几个特点。

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

（2）不可重用编号。

（3）不支持在一个数据表里使用复合约束来生成多个互不干扰的序列编号。

4.7.2 初始值和偏移量

在MySQL Workbench工具下，只能够创建初始值和递增偏移量均为1的自增约束，不过使用SQL语句可以设置自增约束的相关属性和变量，修改字段初始值和递增偏移量。这几个属性和变量及其含义如下所示。

（1）AUTO_INCREMENT：自增初始值。

（2）auto_increment_increment：自增值的自增量。

（3）auto_increment_offset：自增值的偏移量。

自增量和偏移量均可以设置为全局或局部变量，并且在字段的数据类型和约束允许的情况下，每个值都可以为1～65 535之间的整数值。如果将变量设置为0会使该变量的值为1；如果试图将这些变量设置为大于65 535或小于0的值，则会将该值设置为65 535；如果将auto_increment_increment或auto_increment_offset设置为非整数值，则会给出错误，并且变量的实际值在这种情况下保持不变。

自增约束字段的数据值并不是必须从一开始就严格按照指定初始值和偏移量来编号的，如一个字段原有数据1，2，1，2，那么同样可以在此基础上为字段添加自增约束并设置初始值为大于2的整数。

并且若数据表中自增字段的数据已经从1排到了10，也可以在此基础上修改自增约束的初始值和偏移量，使10以后的数据根据新的自增规则进行编号。如在自增字段有着1～10这10个数据的表中修改自增约束的递增规则（修改初始值和偏移量两个变量的值）为从15开始每次偏移5，那么第11条数据的字段值为15、第12条数据的字段值为20。

如果修改的初始值比当前的数据值小，执行的SQL不会报错，但是不会生效！MyISAM和InnoDB均是如此。

如果同时设置了auto_increment_offset和auto_increment_increment，那么这两个变量的值最好相同。如果auto_increment_offset的值大于auto_increment_increment的值，auto_increment_offset的值将被忽略。

4.7.3 创建自增约束

创建自增约束可以在表创建或表修改界面（如图4-1所示）中选中AI（或Auto Increment）复选框。

在MySQL Workbench工具下创建的自增约束不需要提供字段的初始值和递增偏移量，默认从1开始每次增加1。

使用SQL语句创建表的时候设置自增约束，代码如下。

CREATE TABLE 表名 (字段名 数据类型 非空约束类型 AUTO_INCREMENT);

上述代码创建了有着默认自增约束的字段，也可在创建语句后为该自增约束添加自增初始值，代码如下。

CREATE TABLE 表名 (字段名 数据类型 非空约束类型 AUTO_INCREMENT)
    AUTO_INCREMENT=初始值;

【范例15】

创建表fruitshop.worker，为主键id添加默认自增约束，代码如下。

CREATE TABLE 'fruitshop'.'worker' (
      'id' INT NOT NULL AUTO_INCREMENT,
      'name' VARCHAR(45) NULL,
      'sex' VARCHAR(4) NULL,
      'age' INT NULL,
      PRIMARY KEY ('id'));

【范例16】

创建表fruitshop.worker，为主键id添加自增约束初始值为8，代码如下。

CREATE TABLE 'fruitshop'.'worker' (
      'id' INT NOT NULL AUTO_INCREMENT,
      'name' VARCHAR(45) NULL,
      'sex' VARCHAR(4) NULL,
      'age' INT NULL,
      PRIMARY KEY ('id')
    )
    AUTO_INCREMENT=8;

4.7.4 修改自增约束

修改自增约束包括多种操作：为字段添加自增约束；修改自增约束的初始值；修改自增约束的偏移量。

1．为字段添加自增约束

使用修改表字段类型的方法，通过MODIFY关键字来添加，语法如下。

ALTER TABLE 表名 MODIFY COLUMN 字段名 数据类新 非空约束 AUTO_INCREMENT;

【范例17】

为fruitshop.fruits表的主键fid字段添加自增约束，代码如下。

ALTER TABLE 'fruitshop'.'fruits' MODIFY COLUMN fid INT NOT NULL AUTO_ INCREMENT;

2．修改自增约束的初始值

修改自增初始值使用ATLER关键字，针对一个表中的自增约束进行修改。

【范例18】

修改fruitshop.fruits表fid字段的自增约束初始值为7，代码如下。

ALTER TABLE 'fruitshop'.'fruits' AUTO_INCREMENT=7;

查看fruitshop.fruits表的定义，代码如下。

SHOW CREATE TABLE 'fruitshop'.'fruits'

上述代码的执行效果如下所示。

+ --------- + --------------------------+
    | Table      | Create Table                 |
    + --------- + --------------------------+
    | fruits      | CREATE TABLE 'fruits' (
      'fid' int(11) NOT NULL AUTO_INCREMENT,
      'fname' varchar(45) NOT NULL,
      'fprice' varchar(45) DEFAULT NULL,
      'ftime' varchar(45) DEFAULT NULL,
      'fwid' int(11) DEFAULT NULL,
      PRIMARY KEY ('fid'),
      KEY 'wid_idx' ('fwid'),
      CONSTRAINT 'fwid_wid' FOREIGN KEY ('fwid') REFERENCES 'workers' ('id') ON DELETE NO ACTION ON UPDATE NO ACTION
    ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 |
    + --------- + --------------------------+
    1 rows

由上述执行结果可以看到AUTO_INCREMENT=7语句，可见自增约束的初始值已经被修改为7。由于fruitshop.fruits表已经有数据了，因此在添加自增约束时，其初始值并不为1，而是比已有数据大的整数。

向fruitshop.fruits表中添加一条数据，仅添加fname字段和fprice字段的数据，代码如下。

INSERT INTO 'fruitshop'.'fruits' ('fname', 'fprice') VALUES ('mango', '15.4');

上述代码执行后查看fruitshop.fruits表中的数据，如下所示。

+ ------- + ----------- + ---------- + --------- + -------- +
    | fid      | fname        | fprice      | ftime      | fwid      |
    + ------- + ----------- + ---------- + --------- + -------- +
    | 1        | orange       | 5            | 6.15       | 1          |
    | 2        | apple        | 2.5          | 6.15       | 2          |
    | 3        | banana       | 3.5          | 6.18       | 1          |
    | 4        | watermelon  | 0.7          | 6.15       | 2          |
    | 7        | mango        | 15.4         |             |            |
    + ------ + ----------- + ---------- + --------- + --------- +
    
    5 rows

从上述执行效果可以看出，新增的数据自增约束列被添加了数据7，该字段将以7开始由系统自动添加数据。

3．修改自增约束的偏移量

字段的自增偏移量和自增值可以使用SHOW VARIABLES语句来查看，如查看服务器的自增约束代码如下。

SHOW VARIABLES LIKE '%auto_increment%';

上述代码的执行结果如下所示。

+ ------------------------------- + ---------- +
    | Variable_name           | Value   |
    + ------------------------------- + ---------- +
    | auto_increment_increment | 1     |
    | auto_increment_offset     | 1     |
    + ------------------------------- + ---------- +
    2 rows

修改服务器auto_increment_offset和auto_increment_increment变量的值，可使用SET关键字，语法如下：

SET [SESSION]/[GLOBAL] auto_increment_increment=变量的值;
    SET [SESSION]/[GLOBAL] auto_increment_ offset=变量的值;

使用GLOBAL关键字修改变量的值，如果不退出session，重新连接，则不能生效，而且只能使用如下代码查询。

SHOW GLOBAL VARIABLES like '%auto_increment%';

不使用SESSION或GLOBAL也可以修改变量的值，但需要重启MySQL才能生效。使用SESSION修改的数据将直接生效，可使用SHOW VARIABLES语句查看。

【范例19】

fruitshop.work表中有4条数据，先为该表添加自增约束，再设置其自增初始值为10，自增量和自增偏移量为2，为表添加两条数据查看效果，步骤如下。

（1）为fruitshop.work表添加自增约束代码省略。设置其自增初始值为10，自增量和自增偏移量为2，代码如下。

ALTER TABLE 'fruitshop'.'work' AUTO_INCREMENT=10;
    SET SESSION auto_increment_increment=2;
    SET SESSION auto_increment_offset=2;

（2）为表添加两条数据，代码如下。

INSERT INTO 'fruitshop'.'work' ('wname', 'wsex') VALUES ('张明', '男');
    INSERT INTO 'fruitshop'.'work' ('wname', 'wsex') VALUES ('王丽', '女');

（3）查看fruitshop.work表中的数据，代码省略，其效果如下所示。

+ ------- + ------- + -------- + -------- + ----------- +
    | wid      | wname    | wsex      | wage      | wemail       |
    + ------- + ------- + -------- + -------- + ----------- +
    | 1        | 梁思      | 女         | 22         | li@126.com  |
    | 2        | 何健      | 男         | 21         | jk@126.com  |
    | 3        | 赵龙      | 男         | 26         | zl@126.com  |
    | 4        | 李虎      | 男         | 25         | lh@126.com  |
    | 10       | 张明      | 男         |            |               |
    | 12       | 王丽      | 女         |            |               |
    + ------- + ------- + -------- + -------- + ---------- +
    6 rows

由上述执行效果可以看出，系统自动为新添加的两条语句填写了wid字段的值，从10开始依次增加2。

4.7.5 删除自增约束

删除自增约束可以使用MySQL Workbench工具或使用SQL语句。在MySQL Workbench工具下的操作可参考唯一约束的删除操作，这里不再详细介绍。

使用SQL语句删除自增约束通过CHANGE COLUMN语句来进行。如删除fruitshop.work表中的自增约束，其删除语句及其执行结果如下所示。

ALTER TABLE 'fruitshop'.'work' CHANGE COLUMN 'wid' 'wid' INT(11) NOT NULL ;
上一章目录下一章