作者SmallBeeWayn (喵喵叫的蜜蜂猫)
看板Database
标题[SQL ] 合并查询会锁表? JOIN的问题?
时间Sun Feb 8 15:24:18 2009
事情如下, 我将原本这样的执行顺序(PHP+MySQL)
$res_vote_times = mysql_query("
SELECT message FROM system_message
WHERE room_no = $room_no and date = $date and type = 'VOTE_TIMES'");
$vote_times = mysql_result($res_vote_times,0,0);
mysql_query("
CREATE TEMPORARY TABLE tmp_sd SELECT uname FROM vote
WHERE room_no = $room_no AND date = $date AND situation = 'VOTE_KILL'
AND vote_times = $vote_times");
$res_novote = mysql_query("
SELECT user_entry.uname,user_entry.handle_name FROM user_entry
LEFT JOIN tmp_sd ON user_entry.uname = tmp_sd.uname
WHERE user_entry.room_no = $room_no and user_entry.live = 'live'
AND user_entry.user_no > 0 AND tmp_sd.uname is NULL");
改成这样的叙述
$res_novote=mysql_query("
SELECT user_entry.uname,user_entry.handle_name
FROM user_entry,system_message
LEFT JOIN vote ON user_entry.uname=vote.uname AND vote.room_no = $room_no
AND vote.date = $date AND vote.situation = 'VOTE_KILL'
AND vote.vote_times = system_message.message
WHERE user_entry.room_no = $room_no AND user_entry.live = 'live'
AND user_entry.user_no > 0 AND vote.uname is NULL
AND system_message.room_no = $room_no
AND system_message.date = $date AND system_message.type = 'VOTE_TIMES'");
被说"会有机率导致锁表"而不采用
请问这到底是怎麽回事....
网路上查询除了建议把加入system_message表的部份写成INNER JOIN以外
大概可能扯上关系的就是
http://blog.csdn.net/Areyan/archive/2006/05/27/757456.aspx
没有提到锁表之类的问题，想知道采用前者而不采用後者的理由？
--
※ 发信站: 批踢踢实业坊(ptt.cc)
◆ From: 122.116.180.163