您现在访问的是微软AZURE全球版技术文档网站,若需要访问由世纪互联运营的MICROSOFT AZURE中国区技术文档网站,请访问 https://docs.azure.cn.

Azure Database for MySQL:使用 Ruby 连接和查询数据Azure Database for MySQL: Use Ruby to connect and query data

本快速入门演示如何在 Windows、Ubuntu Linux 和 Mac 平台中使用 Ruby 应用程序和 mysql2 gem 连接到适用于 MySQL 的 Azure 数据库。This quickstart demonstrates how to connect to an Azure Database for MySQL using a Ruby application and the mysql2 gem from Windows, Ubuntu Linux, and Mac platforms. 同时还介绍了如何使用 SQL 语句在数据库中查询、插入、更新和删除数据。It shows how to use SQL statements to query, insert, update, and delete data in the database. 本主题假设你熟悉如何使用 Ruby 进行开发,但不太熟悉 Azure Database for MySQL 的用法。This topic assumes that you are familiar with development using Ruby 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

安装 RubyInstall Ruby

在自己的计算机上安装 Ruby、Gem 和 MySQL2 库。Install Ruby, Gem, and the MySQL2 library on your own computer.

WindowsWindows

  1. 下载并安装 Ruby 版本 2.3。Download and Install the 2.3 version of Ruby.
  2. 从“开始”菜单启动新的命令提示符 (cmd)。Launch a new command prompt (cmd) from the Start menu.
  3. 将目录切换到 Ruby 版本 2.3 所在的目录。Change directory into the Ruby directory for version 2.3. cd c:\Ruby23-x64\bin
  4. 运行 ruby -v 命令查看所安装的版本,以测试 Ruby 安装。Test the Ruby installation by running the command ruby -v to see the version installed.
  5. 运行 gem -v 命令查看所安装的版本,以测试 Gem 安装。Test the Gem installation by running the command gem -v to see the version installed.
  6. 运行命令 gem install mysql2,使用 Gem 生成适用于 Ruby 的 Mysql2 模块。Build the Mysql2 module for Ruby using Gem by running the command gem install mysql2.

MacOSMacOS

  1. 运行命令 brew install ruby,使用 Homebrew 安装 Ruby。Install Ruby using Homebrew by running the command brew install ruby. 如需更多安装选项,请参阅 Ruby 安装文档For more installation options, see the Ruby installation documentation.
  2. 运行 ruby -v 命令查看所安装的版本,以测试 Ruby 安装。Test the Ruby installation by running the command ruby -v to see the version installed.
  3. 运行 gem -v 命令查看所安装的版本,以测试 Gem 安装。Test the Gem installation by running the command gem -v to see the version installed.
  4. 运行命令 gem install mysql2,使用 Gem 生成适用于 Ruby 的 Mysql2 模块。Build the Mysql2 module for Ruby using Gem by running the command gem install mysql2.

Linux (Ubuntu)Linux (Ubuntu)

  1. 通过运行命令 sudo apt-get install ruby-full 安装 Ruby。Install Ruby by running the command sudo apt-get install ruby-full. 如需更多安装选项,请参阅 Ruby 安装文档For more installation options, see the Ruby installation documentation.
  2. 运行 ruby -v 命令查看所安装的版本,以测试 Ruby 安装。Test the Ruby installation by running the command ruby -v to see the version installed.
  3. 通过运行命令 sudo gem update --system 安装 Gem 的最新更新。Install the latest updates for Gem by running the command sudo gem update --system.
  4. 运行 gem -v 命令查看所安装的版本,以测试 Gem 安装。Test the Gem installation by running the command gem -v to see the version installed.
  5. 通过运行命令 sudo apt-get install build-essential 安装 gcc、make 和其他生成工具。Install the gcc, make, and other build tools by running the command sudo apt-get install build-essential.
  6. 运行 sudo apt-get install libmysqlclient-dev 命令安装 MySQL 客户端开发人员库。Install the MySQL client developer libraries by running the command sudo apt-get install libmysqlclient-dev.
  7. 运行命令 sudo gem install mysql2,使用 Gem 生成适用于 Ruby 的 mysql2 模块。Build the mysql2 module for Ruby using Gem by running the command sudo gem install mysql2.

获取连接信息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

运行 Ruby 代码Run Ruby code

  1. 将以下部分中的 Ruby 代码粘贴到文本文件,然后使用文件扩展名 .rb 将这些文件保存到项目文件夹中(例如 C:\rubymysql\createtable.rb/home/username/rubymysql/createtable.rb)。Paste the Ruby code from the sections below into text files, and then save the files into a project folder with file extension .rb (such as C:\rubymysql\createtable.rb or /home/username/rubymysql/createtable.rb).
  2. 若要运行此代码,请启动命令提示符或 Bash shell。To run the code, launch the command prompt or Bash shell. 将目录切换到项目文件夹 cd rubymysqlChange directory into your project folder cd rubymysql
  3. 然后键入 Ruby 命令并后接文件名(例如 ruby createtable.rb)以运行应用程序。Then type the Ruby command followed by the file name, such as ruby createtable.rb to run the application.
  4. 在 Windows OS 上,如果 Ruby 应用程序不在路径环境变量中,则可能需要使用完整路径来启动 Node 应用程序,例如 "c:\Ruby23-x64\bin\ruby.exe" createtable.rbOn the Windows OS, if the Ruby application is not in your path environment variable, you may need to use the full path to launch the node application, such as "c:\Ruby23-x64\bin\ruby.exe" createtable.rb

进行连接并创建表Connect and create a table

使用以下代码进行连接,使用 CREATE TABLE SQL 语句创建表,然后使用 INSERT INTO SQL 语句将行添加到表中。Use the following code to connect and create a table by using CREATE TABLE SQL statement, followed by INSERT INTO SQL statements to add rows into the table.

该代码使用 mysql2::client 类的 .new() 方法连接到适用于 MySQL 的 Azure 数据库。The code uses a mysql2::client class .new() method to connect to Azure Database for MySQL. 然后,该代码调用 query() 方法多次,以运行 DROP、CREATE TABLE 和 INSERT INTO 命令。Then it calls method query() several times to run the DROP, CREATE TABLE, and INSERT INTO commands. 然后,它会调用方法 close(),在终止之前关闭连接。Then it calls method close() to close the connection before terminating.

hostdatabaseusernamepassword 字符串替换为你自己的值。Replace the host, database, username, and password strings with your own values.

require 'mysql2'

begin
    # Initialize connection variables.
    host = String('mydemoserver.mysql.database.azure.com')
    database = String('quickstartdb')
    username = String('myadmin@mydemoserver')
    password = String('yourpassword')

    # Initialize connection object.
    client = Mysql2::Client.new(:host => host, :username => username, :database => database, :password => password)
    puts 'Successfully created connection to database.'

    # Drop previous table of same name if one exists
    client.query('DROP TABLE IF EXISTS inventory;')
    puts 'Finished dropping table (if existed).'

    # Drop previous table of same name if one exists.
    client.query('CREATE TABLE inventory (id serial PRIMARY KEY, name VARCHAR(50), quantity INTEGER);')
    puts 'Finished creating table.'

    # Insert some data into table.
    client.query("INSERT INTO inventory VALUES(1, 'banana', 150)")
    client.query("INSERT INTO inventory VALUES(2, 'orange', 154)")
    client.query("INSERT INTO inventory VALUES(3, 'apple', 100)")
    puts 'Inserted 3 rows of data.'

# Error handling
rescue Exception => e
    puts e.message

# Cleanup
ensure
    client.close if client
    puts 'Done.'
end

读取数据Read data

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

该代码使用 mysql2::client class.new() 方法连接到 Azure Database for MySQL。The code uses a mysql2::client class.new() method to connect to Azure Database for MySQL. 然后,该代码调用 query() 方法来运行 SELECT 命令。Then it calls method query() to run the SELECT commands. 然后,它会调用方法 close(),在终止之前关闭连接。Then it calls method close() to close the connection before terminating.

hostdatabaseusernamepassword 字符串替换为你自己的值。Replace the host, database, username, and password strings with your own values.

require 'mysql2'

begin
    # Initialize connection variables.
    host = String('mydemoserver.mysql.database.azure.com')
    database = String('quickstartdb')
    username = String('myadmin@mydemoserver')
    password = String('yourpassword')

    # Initialize connection object.
    client = Mysql2::Client.new(:host => host, :username => username, :database => database, :password => password)
    puts 'Successfully created connection to database.'

    # Read data
    resultSet = client.query('SELECT * from inventory;')
    resultSet.each do |row|
        puts 'Data row = (%s, %s, %s)' % [row['id'], row['name'], row['quantity']]
    end
    puts 'Read ' + resultSet.count.to_s + ' row(s).'

# Error handling
rescue Exception => e
    puts e.message

# Cleanup
ensure
    client.close if client
    puts 'Done.'
end

更新数据Update data

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

该代码使用 mysql2::client 类的 .new() 方法连接到适用于 MySQL 的 Azure 数据库。The code uses a mysql2::client class .new() method to connect to Azure Database for MySQL. 然后,该代码调用 query() 方法来运行 UPDATE 命令。Then it calls method query() to run the UPDATE commands. 然后,它会调用方法 close(),在终止之前关闭连接。Then it calls method close() to close the connection before terminating.

hostdatabaseusernamepassword 字符串替换为你自己的值。Replace the host, database, username, and password strings with your own values.

require 'mysql2'

begin
    # Initialize connection variables.
    host = String('mydemoserver.mysql.database.azure.com')
    database = String('quickstartdb')
    username = String('myadmin@mydemoserver')
    password = String('yourpassword')

    # Initialize connection object.
    client = Mysql2::Client.new(:host => host, :username => username, :database => database, :password => password)
    puts 'Successfully created connection to database.'

    # Update data
   client.query('UPDATE inventory SET quantity = %d WHERE name = %s;' % [200, '\'banana\''])
   puts 'Updated 1 row of data.'

# Error handling
rescue Exception => e
    puts e.message

# Cleanup
ensure
    client.close if client
    puts 'Done.'
end

删除数据Delete data

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

该代码使用 mysql2::client 类的 .new() 方法连接到适用于 MySQL 的 Azure 数据库。The code uses a mysql2::client class .new() method to connect to Azure Database for MySQL. 然后,该代码调用 query() 方法来运行 DELETE 命令。Then it calls method query() to run the DELETE commands. 然后,它会调用方法 close(),在终止之前关闭连接。Then it calls method close() to close the connection before terminating.

hostdatabaseusernamepassword 字符串替换为你自己的值。Replace the host, database, username, and password strings with your own values.

require 'mysql2'

begin
    # Initialize connection variables.
    host = String('mydemoserver.mysql.database.azure.com')
    database = String('quickstartdb')
    username = String('myadmin@mydemoserver')
    password = String('yourpassword')

    # Initialize connection object.
    client = Mysql2::Client.new(:host => host, :username => username, :database => database, :password => password)
    puts 'Successfully created connection to database.'

    # Delete data
    resultSet = client.query('DELETE FROM inventory WHERE name = %s;' % ['\'orange\''])
    puts 'Deleted 1 row.'

# Error handling
rescue Exception => e
    puts e.message

# Cleanup
ensure
    client.close if client
    puts 'Done.'
end

后续步骤Next steps