mysql - vb.net快速插入数据到远程mysql服务器

Question

我在 vb.net 中有一个小应用程序

我在 msaccess 中有一张表，它有大约 20,000 条记录，

现在，我想将这些记录插入到mysql的远程服务器中，

我试过如下代码，插入数据大约需要3-4个小时，

谁能告诉我更快的插入数据的方法，我会很高兴..

代码中的 dst 拥有来自 ms Access 的所有记录

For i As Integer = 0 To dst.Tables(0).Rows.Count - 1

mycmd.Connection = mycn

str = "INSERT INTO tblstudentresults(department_desc, grade, roll_no, name, course_code, course_desc, examination_type, total_marks, obtained_marks)"
/*'str = str & " VALUES('" & cname & "','" & sdr("grade").ToString() & "','" & sdr("st_code").ToString() & "','" & sdr("stName").ToString() & "','" & sdr("Subject_code").ToString() & "','" & sdr("Subject").ToString() & "','" & sdr("ExamTitle").ToString() & "','" & sdr("Maxmark").ToString() & "','" & sdr("score").ToString() & "')" -- Added non-VB comment here to improve readability */
str = str & " VALUES('" & cname & "', '" & dst.Tables(0).Rows(i)("grade").ToString() & "', '" & dst.Tables(0).Rows(i)("st_code").ToString() & "', '" & dst.Tables(0).Rows(i)("stName").ToString() & "', '', '" & dst.Tables(0).Rows(i)("Subject").ToString() & "', '" & dst.Tables(0).Rows(i)("ExamTitle").ToString() & "', '" & dst.Tables(0).Rows(i)("Maxmark").ToString() & "', '" & dst.Tables(0).Rows(i)("score").ToString() & "')"
mycmd.CommandText = str
mycmd.ExecuteNonQuery()

next

Answer 1

构造一个 multiple-row insert 可能会更快在一个大字符串(或者可能是 500 行的 block )中，然后在一次调用中运行整个插入语句，大致如下所示:

Dim firstRow as Boolean = True
str = "INSERT INTO tblstudentresults(...) VALUES"

For i As Integer = 0 To dst.Tables(0).Rows.Count - 1

    ' Only insert comma after first row, so we don't have comma at the end.
    If Not firstRow Then str = str & ","

    str = str & "('" & cname & "','" ...

    firstRow = False

Next

mycmd.Connection = mycn
mycmd.CommandText = str
mycmd.ExecuteNonQuery()