MySQL随机选取资源

随机选取一个资源
模拟表如下:

1. create table room_info(  
2.     RoomID bigint not null auto_increment comment '房间ID',  
3.     State smallint not null default 1 comment '状态.1表示空闲,2表示被占用',  
4.     primary key(RoomID)  
5. ) ;  
6.   
7. INSERT INTO `room_info` (`RoomID`,`State`) VALUES (1,1);  
8. INSERT INTO `room_info` (`RoomID`,`State`) VALUES (2,1);  
9. INSERT INTO `room_info` (`RoomID`,`State`) VALUES (3,1);  
10. INSERT INTO `room_info` (`RoomID`,`State`) VALUES (4,1);  
11. INSERT INTO `room_info` (`RoomID`,`State`) VALUES (5,1);  
12. INSERT INTO `room_info` (`RoomID`,`State`) VALUES (6,1);  
13. INSERT INTO `room_info` (`RoomID`,`State`) VALUES (7,1);  
14. INSERT INTO `room_info` (`RoomID`,`State`) VALUES (8,1);  
15. INSERT INTO `room_info` (`RoomID`,`State`) VALUES (9,1);  
16. INSERT INTO `room_info` (`RoomID`,`State`) VALUES (10,1);  
17. INSERT INTO `room_info` (`RoomID`,`State`) VALUES (11,1);  
18. INSERT INTO `room_info` (`RoomID`,`State`) VALUES (12,1);  
19. INSERT INTO `room_info` (`RoomID`,`State`) VALUES (13,1);  
20. INSERT INTO `room_info` (`RoomID`,`State`) VALUES (14,1);  
21. INSERT INTO `room_info` (`RoomID`,`State`) VALUES (15,1);  
22. INSERT INTO `room_info` (`RoomID`,`State`) VALUES (16,1);  
23. INSERT INTO `room_info` (`RoomID`,`State`) VALUES (17,1);  
24. INSERT INTO `room_info` (`RoomID`,`State`) VALUES (18,1);  
25. INSERT INTO `room_info` (`RoomID`,`State`) VALUES (19,1);  
26. INSERT INTO `room_info` (`RoomID`,`State`) VALUES (20,1);  

从资源表中随机选取一个资源,并且修改状态.
资源表的记录不多,大致1w行左右.

创建普通索引
create index inx_1 on room_info(roomid,state);

这个索引非常重要
如果没有这个索引,可能会锁多行.

随机获取一行,并且修改资源状态.

1. set autocommit=false;  
2. update room_info set state=2 where RoomID=(  
3.     select * from (  
4.         select RoomID from room_info where state=1 order by rand() limit 1  
5.     ) a  
6. ) and state=1 and @roomid:=roomid;  
7. select @roomid;  
8. commit;  

需要注意的是,在执行下面SQL的时候,没有上锁.
select RoomID from room_info where state=1 order by rand() limit 1
所以在多线程环境下,可能冲突.
所以需要判断 update 的影响行数.如果影响行数为0,说明资源已经被别人锁定.自己需要重新获取.


如果影响行数为0,此时切记不能拿到@roomid直接使用,而是需要重新运行.
分享文章：MySQL随机选取资源
文章位置：http://cqcxhl.cn/article/gooipd.html