自己动手丰衣足食,夜谈MySQL数据库去除重复记录最快的方法
mysql数据库重复插入了一些数据,想删除保留一条,查找了很多方案,类似这种
以及这种的
初试了一下,感觉可能达到效果,但是速度太慢了,几十上百条数据还可以,几十上百万条,mysql.exe 进程基本上就跑满cpu了,半天出不来结果。大家都是程序员，就该有程序员的样子，不能完全依赖于sql语句，可以写个小工具变通下。
思路如下：
单字段重复的时候，就写个工具查询 这个字段，附带记录这个字段的主键，然后请看代码，我这边是zblog的数据库，手动插入了很多博文数据，重复了一些。
批量导入代码
var
database: TSQLiteDatabase;
ssql: string;
stab: TSQLiteTable;
I: Integer;
begin
database := 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 do
begin
try
with qry1 do
begin
Close;
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;
except
qry1.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_post order 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