与您分享电脑世界

使用以下语句删除重复记录时候出现错误提示，据说mssql和oracle不会出现此问题。

delete from table where (countid) in (

 select min(countid) as countid from table where year=2017 group by objname,objid,year having count(1) > 1

)

提示#1093 – You can’t specify target table ‘X’ for update in FROM clause

大概意思是你不能select出来后同时又更新

解决方案 要不就create table tmp 创建个临时表，要不就再套个select

delete from table where (countid) in (
 select a.countid from
 (
 select min(countid) as countid from table where year=2017 group by objname,objid,year having count(1) > 1
 )a
)

大量sleep产生的原因从三点排查：
1.程序中，不使用持久链接，即使用mysql_connect而不是pconnect
2. 程序执行完毕，应该显式调用mysql_close
3. 数据库查询不够优化，过度耗时

<?php
define('MAX_SLEEP_TIME', 1000);

$hostname = "ip:port";
$username = "username";
$password = "password";

$connect = mysql_connect($hostname, $username, $password);
$result = mysql_query("SHOW PROCESSLIST", $connect);
while ($proc = mysql_fetch_assoc($result)) {
 if ($proc["Command"] == "Sleep" && $proc["Time"] > MAX_SLEEP_TIME) {
 @mysql_query("KILL " . $proc["Id"], $connect);
 }
}
mysql_close($connect);
?>