把很多TXT文件内容写入MYSQL数据库，有一亿条数据，如何提高写入效率？我的代码是一条一条插入的，半个小时了才插入几百万，求高手赐教修改如下代码

yuerxiao1990 2017-08-31 09:36:04

Private Function F_WritetoDatabase(ByRef myArr As ArrayList) As Boolean
Dim connection As MySqlConnection
Dim connectionString As String
Dim strInsert As String
Dim hasstr As String
Dim myhash As Hashtable = New Hashtable()
' Dim myIndex As Integer
Dim remyArr As String
myhash.Clear()
For i = 0 To myArr(0).ToString.Length - 1
remyArr = myArr(0)(i).ToString
' If (Asc(remyArr) < 0) Or ((65 <= Asc(remyArr)) And (Asc(remyArr) <= 90)) Or ((97 <= Asc(remyArr)) And (Asc(remyArr) <= 122)) Or (Asc(remyArr) = 42) Then
'remyArr = remyArr.Replace(remyArr, """" & remyArr & """")
myArr(0)(i) = """" & remyArr & """"
' myIndex = myArr(i).ToString().Trim().IndexOf(",")

' End If
myhash.Add(i, myArr(0)(i).ToString())
Next

connectionString = "Database=schema;Data Source=localhost;User Id=root;Password=1234;"
connection = New MySqlConnection(connectionString)

connection.Open()

If (connection.State = ConnectionState.Open) Then
' MsgBox("稻香")

End If

hasstr = myhash(0).ToString().Trim() & "," & myhash(1).ToString().Trim() & "," & myhash(2).ToString().Trim() & "," & myhash(3).ToString().Trim() & "," & myhash(4).ToString().Trim() &
"," & myhash(5).ToString().Trim() & "," & myhash(6).ToString().Trim() & "," & myhash(7).ToString().Trim() & "," & myhash(8).ToString().Trim() & "," & myhash(9).ToString().Trim() &
"," & myhash(10).ToString().Trim() & "," & myhash(11).ToString().Trim() & "," & myhash(12).ToString().Trim() & "," & myhash(13).ToString().Trim() & "," & myhash(14).ToString().Trim()

strInsert = "insert into share(time,stockcode,name,closingprice,highestprice,lowestprice,openingprice," &
"beforeclosingprice,riseandfalllines,riseandfallamplitude,turnoverrate,volume,turnover,totalmarketvalue,currentmarket) " &
"values(" & hasstr & ")"
Dim cmdInsert As MySqlCommand = New MySqlCommand(strInsert, connection)

Try
cmdInsert.ExecuteNonQuery() '执行具体的插入查询动作
Catch ex As ApplicationException
ex.Message.ToString()
connection.Close()
End Try

connection.Close()

' MsgBox("全部完成，共写入数据库" & myhash.Count - 1 & "条！")

Return True

End Function

...全文