MySQL随机选取资源
发布时间：2022-04-04 11:05:41 所属栏目：MySql教程 来源：互联网
导读：随机选取一个资源 模拟表如下: create table room_info( RoomID bigint not null auto_increment comment 房间ID, State smallint not null default 1 comment 状态.1表示空闲,2表示被占用, primary key(RoomID) ) ; INSERT INTO `room_info` (`RoomID`,`S
|
随机选取一个资源 模拟表如下: create table room_info( RoomID bigint not null auto_increment comment '房间ID', State smallint not null default 1 comment '状态.1表示空闲,2表示被占用', primary key(RoomID) ) ; INSERT INTO `room_info` (`RoomID`,`State`) VALUES (1,1); INSERT INTO `room_info` (`RoomID`,`State`) VALUES (2,1); INSERT INTO `room_info` (`RoomID`,`State`) VALUES (3,1); INSERT INTO `room_info` (`RoomID`,`State`) VALUES (4,1); INSERT INTO `room_info` (`RoomID`,`State`) VALUES (5,1); INSERT INTO `room_info` (`RoomID`,`State`) VALUES (6,1); INSERT INTO `room_info` (`RoomID`,`State`) VALUES (7,1); INSERT INTO `room_info` (`RoomID`,`State`) VALUES (8,1); INSERT INTO `room_info` (`RoomID`,`State`) VALUES (9,1); INSERT INTO `room_info` (`RoomID`,`State`) VALUES (10,1); INSERT INTO `room_info` (`RoomID`,`State`) VALUES (11,1); INSERT INTO `room_info` (`RoomID`,`State`) VALUES (12,1); INSERT INTO `room_info` (`RoomID`,`State`) VALUES (13,1); INSERT INTO `room_info` (`RoomID`,`State`) VALUES (14,1); INSERT INTO `room_info` (`RoomID`,`State`) VALUES (15,1); INSERT INTO `room_info` (`RoomID`,`State`) VALUES (16,1); INSERT INTO `room_info` (`RoomID`,`State`) VALUES (17,1); INSERT INTO `room_info` (`RoomID`,`State`) VALUES (18,1); INSERT INTO `room_info` (`RoomID`,`State`) VALUES (19,1); INSERT INTO `room_info` (`RoomID`,`State`) VALUES (20,1); 从资源表中随机选取一个资源,并且修改状态. 资源表的记录不多,大致1w行左右. 创建普通索引 create index inx_1 on room_info(roomid,state); 这个索引非常重要 如果没有这个索引,可能会锁多行. 随机获取一行,并且修改资源状态. set autocommit=false; update room_info set state=2 where RoomID=( select * from ( select RoomID from room_info where state=1 order by rand() limit 1 ) a ) and state=1 and @roomid:=roomid; select @roomid; commit; 需要注意的是,在执行下面SQL的时候,没有上锁. select RoomID from room_info where state=1 order by rand() limit 1 所以在多线程环境下,可能冲突. 所以需要判断 update 的影响行数.如果影响行数为0,说明资源已经被别人锁定.自己需要重新获取. 如果影响行数为0,此时切记不能拿到@roomid直接使用,而是需要重新运行. （编辑：大连站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐