mysql数据库重复插入了一些数据,想删除保留一条,查找了很多方案,类似这种
以及这种的

初试了一下,感觉可能达到效果,但是速度太慢了,几十上百条数据还可以,几十上百万条,mysql.exe 进程基本上就跑满cpu了,半天出不来结果。大家都是程序员，就该有程序员的样子，不能完全依赖于sql语句，可以写个小工具变通下。
思路如下：
单字段重复的时候，就写个工具查询 这个字段，附带记录这个字段的主键，然后请看代码，我这边是zblog的数据库，手动插入了很多博文数据，重复了一些。

批量导入代码

vardatabase: TSQLiteDatabase;ssql: string;stab: TSQLiteTable;I: Integer;begindatabase := TSQLiteDatabase.Create(AnsiToUtf8(AppPath + 'myblog.db'));ssql := 'SELECT [bid]' + #13#10 + ' ,[blog_user]' + #13#10 + ',[blog_server]' + #13#10+ ' ,[title]' + #13#10 + ' ,[content]' + #13#10 + ' ,[blog_url]' + #13#10 + ' ,[cate]' + #13#10 + ' ,[id]' + #13#10 + ' ,[read]' + #13#10 + ' ,[pubtime]' + #13#10 + 'FROM [blog] order by blog_user,id;';stab := database.GetTable(ssql);for I := 0 to stab.RowCount - 1 dobegintrywith qry1 dobeginClose;SQL.Clear;SQL.Text := ''; // for I := 0 to 500 - 1 do//ShowMessage(UTF8Decode(stab.FieldByName['content']));SQL.Text := ('INSERT INTO `zblog`.`zbp_post`' + #13#10 + ' (`log_AuthorID`' + ',`log_Tag`' + ' ,`log_Status`' + ',`log_Type`' + ',`log_Alias`' + ' ,`log_IsTop`' + ' ,`log_IsLock`' + ',`log_Title`' + ' ,`log_Intro`' + ' ,`log_Content`' + ' ,`log_PostTime`' +',`log_CommNums`' + ',`log_ViewNums`' +' ,`log_Template`' + ',`log_Meta`)' + ' VALUES' + '(1' + ' ,0' + ' ,0' + ' ,0' + ' ,0' + ' ,0' + ' ,0'+ ', :title ' + ', :intro ' + ', :content ' +' ,' + QuotedStr(IntToStr(DaysBetween(dtp1.Date,EncodeDate(1970,1,1)) * 1440 * 60 + I)) + ' ,0' + ' ,150' + ',' + quotedstr('') + ',' + quotedstr('') + ')');ParamByName('title').AsString := (UTF8Decode(stab.FieldByName['title']));ParamByName('intro').AsString := UTF8Decode(stab.FieldByName['blog_url']);ParamByName('content').AsString := (UTF8Decode(stab.FieldByName['content'])); SQL.SaveToFile('a.txt');ExecSQL;end;exceptqry1.SQL.Clear;qry1.Close;stab.Next;Continue;end;stab.Next;end;ShowIntmessage(stab.RowCount);stab.Free;database.Free;end;

导出重复代码

思路 按重复字段排序 找到相邻记录字段内容相同的记录，然后记录下当前的主键id
，生成删除代码，到文本文件，备用下一步

procedure TForm1.btn4Click(Sender: TObject);

var
I: Integer;
sl,ssql:TStringList;
s,cID:string;
begin

with qry1 do
begin
Close;
SQL.Clear;
SQL.Text := '';
// for I := 0 to 500 - 1 do

SQL.Text:='select log_ID,log_Intro,log_Title from zbp_postorder by log_Intro';

//SQL.Add(
//'INSERT INTO zblog.zbp_post' + #13#10
//+ ' (log_AuthorID' + #13#10 + ' ,log_Tag' + #13#10 + ',log_Status'
//+ #13#10 + ',log_Type' + #13#10 + ' ,log_Alias' + #13#10 + ',log_IsTop' + #13#10 + ',log_IsLock'
//+ #13#10 + ',log_Title' + #13#10 + ',log_Intro' + #13#10 + ',log_Content'
//+ #13#10 + ',log_PostTime' + #13#10 + ' ,log_CommNums' + #13#10 + ' ,log_ViewNums' + #13#10 + ' ,log_Template' + #13#10 + ' ,log_Meta)' + #13#10 + 'VALUES' + #13#10 + '(0' +
//#13#10 + ',0' + #13#10 + ',0' + #13#10 + ',0' + #13#10 + ',0' + #13#10 + ',0' + #13#10 + ',0' + #13#10 + ',' + quotedstr(('你好')) + #13#10 + ' ,' + quotedstr('intro1') + #13#10 + ' ,' + quotedstr('conn1') + #13#10 + ',' + QuotedStr(IntToStr(random(Trunc((now - encodedate(1970, 1, 1)) 1440 60)))) + #13#10 + ',0' + #13#10 + ',0' + #13#10 + ',' + quotedstr('') + ',' + quotedstr('') + ');');
//
SQL.SaveToFile('a.txt');
//ExecSQL;
Open;
end;
cID:='';
sl:=TStringList.Create;
ssql:=TStringList.Create;
for I := 0 to qry1.RecordCount - 1 do
begin
if cID=qry1.FieldValues['log_Intro'] then
ssql.Add('delete from zbp_post where log_ID='+QuotedStr(inttostr(qry1.FieldValues['log_ID']))+';');

s:=inttostr(qry1.FieldValues['log_ID'])+','+qry1.FieldValues['log_Intro']+','+qry1.FieldValues['log_Title'];
sl.Add(s);
cID:=qry1.FieldValues['log_Intro'];
qry1.Next;
end;
ssql.SaveToFile(AppPath+'ssql.txt');
sl.SaveToFile(AppPath+'list.txt');
sl.Free;
ssql.Clear;

ShowintMessage(qry1.RecordCount);
end;

删除重复代码

运行上一步生成的删除命令即可，秒删

procedure TForm1.btn5Click(Sender: TObject);

var
I: Integer;
sl:TStringList;
begin
sl:=TStringList.Create;
sl.LoadFromFile(AppPath+'ssql.txt');

with qry1 do
begin
Close;
SQL.Clear;
SQL.Text := '';
for I := 0 to sl.Count - 1 do

begin

SQL.Text:=sl[i];

//SQL.SaveToFile('a.txt');
ExecSQL;
end;
end;

sl.Free;
end;

delphi写的，代码很渣，效率很高
15万条数据查重，删除基本上30秒内搞定

窗体代码

object Form1: TForm1
Left = 0
Top = 0
Caption = 'MySql'#21435#37325#23567#31243#24207
ClientHeight = 501
ClientWidth = 464
Color = clBtnFace
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'Tahoma'
Font.Style = []
OldCreateOrder = False
OnShow = FormShow
PixelsPerInch = 96
TextHeight = 13
object btn1: TButton
Left = 8
Top = 8
Width = 75
Height = 25
Caption = #27979#35797#28155#21152
TabOrder = 0
OnClick = btn1Click
end
object btn2: TButton
Left = 31
Top = 256
Width = 123
Height = 81
Caption = #25171#24320#30446#24405
TabOrder = 1
OnClick = btn2Click
end
object btn3: TButton
Left = 31
Top = 110
Width = 123
Height = 81
Caption = #25209#37327#23548#20837
TabOrder = 2
OnClick = btn3Click
end
object dtp1: TDateTimePicker
Left = 24
Top = 64
Width = 186
Height = 21
Date = 43636.497093726850000000
Time = 43636.497093726850000000
ImeName = #20013#25991'('#31616#20307') - '#25628#29399#25340#38899#36755#20837#27861
TabOrder = 3
end
object btn4: TButton
Left = 200
Top = 110
Width = 123
Height = 81
Caption = #23548#20986#37325#22797
TabOrder = 4
OnClick = btn4Click
end
object btn5: TButton
Left = 200
Top = 256
Width = 123
Height = 81
Caption = #21024#38500#37325#22797
TabOrder = 5
OnClick = btn5Click
end
object MySQLUniProvider1: TMySQLUniProvider
Left = 400
Top = 152
end
object con1: TUniConnection
ProviderName = 'MySQL'
Port = 3306
Database = 'zblog'
SpecificOptions.Strings = (
'MySQL.UseUnicode=True')
Username = ''
Server = '127.0.0.1'
Connected = True
LoginPrompt = False
Left = 400
Top = 88
EncryptedPassword = ''
end
object qry1: TUniQuery
Connection = con1
Left = 400
Top = 40
end
end