跟我一起学习MySQL技术内幕（第五版）：（第三章学习日记14）

3.4.3使用auto_increment列需要考虑的问题
3.4.4auto_incrementlieder使用提示
3.4.4.1为表增加一个序列编号列
3.4.4.2重置已有列的序列编号
3.4.5在无auto_increment的情况下生成序列
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

3.4.3使用auto_increment列需要考虑的问题

1.auto_increment机制的主要用途是生成一个正整数序列,auto_increment列不支持使用非正数.因此我们还可以吧auto_increment定义为unsigned类型.对于整数列,使用unsigned还有一个好处,在达到该数据类型的范围上限前,可以获得两倍的序列编号
2.把auto_increment添加到列的定义里,并不能得到无穷尽的序列编号.auto_increment序列总是会受到底层数据类型的取值范围的约束.
3.使用truncate table 语句来清除某个表的内容,可以把该表的计数序列重置为重新从1开始.即使对于那些通常不会重用auto_increment值的存储引擎.也是如此.之所以会重置序列,是因为MySQL需要对整表的删除操作进行优化.只要有可能,他会快速丢弃全部的行和索引,并且从头开始重建表,而不是一行一行的删.这样做将导致序列编号信息出现丢失.如果想在删除所有行的同时把序列信息保留下来,那么可以使用带有一个永远为真的where子句的delete语句来组织这个优化操作的执行.

delete from tal_name where ture;

3.4.4auto_incrementlieder使用提示

3.4.4.1为表增加一个序列编号列

假设,你创建并填充了一个表:

create table t(c char(10));
insert into t values('a'),('b'),('c');

接着,你想为这个表增加一个序列编号列.为做到这一点,可以执行alter table语句来增加一个auto_increment列,所定义的类型与create table 语句里使用的类型一样.MySQL会把序列值赋给auto_increment列:

alter table t add i int unsigned not null auto_increment primary key;
select * from  t;

3.4.4.2重置已有列的序列编号

如果表已经有了一个auto_increment列,但是你想要对其进行重新编号,以便消除因删除行而在序列里产生的断裂.实现这一目标的最简单方法是:先删除该列,然后再重新添加它.

先创建一个表,添加行,删除一部分行

create table t(c char(10),i int unsigned auto_increment not null primary key);
insert into t (c)
values('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h'),('i'),('j'),('k');
delete from t where c in ('a','d','f','g','j');

这时候i列的值剩下了 2 3 5 8 9 11
下面重新设置auto_increment列

alter table t
drop primary key,
drop i,
add i int unsigned  not null auto_increment primary key ,
auto_increment =1;

auto_increment = 1 子句把序列的起始编号重置为1 .对于MyISAM, MEMORY或InnoDB表,可以把序列的起始编号设置为不等于1的其他数.对于其他的存储引擎,则可以忽略auto_increment子句,因为他们不允许使用这样的方式设定序列的初始值.序列将从1开始编号.

虽然可以轻易地重新设置列的编号,但是”要怎样才能做到呢?”这个问题仍然很常见,而且通常没有必要这么做.MySQL并不会在意序列里是否存在有断裂,并且在重置序列编号后也不会获得任何性能上的提高.此外,如果在另一个表里有行引用了auto_increment列里的值,那么调整该列的编号将破坏这两个表之间的对应关系.

3.4.5在无auto_increment的情况下生成序列

MySQL支持一种生成序列编号的方法,他根本不需要使用auto_increment列.事实上,它使用的是last_insert_id()函数的另一种带参数的形式.如果在插入或者修改一个列时使用了last_insert_id(expr)函数,那么下次不带参数调用last_insert_id()时,它会返回表达式expr的值.换句话说,MySQL会把表达式expr当作是一个新生成的auto_increment值,因此,你可以先创建一个序列编号,然后再随后的检索会话中检索它,同时不用担心这个编号值会受到其他客户端程序活动的影响.

这种策略的一种用途是:创建一个只有一行的表,其中包含一个在每次需要该序列里下一个值时都会进行更新的值.例如:可以像下面这样创建和初始化这个表:

create table seq_table (seq int unsigned not null);
insert into seq_table values(0);

上面这些语句会创建一个只有一个行的seq_table表,期中seq的值为0.位生成下一个序列编号,并对他进行检索,可以这样做:

update seq_table set seq = last_insert_id( seq + 1 );
select last_insert_id();

上面的update语句将检索seq列的当前值,并把它加上1,从而产生该序列的下一个编号,利用last_insert_id(seq+1)生成的编号值,与auto_increment值很像,因此可以通过不带参数调用last_insert_id()的方式来检索它.last_insert_id()函数是客户端专用的,因此即使在update和select两个操作之间的时间间隔里,与其他客户端程序又生成多个序列编号,你也会检索正确.

利用这种方法,可以生成步长为1(甚至可以为负值)的序列编号.例如,反复执行下面这条语句.将生成一个步长为100的序列:

update seq_table set seq =last_insert_id(seq+100);

步长为-1的单调递减序列:

update seq_table set seq = last_insert_id(-1);

你也可以生成一个从任意值开始编号的序列,只要个seq列设置一个合适的初始值就行.j

前面的讨论描述了如何利用一个只包含一行的表来建立一个计数器.如果需要多个计数器,则可以这样做:首先给这个表增加一列,用作计数器的标识符,然后在这个表里为每个计数器单独增加一行.假设,你有一个网站,想要实现”本网页已被访问过n次”的页计数器.为此,创建一个具有两个列的表:一个列用于保存计数器的唯一标识;另一列则用于保存计数器当前的值.你仍可以用last_insert_id()函数,但需要根据计数器的名字来确定它应该应用与哪一行.例如,可以使用下面的语言来创建这样一个表:

create table counter
(
    name varchar(255) character set latin1 collate latin1_genaral_cs not null,
    value int unsigned,
    primary key (name)
);

其中,name 列是一个字符串,因此你可以把计数器命名为你想要的任何名字.同时为了避免计数器的名字出现重复,他还被定义了primary key .这里假定使用这个表的应用程序,对他们所使用的名字有所了解.就web计数器而言,可以使用每个页面在文档树里的路径名座位计数器名,以此保证计数器名的唯一性.name列的排序规则是区分大小写的,所以路径名的值也是区分大小写.如果你的系统不区分路径名的大小写,则需要使用不区分大小写的排序规则.

在使用counter表时,insert…..on duplicate key update 语句会非常有用,因为它既可以为一个未曾统计过的页面插入一个新行,也可以更新已有页面的技术值.此外,通过使用last_insert_id(expr)函数来生成计数值,你还可以在更新当前计数器后,轻易地将其值检索出来.例如,为了初始化或递增网站主页的计数器,然后检索计数器显示结果,可以像下面这样做:

insert into counter( name, value )
    values('index.html', last_insert_id(1))
    on deplicate key update value = last_insert_id(value+1);
select last_insert_id();

在不使用last_insert_id()函数的情况下,另一种递增已有页面计数器的方法是:

update counter set value =value+1 where name ='index.html';
select value from counter where name ='index.html';

不过,在执行update之后,且在执行select语句之前,如果有另一个客户端递增了这个计数器,那么这种方法就无法正确的工作.可以通过lock table 和unlock table 把这两条语句围起来,解决此问题.或者使用支持事务处理的存储引擎来创建这个表,并且欸子啊一个事务里更新这个表.这两种办法都可以在你使用这个计数器时,阻断其他客户端,但是使用last_insert_id()函数可以更容易地完成这件事情.因为计数器的值是客户端专用的,所以你总能获得你刚插入的那个值,而不会获得来字自其他客户端插入的值;并且,你不用为了达到排斥其他客户端的目的,而是用表锁定或事务来把代码弄得很复杂.