mysql去重操作哪个最快_自己动手丰衣足食,夜谈MySQL数据库去除重复记录最快的方法...

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

647d2f9ccf7d6263390c17376d4efb1e.png

以及这种的

4a9b3c803e9df06398c424e14241eaa9.png

初试了一下,感觉可能达到效果,但是速度太慢了,几十上百条数据还可以,几十上百万条,mysql.exe 进程基本上就跑满cpu了,半天出不来结果。大家都是程序员,就该有程序员的样子,不能完全依赖于sql语句,可以写个小工具变通下。

思路如下:

单字段重复的时候,就写个工具查询 这个字段,附带记录这个字段的主键,然后请看代码,我这边是zblog的数据库,手动插入了很多博文数据,重复了一些。

e2962bcf486ca7a0a8ee4a4e0096b2a7.png

批量导入代码

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

  • 0
    点赞
  • 0
    收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Asmolia

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值