想做一个通用的删除删复记录存储过程:方法:删除重复记录,保留lIndex最小那条 --- MIN(lIndex)DELETE FROM event_log WHERE lIndex NOT IN ( SELECT temp.mid FROM ( SELECT MIN(lIndex) as mid FROM event_log GROUP BY lEventTime,nEventCode,ucPowerStage,ucDevFrom,ucConvFrom) AS temp)
想把上面的写成通用的,执行简单修改变量即可,但还是我写成下面这样的报错,有谁能会修改呢?
SET @tb_name = ‘myLog’;
SET @index_name = ‘lIndex’;
SET @groupby = ‘lTime,nErrorCode,ucStage,ucDevID,ucConvID’;DELETE FROM @tb_name WHERE @index_name NOT IN ( SELECT temp.mid FROM ( SELECT MIN(@index_name) as mid FROM @tb_name GROUP BY @groupby) AS temp)
表结构:CREATE TABLE `myLog` (
`lIndex` int(11) NOT NULL AUTO_INCREMENT,
`lWindFieldID` int(11) NOT NULL DEFAULT '0',
`lTime` int(11) NOT NULL DEFAULT '0',
`nErrorCode` smallint(6) NOT NULL DEFAULT '0',
`ucStage` smallint(6) NOT NULL DEFAULT '0',
`ucDevID` tinyint(3) NOT NULL DEFAULT '0',
`ucConvID` smallint(6) NOT NULL DEFAULT '0',
`ucConvNo` smallint(6) NOT NULL DEFAULT '0',
PRIMARY KEY (`lIndex`),
UNIQUE KEY `unio` (`lTime`,`ucConvID`,`nErrorCode`,`ucDevID`,`ucStage`,`ucConvNo`)
) ENGINE=MyISAM AUTO_INCREMENT=10929 DEFAULT CHARSET=utf8;
PREPARE stmt1 FROM "DELETE FROM ? WHERE @index_name NOT IN ( SELECT temp.mid FROM ( SELECT MIN(?) AS MID FROM ? GROUP BY @groupby) AS temp)" ;
SET @tb_name = 'myLog';
SET @index_name = 'lIndex';
SET @groupby = 'lTime,nErrorCode,ucStage,ucDevID,ucConvID';
EXECUTE stmt1 USING @tb_name, @index_name, @groupby;
SET @index_name = 'lIndex';
SET @groupby = 'lTime,nErrorCode,ucStage,ucDevID,ucConvID';
EXECUTE stmt1 USING @tb_name, @index_name,@index_name, @groupby;
SET @index_name = 'lIndex';
SET @groupby = 'lTime,nErrorCode,ucStage,ucDevID,ucConvID';
EXECUTE stmt1 USING @tb_name, @index_name,@index_name, @groupby;
额,的确有点问题,上边的问号,就是需要传入参数的地方。
中间所有的SET 都是定义的参数具体内容。
最下所有的参数,按照顺序对应第一行的问号。问号的数量应该要和最下的参数,数量一致。