最近单位做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;
![](//img.blog.itpub.net/blog/attachment/201501/28/29254281_14224401143JOo.png?x-oss-process=style/bb)
但是@a初始化为-1就可以
set @a:=-1;
update test set state=1 where state=0 and (@a:=concat(@a,',',id)) limit 3;
select @a;
commit;
![](//img.blog.itpub.net/blog/attachment/201501/28/29254281_1422440269Nm72.png?x-oss-process=style/bb)
这是因为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)
其中一个功能是新建群,获取群号.
群号是我们运营定义的。好的号码要留着卖吧.
模拟群号的号池.
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;
![](http://img.blog.itpub.net/blog/attachment/201501/28/29254281_14224401143JOo.png?x-oss-process=style/bb)
但是@a初始化为-1就可以
set @a:=-1;
update test set state=1 where state=0 and (@a:=concat(@a,',',id)) limit 3;
select @a;
commit;
![](http://img.blog.itpub.net/blog/attachment/201501/28/29254281_1422440269Nm72.png?x-oss-process=style/bb)
这是因为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博客 ” ,链接:http://blog.itpub.net/29254281/viewspace-1418584/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29254281/viewspace-1418584/