MySQL 数据导入器

• 下载源代码 - 126.1 KB

引言

我需要将数据从 MS SQL Server 导入到 MySQL。我首先尝试使用 MySQL Workbench，但遇到了一些问题。所以我编写了一个工具来复制表模式和数据到 MySQL。您可以在这里下载。

尝试类似下面的代码：

"<div style='background-image:url(bg.aspx?color=99ccff&value="+ cstr(

CASE [Organisation].[Level 03].CurrentMember.Name
WHEN 'Main' THEN [Measures].[Total monthly profit]/1500*100
ELSE             [Measures].[Total monthly profit]/500*100
END

)+"); background-repeat:no-repeat; width: 100px; text-align:right'>"+
format([Measures].[Unit Sales],"Standard")+"</div>"

背景

我已经针对 MS SQL Server 和 Access 数据库测试了这个工具。并非所有数据类型都支持。您可以修改应用程序以支持其他数据类型和数据库（如 Oracle）。

所需组件

如果您没有 MySQL ODBC 驱动程序或 Microsoft Access OLEDB 提供程序，您可以在这里下载它们

1. Microsoft Access 数据库引擎
2. MySQL ODBC 驱动程序 - 选择版本并选择 "No thanks, just start my download."（不用了，直接开始下载）

Using the Code

当您单击连接按钮时，您将得到 OLEDB 对话框，要求您选择提供程序和连接信息。在这里，您也可以测试连接。EditConnectionString() 函数返回的连接字符串存储在 Windows 注册表中。

    Private Sub btnConnect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
	Handles btnConnect.Click

        Dim sConnectionString As String = "Provider=SQLOLEDB.1"
        sConnectionString = EditConnectionString(sConnectionString)
        If sConnectionString = "" Then
            Exit Sub
        End If

        txtConnect.Text = sConnectionString

        Dim oAppRegistry As New AppRegistry
        oAppRegistry.SetRegistryValue("ConnectionString", sConnectionString)

        SetTableCombo()
    End Sub

EditConnectionString 函数使用 DataLinks ActiveX 对象来根据用户输入生成连接字符串。使用 COM 对象的便利性是我选择 VB.NET 来开发这个应用程序的主要原因。

    Protected Function EditConnectionString(ByVal sConnectionString As String) As String
        Try
            Dim oDataLinks As Object = CreateObject("DataLinks")
            Dim cn As Object = CreateObject("ADODB.Connection")

            cn.ConnectionString = sConnectionString
            oDataLinks.hWnd = Me.Handle

            If Not oDataLinks.PromptEdit(cn) Then
                'User pressed cancel button
                Return ""
            End If

            cn.Open()

            Return cn.ConnectionString

        Catch ex As Exception
            MsgBox(ex.Message)
            Return ""
        End Try
    End Function

一旦您输入 MySQL 服务器信息并单击“测试”，连接信息将被保存到注册表中，并且 GetMySqlConnectionString() 将创建 MySQL 连接字符串。用户将看到成功消息框或错误消息框。

    Private Sub txtTest_Click(sender As System.Object, e As System.EventArgs) Handles txtTest.Click

        Dim oAppRegistry As New AppRegistry
        oAppRegistry.SetRegistryValue("Driver", cboDriver.Text)
        oAppRegistry.SetRegistryValue("Server", txtServer.Text)
        oAppRegistry.SetRegistryValue("Database", txtDatabase.Text)
        oAppRegistry.SetRegistryValue("User", txtUser.Text)
        oAppRegistry.SetRegistryValue("Password", txtPassword.Text)

        Try
            Dim cnOdbc As OdbcConnection = New OdbcConnection(GetMySqlConnectionString())
            cnOdbc.Open()
            cnOdbc.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
            Exit Sub
        End Try

        MsgBox("Success")
    End Sub

当窗体打开时，这个 sub 将从注册表中检索连接字符串。它还会调用 SetTableCombo() 来填充可用表的列表。

    Private Sub frmExport_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Dim oAppRegistry As New AppRegistry
        txtConnect.Text = oAppRegistry.GetRegistryValue("ConnectionString")

        If txtConnect.Text <> "" Then
            SetTableCombo()
        End If

        Dim sDriver As String = oAppRegistry.GetRegistryValue("Driver")
        If sDriver = "" Then
            cboDriver.SelectedIndex = 0
        Else
            cboDriver.SelectedItem = sDriver
        End If

        txtServer.Text = oAppRegistry.GetRegistryValue("Server")
        txtDatabase.Text = oAppRegistry.GetRegistryValue("Database")
        txtUser.Text = oAppRegistry.GetRegistryValue("User")
        txtPassword.Text = oAppRegistry.GetRegistryValue("Password")
    End Sub

SetTableCombo() 用于填充可用表的列表。它使用 GetOleDbSchemaTable() 函数从 OLEDB 连接获取表列表。

    Sub SetTableCombo()
        cboTable.Items.Clear()

        Dim oTable As DataTable
        Try
            Dim cnOleDb As New OleDbConnection(txtConnect.Text)
            cnOleDb.Open()

            oTable = cnOleDb.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _
             New Object() {Nothing, Nothing, Nothing, "TABLE"})
            cnOleDb.Close()
        Catch ex As Exception
            MsgBox(Err.Description)
            Exit Sub
        End Try

        For i As Integer = 0 To oTable.Rows.Count - 1
            Dim sSchema As String = oTable.Rows(i)("TABLE_SCHEMA") & ""
            Dim sTableName As String = oTable.Rows(i)("TABLE_NAME") & ""
            cboTable.Items.Add(sTableName)
        Next

        If cboTable.Items.Count > 0 Then
            cboTable.SelectedIndex = 0
        End If

    End Sub

当您单击“复制表”按钮时，这个 sub 将检查 SetTableCombo() 是否填充了表列表，并且还会检查用户是否选择了要导出的表。接下来，它将循环遍历所选表的列表，并为每个表调用 ExportTable sub。

    Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
	Handles btnExport.Click

        If cboTable.Items.Count = 0 Then
            MsgBox("Please connect to the source database.")
            Exit Sub
        End If

        If cboTable.CheckedItems.Count = 0 Then
            MsgBox("Please select tables to copy.")
            Exit Sub
        End If

        txtLog.Clear()

        If cboTable.CheckedItems.Count > 0 Then
            Dim cnOdbc As OdbcConnection = New OdbcConnection(GetMySqlConnectionString())
            cnOdbc.Open()

            Dim cn As New OleDbConnection(txtConnect.Text)
            cn.Open()

            For i As Integer = 0 To cboTable.CheckedItems.Count - 1
                Dim sTable As String = cboTable.CheckedItems(i).ToString
                ExportTable(sTable, cn, cnOdbc)
            Next

            cnOdbc.Close()
            cn.Close()
        End If

    End Sub

ExportTable 是最重要的 sub

1. 它将首先将目标表的记录计数加载到 iMySqlRecCount 中。
2. 如果有数据并且用户已选择“插入前清空目标表”，则目标表中的所有记录都将被删除。
3. 如果用户选择了“创建目标表（如果存在则删除）”，则 sub 将删除目标表（如果存在），并将创建新的目标表。GetColumnsTableAccess 函数将用于获取 Access 的 SQL，GetColumnsTableSqlServer 将用于获取 SQL Server 的 SQL。
4. 它将首先将源表的记录计数加载到 iCount 中。
5. 最后，它将为每个源记录生成 INSERT INTO 语句，并针对目标数据库运行它。

    Private Sub ExportTable(ByVal sTableName As String, ByRef cn As OleDbConnection, _
	ByRef cnOdbc As OdbcConnection)

        Dim bMySqlTableExists As Boolean = False
        Dim iMySqlRecCount As Integer = 0

        Try
            Dim oMySqlCmd As New OdbcCommand("SELECT Count(*) FROM " & sTableName, cnOdbc)
            iMySqlRecCount = Integer.Parse(oMySqlCmd.ExecuteScalar().ToString())
            bMySqlTableExists = True
        Catch ex As Exception
            'Ignore - assume table dos not exist
        End Try

        If chkDeleteData.Checked And iMySqlRecCount > 0 Then
            Log("Deleting data from table: " & sTableName)

            OpenConnections(cn, cnOdbc)
            Dim sSql1 As String = "DELETE FROM " & sTableName
            Dim oCmd1 As New OdbcCommand(sSql1, cnOdbc)

            Try
                oCmd1.ExecuteNonQuery()
            Catch ex As Exception
                Log(ex.Message & vbTab & "SQL: " & sSql1)
            End Try
        End If

        If chkCreateTable.Checked Then

            If bMySqlTableExists Then
                Log("Drop table: " & sTableName)

                Dim oCmdDrop As New OdbcCommand("DROP TABLE " & PadQuotes(sTableName), cnOdbc)

                Try
                    oCmdDrop.ExecuteNonQuery()
                    bMySqlTableExists = False
                Catch ex As Exception
                    Log("Could not drop table: " & sTableName & ", " & ex.Message & vbTab)
                End Try

            End If

            Log("Create table: " & sTableName)
            OpenConnections(cn, cnOdbc)

            'Make create table statement
            Dim sConnectionString As String = txtConnect.Text.ToUpper()
            Dim sSql1 As String = ""

            If sConnectionString.IndexOf(".ACE.OLEDB") = -1 _
		OrElse sConnectionString.IndexOf(".JET.OLEDB") = -1 Then
                sSql1 = GetColumnsTableAccess(sTableName, cn)
            Else
                sSql1 = GetColumnsTableSqlServer(sTableName, cn)
            End If

            Dim oCmd1 As New OdbcCommand(sSql1, cnOdbc)

            Try
                oCmd1.ExecuteNonQuery()
                bMySqlTableExists = True
            Catch ex As Exception
                Log(ex.Message & vbTab & "SQL: " & sSql1)
            End Try
        End If

        Dim sSql As String = "SELECT Count(*) FROM " & PadQuotes(sTableName)
        Dim cmd As New OleDbCommand(sSql, cn)

        Dim iCount As Integer = Integer.Parse(cmd.ExecuteScalar().ToString())
        If iCount = 0 Then
            'Nothing to copy - Exit
            Exit Sub
        End If

        If bMySqlTableExists = False Then
            Log("Destination table does not exist: " & sTableName)
            Exit Sub
        End If

        'Copy Data
        ProgressBar1.Maximum = iCount
        lbCount.Visible = True

        Log("Copying " & iCount & " rows from table: " & sTableName)

        cmd = New OleDbCommand("SELECT * FROM " & sTableName, cn)

        Dim dr As OleDbDataReader = cmd.ExecuteReader()
        Dim oSchemaRows As Data.DataRowCollection = dr.GetSchemaTable.Rows

        Dim sRow As String
        Dim i As Integer
        Dim iRow As Integer = 0

        'Get Header
        Dim sHeader As String = ""
        For i = 0 To oSchemaRows.Count - 1
            Dim sColumn As String = oSchemaRows(i)("ColumnName")
            If i <> 0 Then
                sHeader += ", "
            End If
            sHeader += sColumn
        Next

        While dr.Read()
            sRow = ""
            For i = 0 To oSchemaRows.Count - 1
                If sRow <> "" Then
                    sRow += ", "
                End If

                sRow += GetValueString(dr.GetValue(i))
            Next

            OpenConnections(cn, cnOdbc)
            Dim sSql1 As String = "INSERT INTO " & sTableName _
			& " (" & sHeader & ") VALUES (" & sRow & ")"
            Dim oCmd1 As New OdbcCommand(sSql1, cnOdbc)

            Try
                oCmd1.ExecuteNonQuery()
            Catch ex As Exception
                Log(ex.Message & vbTab & "SQL: " & sSql1)
            End Try

            iRow += 1
            ProgressBar1.Value = iRow
            lbCount.Text = iRow.ToString()
            lbCount.Refresh()
        End While
        dr.Close()

        ProgressBar1.Value = 0
        lbCount.Visible = False
        lbCount.Text = ""

        Log("Finished processing " & sTableName)
    End Sub

OpenConnections sub 会定期调用，以防数据库连接关闭。

    Private Sub OpenConnections(ByRef cn As OleDbConnection, ByRef cnOdbc As OdbcConnection)

        If cn.State <> ConnectionState.Open Then
            cn.Open()
        End If

        If cnOdbc.State <> ConnectionState.Open Then
            cnOdbc.Open()
        End If

    End Sub

GetMySqlConnectionString 函数将根据用户信息创建 MySQL 连接字符串。

    Function GetMySqlConnectionString() As String
        Return "Driver={" & cboDriver.Text & "};Option=3" & _
           ";Server=" & txtServer.Text & _
           ";Port=;Database=" & txtDatabase.Text & _
           ";User=" & txtUser.Text & _
           ";Password=" & txtPassword.Text & ";"
    End Function

Log sub 将向用户显示进度日志。

    Private Sub Log(s As String)
        txtLog.Text += s & vbCrLf
        txtLog.Refresh()
    End Sub

GetValueString 函数被 ExportTable 用来生成 INSERT INTO 语句。

    Private Function GetValueString(ByVal obj As Object) As String
        If (IsDBNull(obj)) Then Return "NULL"

        Select Case obj.GetType.FullName

            Case "System.Boolean"
                If (obj = True) Then
                    Return "1"
                Else
                    Return "0"
                End If

            Case "System.String"
                Dim str As String = obj
                Return "'" + str.Replace("'", "''") + "'"

            Case "System.DateTime"
                Return "STR_TO_DATE('" + obj.ToString() + "','%m/%d/%Y %r')"

            Case "System.Drawing.Image"
                Return "NULL"

            Case "System.Drawing.Bitmap"
                Return "NULL"

            Case "System.Byte[]"
                Return "0x" + GetHexString(obj)

            Case Else
                Return obj.ToString()

        End Select
    End Function

GetHexString 函数会将二进制数据编码为十六进制 string。此函数由 GetValueString 调用。

    Private Function GetHexString(ByRef bytes() As Byte) As String
        Dim sb As New System.Text.StringBuilder
        Dim b As Byte
        Dim i As Integer = 0

        For Each b In bytes
            i += 1
            sb.Append(b.ToString("X2"))
            If i > 10 Then
                Return sb.ToString()
            End If
        Next

        Return sb.ToString()
    End Function

GetColumnsTableSqlServer 函数使用 SQL Server INFORMATION_SCHEMA.COLUMNS 系统视图来创建 CREATE TABLE 语句。

    Private Function GetColumnsTableSqlServer(ByVal sTableName As String, _
	ByRef cn As OleDbConnection) As String
        Dim sb As New System.Text.StringBuilder()

        sb.Append("CREATE TABLE " & sTableName & " (" & vbCrLf)

        Dim sSql As String = "select * from INFORMATION_SCHEMA.COLUMNS _
		where TABLE_NAME = '" & PadQuotes(sTableName) & "'"

        Dim cmd As New OleDbCommand(sSql, cn)
        Dim dr As OleDbDataReader = cmd.ExecuteReader()

        Dim i As Integer = 0
        While dr.Read
            Dim sColumn As String = dr.GetValue(dr.GetOrdinal("COLUMN_NAME")).ToString()
            Dim sDataType As String = dr.GetValue(dr.GetOrdinal("DATA_TYPE")).ToString()
            Dim bAllowDBNull As Boolean = dr.GetString(dr.GetOrdinal("IS_NULLABLE")) = "YES"
            Dim sColumnSize As String = dr.GetValue(dr.GetOrdinal_
		("CHARACTER_MAXIMUM_LENGTH")).ToString()

            'SQL Server to MySql data type converter
            Select Case LCase(sDataType)
                Case "money" : sDataType = "decimal"
                Case "ntext" : sDataType = "text"
                Case "smalldatetime" : sDataType = "datetime"
            End Select

            If sDataType = "decimal" OrElse sDataType = "numeric" Then
                Dim sPrecision As String = dr.GetValue(dr.GetOrdinal_
			("NUMERIC_PRECISION")).ToString() & ""
                Dim sScale As String = dr.GetValue(dr.GetOrdinal("NUMERIC_SCALE")).ToString() & ""
                sDataType += "(" & sPrecision & ", " & sScale & ")"

            ElseIf sDataType = "text" OrElse sDataType = "image" Then
                sColumnSize = ""
            End If

            If i > 0 Then
                sb.Append(",")
                sb.Append(vbCrLf)
            End If

            sb.Append(PadColumnName(sColumn))
            sb.Append(" " & sDataType)

            If sColumnSize <> "" Then
                sb.Append("(" & sColumnSize & ")")
            End If

            If bAllowDBNull Then
                sb.Append(" NULL")
            Else
                sb.Append(" NOT NULL")
            End If

            i += 1
        End While

        sb.Append(")")

        dr.Close()

        If i = 0 Then
            Return ""
        Else
            Return sb.ToString()
        End If

    End Function

GetColumnsTableAccess 函数使用 OleDbDataReader.GetSchemaTable.Rows 为 MS Access 创建 CREATE TABLE 语句。

    Private Function GetColumnsTableAccess(ByVal sTableName As String, _
		ByRef cn As OleDbConnection) As String

        Dim sb As New System.Text.StringBuilder()
        sb.Append("CREATE TABLE " & PadColumnName(sTableName) & " (" & vbCrLf)

        Dim sSql As String = "select * from " & PadColumnName(sTableName) & " where 0=1"

        Dim cmd As New OleDbCommand(sSql, cn)
        Dim dr As OleDbDataReader = cmd.ExecuteReader()
        Dim oSchemaRows As Data.DataRowCollection = dr.GetSchemaTable.Rows
        Dim sKeyColumns As String = ""
        Dim i As Integer = 0

        For iCol As Integer = 0 To oSchemaRows.Count - 1
            Dim sColumn As String = oSchemaRows(iCol).Item("ColumnName").ToString() & ""
            Dim sColumnSize As String = oSchemaRows(iCol).Item("ColumnSize").ToString() & ""
            Dim sDataType As String = oSchemaRows(iCol).Item("DATATYPE").FullName.ToString()
            Dim bAllowDBNull As Boolean = oSchemaRows(iCol).Item("AllowDBNull") 'Does not always work

            If i > 0 Then
                sb.Append(",")
                sb.Append(vbCrLf)
            End If

            sb.Append(PadColumnName(sColumn))
            sb.Append(" " & PadAccessDataType(sDataType, sColumnSize))

            If bAllowDBNull Then
                sb.Append(" NULL")
            Else
                sb.Append(" NOT NULL")
            End If

            i += 1
        Next

        sb.Append(")")

        dr.Close()

        If i = 0 Then
            Return ""
        Else
            Return sb.ToString()
        End If

    End Function

PadAccessDataType 函数创建 Access vs. MySQL Datatype 的映射。

    Private Function PadAccessDataType_
        (ByVal sDataType As String, ByVal sColumnSize As String) As String

        sDataType = Replace(sDataType, "System.", "")

        Select Case LCase(sDataType)
            Case "string" : sDataType = "VARCHAR"
            Case "int16" : sDataType = "SMALLINT"
            Case "int32" : sDataType = "INT"
            Case "int64" : sDataType = "BIGINT"
        End Select

        If sColumnSize <> "" Then
            Return sDataType & "(" & sColumnSize & ")"
        Else
            Return sDataType
        End If

    End Function

PadQuotes 辅助函数将单引号替换为双引号。

    Public Function PadQuotes(ByVal s As String) As String
        If s = "" Then
            Return ""
        End If
        Return (s & "").Replace("'", "''")
    End Function

PadColumnName 辅助函数将 string 括在 MySQL 引号中。

    Public Function PadColumnName(ByVal sTable As String) As String
        Return "`" & sTable & "`"
    End Function

以下是 AppRegistry.vb 模块的代码。

Imports Microsoft.Win32

Public Class AppRegistry

    Dim sRegKey As String = "SOFTWARE\Krupitsky Labs\CopyTable"
    Dim sRegKey64 As String = "SOFTWARE\Wow6432Node\Krupitsky Labs\CopyTable"

SetRegistryValue sub 将一个值保存到 Windows 注册表中。

    Public Sub SetRegistryValue(ByVal sKey As String, ByVal sValue As String)
        'Update registry
        Try
            Dim oKey As RegistryKey = Registry.LocalMachine.OpenSubKey(sRegKey, True)
            If oKey Is Nothing Then
                oKey = Registry.LocalMachine.CreateSubKey(sRegKey)
            End If

            If Not oKey Is Nothing Then
                oKey.SetValue(sKey, sValue)
            End If

        Catch ex As Exception
            MsgBox("Update registry failed: " & ex.Message, , "DeleteBlankPages")
        End Try
    End Sub

GetRegValue 从 Windows 注册表中获取值。

    Private Function GetRegValue(ByVal sFolder As String, ByVal sKey As String) As String
        Dim oKey As RegistryKey = Registry.LocalMachine.OpenSubKey(sFolder, False)
        Dim sValue As String = ""

        If Not oKey Is Nothing Then
            sValue = oKey.GetValue(sKey)
            oKey.Close()
        End If

        Return sValue
    End Function

End Class 

我希望有人会发现这个有用。

历史

• 2016 年 5 月 4 日：初始版本