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

快速入门:使用 Node.js 进行连接并查询 Azure Database for MySQL 中的数据Quickstart: Use Node.js to connect and query data in Azure Database for MySQL

在本快速入门中,你将使用 Node.js 连接到 Azure Database for MySQL。In this quickstart, you connect to an Azure Database for MySQL by using Node.js. 然后使用 SQL 语句在 Mac、Ubuntu Linux 和 Windows 平台的数据库中查询、插入、更新和删除数据。You then use SQL statements to query, insert, update, and delete data in the database from Mac, Ubuntu Linux, and Windows platforms.

本主题假设你熟悉如何使用 Node.js 进行开发,但不熟悉 Azure Database for MySQL 的用法。This topic assumes that you're familiar with developing using Node.js, but you're new to working with Azure Database for MySQL.

先决条件Prerequisites

重要

确保已使用 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

安装 Node.js 和 MySQL 连接器Install Node.js and the MySQL connector

根据自己的平台,按照相应部分中的说明安装 Node.jsDepending on your platform, follow the instructions in the appropriate section to install Node.js. 使用 npm 将 mysql 包及其依赖项安装到项目文件夹中。Use npm to install the mysql package and its dependencies into your project folder.

WindowsWindows

  1. 请访问 Node.js 下载页,然后选择所需的 Windows 安装程序选项。Visit the Node.js downloads page, and then select your desired Windows installer option.

  2. 创建本地项目文件夹,例如 nodejsmysqlMake a local project folder such as nodejsmysql.

  3. 打开命令提示符,然后将目录更改为项目文件夹,例如 cd c:\nodejsmysql\Open the command prompt, and then change directory into the project folder, such as cd c:\nodejsmysql\

  4. 运行 NPM 工具,将 mysql 库安装到项目文件夹中。Run the NPM tool to install the mysql library into the project folder.

    cd c:\nodejsmysql\
    "C:\Program Files\nodejs\npm" install mysql
    "C:\Program Files\nodejs\npm" list
    
  5. 通过检查 npm list 输出文本来验证安装。Verify the installation by checking the npm list output text. 随着新修补程序的发布,版本号可能会变化。The version number may vary as new patches are released.

Linux (Ubuntu)Linux (Ubuntu)

  1. 运行以下命令安装 Node.jsnpm(适用于 Node.js 的包管理器)。Run the following commands to install Node.js and npm the package manager for Node.js.

    sudo apt-get install -y nodejs npm
    
  2. 运行以下命令以创建项目文件夹 mysqlnodejs,并在该文件夹中安装 mysql 包。Run the following commands to create a project folder mysqlnodejs and install the mysql package into that folder.

    mkdir nodejsmysql
    cd nodejsmysql
    npm install --save mysql
    npm list
    
  3. 通过检查 npm list 输出文本来验证安装。Verify the installation by checking npm list output text. 随着新修补程序的发布,版本号可能会变化。The version number may vary as new patches are released.

Mac OSMac OS

  1. 输入以下命令安装 brew(适用于 Mac OS X 和 Node.js 的易用程序包管理器)。Enter the following commands to install brew, an easy-to-use package manager for Mac OS X and Node.js.

    ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
    brew install node
    
  2. 运行以下命令以创建项目文件夹 mysqlnodejs,并在该文件夹中安装 mysql 包。Run the following commands to create a project folder mysqlnodejs and install the mysql package into that folder.

    mkdir nodejsmysql
    cd nodejsmysql
    npm install --save mysql
    npm list
    
  3. 通过检查 npm list 输出文本来验证安装。Verify the installation by checking the npm list output text. 随着新修补程序的发布,版本号可能会变化。The version number may vary as new patches are released.

获取连接信息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, select All resources, and then search for the server you have created (such as mydemoserver).
  3. 选择服务器名称。Select 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

在 Node.js 中运行 JavaScript 代码Running the JavaScript code in Node.js

  1. 将 JavaScript 代码粘贴到文本文件中,然后使用文件扩展名 .js 将其保存到项目文件夹中(例如 C:\nodejsmysql\createtable.js 或 /home/username/nodejsmysql/createtable.js)。Paste the JavaScript code into text files, and then save it into a project folder with file extension .js (such as C:\nodejsmysql\createtable.js or /home/username/nodejsmysql/createtable.js).
  2. 打开命令提示符或 bash shell,然后将目录更改为项目文件夹 cd nodejsmysqlOpen the command prompt or bash shell, and then change directory into your project folder cd nodejsmysql.
  3. 若要运行应用程序,请输入 node 命令并后接文件名,例如 node createtable.jsTo run the application, enter the node command followed by the file name, such as node createtable.js.
  4. 在 Windows 上,如果 Node 应用程序不在环境变量路径中,则你可能需要使用完整路径来启动 Node 应用程序,例如 "C:\Program Files\nodejs\node.exe" createtable.jsOn Windows, if the node application is not in your environment variable path, you may need to use the full path to launch the node application, such as "C:\Program Files\nodejs\node.exe" createtable.js

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

通过以下代码进行连接,然后使用 CREATE TABLE 和 INSERT INTO SQL 语句加载数据。Use the following code to connect and load the data by using CREATE TABLE and INSERT INTO SQL statements.

mysql.createConnection() 方法用于与 MySQL 服务器对接。The mysql.createConnection() method is used to interface with the MySQL server. connect() 函数用于与服务器建立连接。The connect() function is used to establish the connection to the server. query() 函数用于针对 MySQL 数据库执行 SQL 查询。The query() function is used to execute the SQL query against MySQL database.

hostuserpassworddatabase 参数替换为你在创建服务器和数据库时指定的值。Replace the host, user, password, and database parameters with the values that you specified when you created the server and database.

const mysql = require('mysql');

var config =
{
    host: 'mydemoserver.mysql.database.azure.com',
    user: 'myadmin@mydemoserver',
    password: 'your_password',
    database: 'quickstartdb',
    port: 3306,
    ssl: true
};

const conn = new mysql.createConnection(config);

conn.connect(
    function (err) { 
    if (err) { 
        console.log("!!! Cannot connect !!! Error:");
        throw err;
    }
    else
    {
       console.log("Connection established.");
           queryDatabase();
    }   
});

function queryDatabase(){
       conn.query('DROP TABLE IF EXISTS inventory;', function (err, results, fields) { 
            if (err) throw err; 
            console.log('Dropped inventory table if existed.');
        })
       conn.query('CREATE TABLE inventory (id serial PRIMARY KEY, name VARCHAR(50), quantity INTEGER);', 
            function (err, results, fields) {
                if (err) throw err;
            console.log('Created inventory table.');
        })
       conn.query('INSERT INTO inventory (name, quantity) VALUES (?, ?);', ['banana', 150], 
            function (err, results, fields) {
                if (err) throw err;
            else console.log('Inserted ' + results.affectedRows + ' row(s).');
        })
       conn.query('INSERT INTO inventory (name, quantity) VALUES (?, ?);', ['orange', 154], 
            function (err, results, fields) {
                if (err) throw err;
            console.log('Inserted ' + results.affectedRows + ' row(s).');
        })
       conn.query('INSERT INTO inventory (name, quantity) VALUES (?, ?);', ['apple', 100], 
        function (err, results, fields) {
                if (err) throw err;
            console.log('Inserted ' + results.affectedRows + ' row(s).');
        })
       conn.end(function (err) { 
        if (err) throw err;
        else  console.log('Done.') 
        });
};

读取数据Read data

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

mysql.createConnection() 方法用于与 MySQL 服务器对接。The mysql.createConnection() method is used to interface with the MySQL server. connect() 方法用于与服务器建立连接。The connect() method is used to establish the connection to the server. query() 方法用于针对 MySQL 数据库执行 SQL 查询。The query() method is used to execute the SQL query against MySQL database. 结果数组用于保存查询结果。The results array is used to hold the results of the query.

hostuserpassworddatabase 参数替换为你在创建服务器和数据库时指定的值。Replace the host, user, password, and database parameters with the values that you specified when you created the server and database.

const mysql = require('mysql');

var config =
{
    host: 'mydemoserver.mysql.database.azure.com',
    user: 'myadmin@mydemoserver',
    password: 'your_password',
    database: 'quickstartdb',
    port: 3306,
    ssl: true
};

const conn = new mysql.createConnection(config);

conn.connect(
    function (err) { 
        if (err) { 
            console.log("!!! Cannot connect !!! Error:");
            throw err;
        }
        else {
            console.log("Connection established.");
            readData();
        }   
    });

function readData(){
        conn.query('SELECT * FROM inventory', 
            function (err, results, fields) {
                if (err) throw err;
                else console.log('Selected ' + results.length + ' row(s).');
                for (i = 0; i < results.length; i++) {
                    console.log('Row: ' + JSON.stringify(results[i]));
                }
                console.log('Done.');
            })
       conn.end(
           function (err) { 
                if (err) throw err;
                else  console.log('Closing connection.') 
        });
};

更新数据Update data

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

mysql.createConnection() 方法用于与 MySQL 服务器对接。The mysql.createConnection() method is used to interface with the MySQL server. connect() 方法用于与服务器建立连接。The connect() method is used to establish the connection to the server. query() 方法用于针对 MySQL 数据库执行 SQL 查询。The query() method is used to execute the SQL query against MySQL database.

hostuserpassworddatabase 参数替换为你在创建服务器和数据库时指定的值。Replace the host, user, password, and database parameters with the values that you specified when you created the server and database.

const mysql = require('mysql');

var config =
{
    host: 'mydemoserver.mysql.database.azure.com',
    user: 'myadmin@mydemoserver',
    password: 'your_password',
    database: 'quickstartdb',
    port: 3306,
    ssl: true
};

const conn = new mysql.createConnection(config);

conn.connect(
    function (err) { 
        if (err) { 
            console.log("!!! Cannot connect !!! Error:");
            throw err;
        }
        else {
            console.log("Connection established.");
            updateData();
        }   
    });

function updateData(){
       conn.query('UPDATE inventory SET quantity = ? WHERE name = ?', [200, 'banana'], 
            function (err, results, fields) {
                if (err) throw err;
                else console.log('Updated ' + results.affectedRows + ' row(s).');
        })
       conn.end(
           function (err) { 
                if (err) throw err;
                else  console.log('Done.') 
        });
};

删除数据Delete data

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

mysql.createConnection() 方法用于与 MySQL 服务器对接。The mysql.createConnection() method is used to interface with the MySQL server. connect() 方法用于与服务器建立连接。The connect() method is used to establish the connection to the server. query() 方法用于针对 MySQL 数据库执行 SQL 查询。The query() method is used to execute the SQL query against MySQL database.

hostuserpassworddatabase 参数替换为你在创建服务器和数据库时指定的值。Replace the host, user, password, and database parameters with the values that you specified when you created the server and database.

const mysql = require('mysql');

var config =
{
    host: 'mydemoserver.mysql.database.azure.com',
    user: 'myadmin@mydemoserver',
    password: 'your_password',
    database: 'quickstartdb',
    port: 3306,
    ssl: true
};

const conn = new mysql.createConnection(config);

conn.connect(
    function (err) { 
        if (err) { 
            console.log("!!! Cannot connect !!! Error:");
            throw err;
        }
        else {
            console.log("Connection established.");
            deleteData();
        }   
    });

function deleteData(){
       conn.query('DELETE FROM inventory WHERE name = ?', ['orange'], 
            function (err, results, fields) {
                if (err) throw err;
                else console.log('Deleted ' + results.affectedRows + ' row(s).');
        })
       conn.end(
           function (err) { 
                if (err) throw err;
                else  console.log('Done.') 
        });
};

后续步骤Next steps