Mark

IGNORE is a MySQL extension to standard SQL. It controls how ALTER TABLE works if there are duplicates on unique keys in the new table or if warnings occur when strict mode is enabled. If IGNORE is not specified, the copy is aborted and rolled back if duplicate-key errors occur. If IGNORE is specified, only the first row is used of rows with duplicates on a unique key, The other conflicting rows are deleted. Incorrect values are truncated to the closest matching acceptable value.

root@test 01:57:29>alter ignore table tmp_abc add unique key uk_b(b);

Query OK, 5 rows affected (0.65 sec)

Records: 5  Duplicates: 3  Warnings: 0

----BUT---- WHY --

alter ignore table table_bbb add unique key uk_tcm_sellerid(seller_id,service_date);

Error 1062: Duplicate entry '10688998-2011-04-11 00:00:00' for key 'uk_tcm_sellerid'

[2011-04-14 03:00:00] SQL执行失败！

--- which variables?

##########

mysql删除表中重复记录的sql，只能搞个临时表了

create table tmp_xd_del_id as

select id from tt_stats a

WHERE a.id != (SELECT MAX(id)

FROM tt_stats b

WHERE a.seller_id = b.seller_id

AND a.service_date = b.service_date);

delete from tt_stats where id in(select id from tmp_xd_del_id);

-- one sql

delete a from table_name a,
(select *, min(id) as bid
from table_name
group by index_value, member_id
having count(1) > 1) as b
where a.index_value = b.index_value
and a.member_id = b.member_id
and a.id > b.bid;