MySQL用表實現號池的功能

壹頁書發表於2015-01-28
MySql
最近單位做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部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-1418584/，如需轉載，請註明出處，否則將追究法律責任。

相關文章