Shulou(Shulou.com)06/01 Report--

Mysql database repeatedly inserted some data, want to delete and retain one, look for a lot of solutions, such as this

And this kind of

After trying it for the first time, it seems that the effect may be achieved, but the speed is too slow, dozens of hundreds of pieces of data are OK, tens of millions of pieces of data, the mysql.exe process is basically full of cpu, and there are no results for a long time. We are all programmers, so we should look like programmers. We can't rely entirely on sql statements. We can write a gadget to modify it.

The ideas are as follows:

When a single field is repeated, write a tool to query the field, along with the primary key of the field, and then look at the code. My side is the database of zblog, which manually inserts a lot of blog data and repeats some.

Bulk import code

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`'+', `Status`'+', `log_ type`'+', `log_ log_'+', `log_ log_'+', `log_ title'+', `log_ Intro`'+' `Content`'+', `PostTime`'+', `log_ CommNums`'+', `log_ ViewNums`'+', 'log_ template`' +', `log_ Meta`)'+ 'VALUES' +' (1'+', 0' +', 0' +', 0' +', 0' +', 0' +' : title'+',: intro'+',: content'+','+ QuotedStr (IntToStr (DaysBetween (dtp1.Date,EncodeDate (1970) * 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

Export duplicate code

The idea is to sort by repeating fields to find records with the same content in adjacent record fields, and then record the current primary key id

, generate the deletion code, go to the text file, and replace the next step

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 ((Hello)) + # 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

Delete duplicate code

Just run the delete command generated in the previous step, and delete it in seconds.

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

Written by delphi, the code is very bad and efficient.

150000 pieces of data are checked and deleted basically in 30 seconds.

Form code

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.