[SQL ] 合併查詢會鎖表? JOIN的問題?
事情如下, 我將原本這樣的執行順序(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