快速入门:使用 Go 语言在 Azure Database for MySQL 中进行连接并查询数据Quickstart: Use Go language to connect and query data in Azure Database for MySQL

备注

将要查看的是 Azure Database for MySQL 的新服务。You are viewing the new service of Azure Database for MySQL. 若要查看经典 MySQL Database for Azure 的文档,请访问此页To view the documentation for classic MySQL Database for Azure, please visit this page.

本快速入门演示如何在 Windows、Ubuntu Linux 和 Apple macOS 平台中使用以 Go 语言编写的代码连接到 Azure Database for MySQL。This quickstart demonstrates how to connect to an Azure Database for MySQL from Windows, Ubuntu Linux, and Apple macOS platforms by using code written in the Go language. 同时还介绍了如何使用 SQL 语句在数据库中查询、插入、更新和删除数据。It shows how to use SQL statements to query, insert, update, and delete data in the database. 本主题假设你熟悉如何使用 Go 进行开发,但不太熟悉 Azure Database for MySQL 的用法。This topic assumes that you are familiar with development using Go and that you are new to working with Azure Database for MySQL.

先决条件Prerequisites

此快速入门使用以下任意指南中创建的资源作为起点:This quickstart uses the resources created in either of these guides as a starting point:

重要

确保已使用 Azure 门户Azure CLI 将服务器的防火墙规则添加到连接的 IP 地址Ensure the IP address you're connecting from has been added the server's firewall rules using the Azure portal or Azure CLI

安装 Go 和 MySQL 连接器Install Go and MySQL connector

在自己的计算机上安装 Go适用于 MySQL 的 go-sql-driverInstall Go and the go-sql-driver for MySQL on your own computer. 根据自己的平台执行相应部分中的步骤:Depending on your platform, follow the steps in the appropriate section:

WindowsWindows

  1. 根据安装说明下载和安装用于 Microsoft Windows 的 Go。Download and install Go for Microsoft Windows according to the installation instructions.

  2. 从开始菜单启动命令提示符。Launch the command prompt from the start menu.

  3. 为项目创建一个文件夹,例如Make a folder for your project such. mkdir %USERPROFILE%\go\src\mysqlgo.mkdir %USERPROFILE%\go\src\mysqlgo.

  4. 将目录转到项目文件夹,例如 cd %USERPROFILE%\go\src\mysqlgoChange directory into the project folder, such as cd %USERPROFILE%\go\src\mysqlgo.

  5. 设置 GOPATH 的环境变量,使之指向源代码目录。Set the environment variable for GOPATH to point to the source code directory. set GOPATH=%USERPROFILE%\go.set GOPATH=%USERPROFILE%\go.

  6. 通过运行 go get github.com/go-sql-driver/mysql 命令安装用于 MySQL 的 go-sql-driverInstall the go-sql-driver for mysql by running the go get github.com/go-sql-driver/mysql command.

    总起来说就是,安装 Go,然后在命令提示符处运行以下命令:In summary, install Go, then run these commands in the command prompt:

    mkdir  %USERPROFILE%\go\src\mysqlgo
    cd %USERPROFILE%\go\src\mysqlgo
    set GOPATH=%USERPROFILE%\go
    go get github.com/go-sql-driver/mysql
    

Linux (Ubuntu)Linux (Ubuntu)

  1. 启动 Bash Shell。Launch the Bash shell.

  2. 通过运行 sudo apt-get install golang-go 来安装 Go。Install Go by running sudo apt-get install golang-go.

  3. 在主目录中为项目创建一个文件夹,例如 mkdir -p ~/go/src/mysqlgo/Make a folder for your project in your home directory, such as mkdir -p ~/go/src/mysqlgo/.

  4. 将目录转到该文件夹,例如 cd ~/go/src/mysqlgo/Change directory into the folder, such as cd ~/go/src/mysqlgo/.

  5. 设置 GOPATH 环境变量,使之指向有效的源目录,例如当前主目录的 Go 文件夹。Set the GOPATH environment variable to point to a valid source directory, such as your current home directory's go folder. 在 Bash shell 中运行 export GOPATH=~/go,将 Go 目录添加为当前 Shell 会话的 GOPATH。At the Bash shell, run export GOPATH=~/go to add the go directory as the GOPATH for the current shell session.

  6. 通过运行 go get github.com/go-sql-driver/mysql 命令安装用于 MySQL 的 go-sql-driverInstall the go-sql-driver for mysql by running the go get github.com/go-sql-driver/mysql command.

    总起来说就是,运行以下 bash 命令:In summary, run these bash commands:

    sudo apt-get install golang-go
    mkdir -p ~/go/src/mysqlgo/
    cd ~/go/src/mysqlgo/
    export GOPATH=~/go/
    go get github.com/go-sql-driver/mysql
    

Apple macOSApple macOS

  1. 下载 Go,然后根据与平台相对应的安装说明进行安装。Download and install Go according to the installation instructions matching your platform.

  2. 启动 Bash Shell。Launch the Bash shell.

  3. 在主目录中为项目创建一个文件夹,例如 mkdir -p ~/go/src/mysqlgo/Make a folder for your project in your home directory, such as mkdir -p ~/go/src/mysqlgo/.

  4. 将目录转到该文件夹,例如 cd ~/go/src/mysqlgo/Change directory into the folder, such as cd ~/go/src/mysqlgo/.

  5. 设置 GOPATH 环境变量,使之指向有效的源目录,例如当前主目录的 Go 文件夹。Set the GOPATH environment variable to point to a valid source directory, such as your current home directory's go folder. 在 Bash shell 中运行 export GOPATH=~/go,将 Go 目录添加为当前 Shell 会话的 GOPATH。At the Bash shell, run export GOPATH=~/go to add the go directory as the GOPATH for the current shell session.

  6. 通过运行 go get github.com/go-sql-driver/mysql 命令安装用于 MySQL 的 go-sql-driverInstall the go-sql-driver for mysql by running the go get github.com/go-sql-driver/mysql command.

    总起来说就是,安装 Go,然后运行以下 bash 命令:In summary, install Go, then run these bash commands:

    mkdir -p ~/go/src/mysqlgo/
    cd ~/go/src/mysqlgo/
    export GOPATH=~/go/
    go get github.com/go-sql-driver/mysql
    

获取连接信息Get connection information

获取连接到 Azure Database for MySQL 所需的连接信息。Get the connection information needed to connect to the Azure Database for MySQL. 需要完全限定的服务器名称和登录凭据。You need the fully qualified server name and login credentials.

  1. 登录到 Azure 门户Log in to the Azure portal.
  2. 在 Azure 门户的左侧菜单中,单击“所有资源”,然后搜索已创建的服务器(例如 mydemoserver)。From the left-hand menu in Azure portal, click All resources, and then search for the server you have created (such as mydemoserver).
  3. 单击服务器名称。Click the server name.
  4. 从服务器的“概览”面板中记下“服务器名称”和“服务器管理员登录名”。 From the server's Overview panel, make a note of the Server name and Server admin login name. 如果忘记了密码,也可通过此面板来重置密码。If you forget your password, you can also reset the password from this panel. Azure Database for MySQL 服务器名称Azure Database for MySQL server name

生成和运行 Go 代码Build and run Go code

  1. 若要编写 Golang 代码,可以使用简单的文本编辑器,例如 Microsoft Windows 中的记事本、Ubuntu 中的 viNano,或者 macOS 中的 TextEdit。To write Golang code, you can use a simple text editor, such as Notepad in Microsoft Windows, vi or Nano in Ubuntu, or TextEdit in macOS. 如果偏好功能更丰富的交互式开发环境 (IDE),可尝试 Jetbrains 推出的 Gogland、Microsoft 推出的 Visual Studio Code,或者 AtomIf you prefer a richer Interactive Development Environment (IDE), try Gogland by Jetbrains, Visual Studio Code by Microsoft, or Atom.
  2. 将以下部分中的 Go 代码粘贴到文本文件中,再使用文件扩展名 *.go 将这些文件保存到项目文件夹中(例如 Windows 路径 %USERPROFILE%\go\src\mysqlgo\createtable.go 或 Linux 路径 ~/go/src/mysqlgo/createtable.go)。Paste the Go code from the sections below into text files, and then save them into your project folder with file extension *.go (such as Windows path %USERPROFILE%\go\src\mysqlgo\createtable.go or Linux path ~/go/src/mysqlgo/createtable.go).
  3. 在代码中找到 HOSTDATABASEUSERPASSWORD 常量,然后将示例值替换为自己的值。Locate the HOST, DATABASE, USER, and PASSWORD constants in the code, and then replace the example values with your own values.
  4. 启动命令提示符或 Bash shell。Launch the command prompt or Bash shell. 将目录转到项目文件夹。Change directory into your project folder. 例如,在 Windows 上使用 cd %USERPROFILE%\go\src\mysqlgo\For example, on Windows cd %USERPROFILE%\go\src\mysqlgo\. 在 Linux 上使用 cd ~/go/src/mysqlgo/On Linux cd ~/go/src/mysqlgo/. 提到的某些 IDE 编辑器提供不需 shell 命令的调试和运行时功能。Some of the IDE editors mentioned offer debug and runtime capabilities without requiring shell commands.
  5. 通过键入命令 go run createtable.go 来运行代码,编译并运行应用程序。Run the code by typing the command go run createtable.go to compile the application and run it.
  6. 另外,若要将代码内建到本机应用程序中,请键入命令 go build createtable.go,再启动 createtable.exe 来运行该应用程序。Alternatively, to build the code into a native application, go build createtable.go, then launch createtable.exe to run the application.

进行连接,创建表,然后插入数据Connect, create table, and insert data

通过以下代码连接到服务器,创建一个表,然后使用 INSERT SQL 语句加载数据。Use the following code to connect to the server, create a table, and load the data by using an INSERT SQL statement.

代码导入三个包:sql 包适用于 mysql 的 go sql driver(充当与 Azure Database for MySQL 通信所需的驱动程序),以及 fmt 包(适用于在命令行中列显的输入和输出)。The code imports three packages: the sql package, the go sql driver for mysql as a driver to communicate with the Azure Database for MySQL, and the fmt package for printed input and output on the command line.

代码通过调用 sql.Open() 方法来连接到 Azure Database for MySQL,并使用 db.Ping() 方法来检查连接。The code calls method sql.Open() to connect to Azure Database for MySQL, and it checks the connection by using method db.Ping(). 将始终使用数据库句柄来存储数据库服务器的连接池。A database handle is used throughout, holding the connection pool for the database server. 代码多次调用 Exec() 方法来运行多个 DDL 命令。The code calls the Exec() method several times to run several DDL commands. 代码还使用 Prepare() 和 Exec() 来运行准备的语句,通过不同的参数插入三行。The code also uses Prepare() and Exec() to run prepared statements with different parameters to insert three rows. 每次都会使用自定义 checkError() 方法来检查是否发生错误,以及是否死机退出。Each time, a custom checkError() method is used to check if an error occurred and panic to exit.

hostdatabaseuserpassword 常量替换为自己的值。Replace the host, database, user, and password constants with your own values.

package main

import (
    "database/sql"
    "fmt"

    _ "github.com/go-sql-driver/mysql"
)

const (
    host     = "mydemoserver.mysql.database.chinacloudapi.cn"
    database = "quickstartdb"
    user     = "myadmin@mydemoserver"
    password = "yourpassword"
)

func checkError(err error) {
    if err != nil {
        panic(err)
    }
}

func main() {

    // Initialize connection string.
    var connectionString = fmt.Sprintf("%s:%s@tcp(%s:3306)/%s?allowNativePasswords=true", user, password, host, database)

    // Initialize connection object.
    db, err := sql.Open("mysql", connectionString)
    checkError(err)
    defer db.Close()

    err = db.Ping()
    checkError(err)
    fmt.Println("Successfully created connection to database.")

    // Drop previous table of same name if one exists.
    _, err = db.Exec("DROP TABLE IF EXISTS inventory;")
    checkError(err)
    fmt.Println("Finished dropping table (if existed).")

    // Create table.
    _, err = db.Exec("CREATE TABLE inventory (id serial PRIMARY KEY, name VARCHAR(50), quantity INTEGER);")
    checkError(err)
    fmt.Println("Finished creating table.")

    // Insert some data into table.
    sqlStatement, err := db.Prepare("INSERT INTO inventory (name, quantity) VALUES (?, ?);")
    res, err := sqlStatement.Exec("banana", 150)
    checkError(err)
    rowCount, err := res.RowsAffected()
    fmt.Printf("Inserted %d row(s) of data.\n", rowCount)

    res, err = sqlStatement.Exec("orange", 154)
    checkError(err)
    rowCount, err = res.RowsAffected()
    fmt.Printf("Inserted %d row(s) of data.\n", rowCount)

    res, err = sqlStatement.Exec("apple", 100)
    checkError(err)
    rowCount, err = res.RowsAffected()
    fmt.Printf("Inserted %d row(s) of data.\n", rowCount)
    fmt.Println("Done.")
}

读取数据Read data

使用以下代码进行连接,并使用 SELECT SQL 语句读取数据。Use the following code to connect and read the data by using a SELECT SQL statement.

代码导入三个包:sql 包适用于 mysql 的 go sql driver(充当与 Azure Database for MySQL 通信所需的驱动程序),以及 fmt 包(适用于在命令行中列显的输入和输出)。The code imports three packages: the sql package, the go sql driver for mysql as a driver to communicate with the Azure Database for MySQL, and the fmt package for printed input and output on the command line.

代码通过调用 sql.Open() 方法来连接到 Azure Database for MySQL,并使用 db.Ping() 方法来检查连接。The code calls method sql.Open() to connect to Azure Database for MySQL, and checks the connection using method db.Ping(). 将始终使用数据库句柄来存储数据库服务器的连接池。A database handle is used throughout, holding the connection pool for the database server. 代码调用 Query() 方法来运行 select 命令。The code calls the Query() method to run the select command. 接下来,代码运行 Next() 来循环访问结果集,再运行 Scan() 来分析列值,将值保存到变量中。Then it runs Next() to iterate through the result set and Scan() to parse the column values, saving the value into variables. 每次都会使用自定义 checkError() 方法来检查是否发生错误,以及是否死机退出。Each time a custom checkError() method is used to check if an error occurred and panic to exit.

hostdatabaseuserpassword 常量替换为自己的值。Replace the host, database, user, and password constants with your own values.

package main

import (
    "database/sql"
    "fmt"

    _ "github.com/go-sql-driver/mysql"
)

const (
    host     = "mydemoserver.mysql.database.chinacloudapi.cn"
    database = "quickstartdb"
    user     = "myadmin@mydemoserver"
    password = "yourpassword"
)

func checkError(err error) {
    if err != nil {
        panic(err)
    }
}

func main() {

    // Initialize connection string.
    var connectionString = fmt.Sprintf("%s:%s@tcp(%s:3306)/%s?allowNativePasswords=true", user, password, host, database)

    // Initialize connection object.
    db, err := sql.Open("mysql", connectionString)
    checkError(err)
    defer db.Close()

    err = db.Ping()
    checkError(err)
    fmt.Println("Successfully created connection to database.")

    // Variables for printing column data when scanned.
    var (
        id       int
        name     string
        quantity int
    )

    // Read some data from the table.
    rows, err := db.Query("SELECT id, name, quantity from inventory;")
    checkError(err)
    defer rows.Close()
    fmt.Println("Reading data:")
    for rows.Next() {
        err := rows.Scan(&id, &name, &quantity)
        checkError(err)
        fmt.Printf("Data row = (%d, %s, %d)\n", id, name, quantity)
    }
    err = rows.Err()
    checkError(err)
    fmt.Println("Done.")
}

更新数据Update data

使用以下代码进行连接,并使用 UPDATE SQL 语句更新数据。Use the following code to connect and update the data using a UPDATE SQL statement.

代码导入三个包:sql 包适用于 mysql 的 go sql driver(充当与 Azure Database for MySQL 通信所需的驱动程序),以及 fmt 包(适用于在命令行中列显的输入和输出)。The code imports three packages: the sql package, the go sql driver for mysql as a driver to communicate with the Azure Database for MySQL, and the fmt package for printed input and output on the command line.

代码通过调用 sql.Open() 方法来连接到 Azure Database for MySQL,并使用 db.Ping() 方法来检查连接。The code calls method sql.Open() to connect to Azure Database for MySQL, and checks the connection using method db.Ping(). 将始终使用数据库句柄来存储数据库服务器的连接池。A database handle is used throughout, holding the connection pool for the database server. 代码调用 Exec() 方法来运行 update 命令。The code calls the Exec() method to run the update command. 每次都会使用自定义 checkError() 方法来检查是否发生错误,以及是否死机退出。Each time a custom checkError() method is used to check if an error occurred and panic to exit.

hostdatabaseuserpassword 常量替换为自己的值。Replace the host, database, user, and password constants with your own values.

package main

import (
    "database/sql"
    "fmt"

    _ "github.com/go-sql-driver/mysql"
)

const (
    host     = "mydemoserver.mysql.database.chinacloudapi.cn"
    database = "quickstartdb"
    user     = "myadmin@mydemoserver"
    password = "yourpassword"
)

func checkError(err error) {
    if err != nil {
        panic(err)
    }
}

func main() {

    // Initialize connection string.
    var connectionString = fmt.Sprintf("%s:%s@tcp(%s:3306)/%s?allowNativePasswords=true", user, password, host, database)

    // Initialize connection object.
    db, err := sql.Open("mysql", connectionString)
    checkError(err)
    defer db.Close()

    err = db.Ping()
    checkError(err)
    fmt.Println("Successfully created connection to database.")

    // Modify some data in table.
    rows, err := db.Exec("UPDATE inventory SET quantity = ? WHERE name = ?", 200, "banana")
    checkError(err)
    rowCount, err := rows.RowsAffected()
    fmt.Printf("Updated %d row(s) of data.\n", rowCount)
    fmt.Println("Done.")
}

删除数据Delete data

使用以下代码进行连接,并使用 DELETE SQL 语句删除数据。Use the following code to connect and remove data using a DELETE SQL statement.

代码导入三个包:sql 包适用于 mysql 的 go sql driver(充当与 Azure Database for MySQL 通信所需的驱动程序),以及 fmt 包(适用于在命令行中列显的输入和输出)。The code imports three packages: the sql package, the go sql driver for mysql as a driver to communicate with the Azure Database for MySQL, and the fmt package for printed input and output on the command line.

代码通过调用 sql.Open() 方法来连接到 Azure Database for MySQL,并使用 db.Ping() 方法来检查连接。The code calls method sql.Open() to connect to Azure Database for MySQL, and checks the connection using method db.Ping(). 将始终使用数据库句柄来存储数据库服务器的连接池。A database handle is used throughout, holding the connection pool for the database server. 代码调用 Exec() 方法来运行 delete 命令。The code calls the Exec() method to run the delete command. 每次都会使用自定义 checkError() 方法来检查是否发生错误,以及是否死机退出。Each time a custom checkError() method is used to check if an error occurred and panic to exit.

hostdatabaseuserpassword 常量替换为自己的值。Replace the host, database, user, and password constants with your own values.

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
)

const (
    host     = "mydemoserver.mysql.database.chinacloudapi.cn"
    database = "quickstartdb"
    user     = "myadmin@mydemoserver"
    password = "yourpassword"
)

func checkError(err error) {
    if err != nil {
        panic(err)
    }
}

func main() {

    // Initialize connection string.
    var connectionString = fmt.Sprintf("%s:%s@tcp(%s:3306)/%s?allowNativePasswords=true", user, password, host, database)

    // Initialize connection object.
    db, err := sql.Open("mysql", connectionString)
    checkError(err)
    defer db.Close()

    err = db.Ping()
    checkError(err)
    fmt.Println("Successfully created connection to database.")

    // Modify some data in table.
    rows, err := db.Exec("DELETE FROM inventory WHERE name = ?", "orange")
    checkError(err)
    rowCount, err := rows.RowsAffected()
    fmt.Printf("Deleted %d row(s) of data.\n", rowCount)
    fmt.Println("Done.")
}

后续步骤Next steps