Query was this:
CREATE TABLE `query` (
`id` int(11) NOT NULL auto_increment,
`searchquery` 开发者_开发知识库varchar(255) NOT NULL default '',
`datetime` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM
first I want to drop the table with:
ALTER TABLE `querynew` DROP `id`
and then delete the double entries.. I tried it with:
INSERT INTO `querynew` SELECT DISTINCT * FROM `query`
but with no success.. :(
and with ALTER TABLE query ADD UNIQUE ( searchquery )
- is it possible to save the queries only one time?
I would use MySQL's multi-table delete syntax:
DELETE q2 FROM query q1 JOIN query q2 USING (searchquery, datetime)
WHERE q1.id < q2.id;
I would do this using an index with the MySQL-specific IGNORE
keyword. This kills two birds with one stone: it deletes duplicate rows, and adds a unique index so that you will not get any more of them. It is usually faster than the other methods as well:
alter ignore table query add unique index(searchquery, datetime);
You should be able to do it without first removing the column:
DELETE FROM `query`
WHERE `id` IN (
SELECT `id`
FROM `query` q
WHERE EXISTS ( -- Any matching rows with a lower id?
SELECT *
FROM `query`
WHERE `searchquery` = q.`searchquery`
AND `datetime` = q.`datetime`
AND `id` < q.`id`
)
);
You could also go via a temp table:
SELECT MIN(`id`), `searchquery`, `datetime`
INTO `temp_query`
GROUP BY `searchquery`, `datetime`;
DELETE FROM `query`;
INSERT INTO `query` SELECT * FROM `temp_query`;
精彩评论