最近单位做IM聊天群
其中一个功能是新建群,获取群号.
群号是我们运营定义的。好的号码要留着卖吧.

模拟群号的号池.
create table test
(
    id int primary key,
    state int
)engine =innodb;

insert into test values
(1,0),
(2,0),
(3,0),
(4,0),
(5,0),
(6,0),
(7,0),
(8,0),
(9,0)
;
Id表示群号
State为0表示该群号未使用,1表示该群号已经使用

1.获取一个未使用的群号.
set @a:=null;
update test set state=1 where state=0 and @a:=id limit 1;
select @a;
commit;

2.随机获取一个未使用的群号.
update test a,
(
    select id from test,(select @a:=null) t where state=0 order by rand() limit 1 for update
) b
set state=1 where a.id=b.id and (@a:=b.id);
select @a;
commit;

3.获取若干群号
set @a:='';
update test as t1 set state=1 and @a:=TRIM(LEADING ',' FROM concat(@a,',',id)) where state=0 limit 3;
select @a;
commit;

这样做的好处是,修改和查询同时进行,效率很高.
缺点是大量并发的情况下,存在锁竞争.

获取若干群号的时候,遇到了一些问题
最开始的版本
set @a:='';
update test set state=1 where state=0 and (@a:=concat(@a,',',id)) limit 3;
select @a;
commit;

但是@a初始化为-1就可以
set @a:=-1;
update test set state=1 where state=0 and (@a:=concat(@a,',',id)) limit 3;
select @a;
commit;

这是因为MySQL将(@a:=concat(@a,',',id))的结果作为一个布尔判断
再看这个SQL
set @a:='';
update test set state=1 where state=0 and (@a:=concat(@a,',',id)) limit 3;
select @a;
commit;

(@a:=concat(@a,',',id))等价于 ',1'
mysql> select * from test where ',1';
Empty set, 1 warning (0.00 sec)

MySQL将',1'转为一个整形,除了0,空串表示false,其余都是true
他将逗号之前的内容转为一个整数,所以SQL事实上是这样的
update test set state=1 where state=0 and false limit 3;
所以没有任何记录被修改,并且@a不断累积.

处理这个问题有两个方式,
1.把变量赋值放在set子句
set @a:='';
update test set state=1 and (@a:=concat(@a,',',id)) where state=0 limit 3;
select @a;
commit;

2.对调id和@a的位置,但是这样返回的结构就是倒序
set @a:='';
update test set state=1 where state=0 and (@a:=concat(id,',',@a)) limit 3;
select @a;
commit;

mysql> select @a;
+--------+
| @a |
+--------+
| 3,2,1, |
+--------+
1 row in set (0.00 sec)

3.最完美的还是王工写的这个
set @a:='';
update test as t1 set state=1 and @a:=TRIM(LEADING ',' FROM concat(@a,',',id)) where state=0 limit 3;
select @a;
commit;

mysql> select @a;
+-------+
| @a |
+-------+
| 1,2,3 |
+-------+
1 row in set (0.00 sec)

ITPub博客

MySQL用表实现号池的功能