Node.js method example for connecting to MySQL-JS Tutorial-php.cn

In this article, we mainly introduce how to use Node.js to connect to MySQL and operate the database. The Websites table SQL file used in this tutorial: websites.sql, I hope it can be helpful to everyone.

Installing the driver

This tutorial uses Taobao’s customized cnpm command for installation:

$ cnpm install mysql

Copy after login

Connect to the database

Modify in the following examples according to your actual situation Configure and modify the database user name, password and database name:

test.js file code:

var mysql      = require(&#39;mysql&#39;);
var connection = mysql.createConnection
({  host     : &#39;localhost&#39;,  user     :
 &#39;root&#39;,  password : &#39;123456&#39;,  database : &#39;test&#39;});
  connection.connect(); connection.query
  (&#39;SELECT 1 + 1 AS solution&#39;, function 
  (error, results, fields) {  if (error)
   throw error;  console.log
   (&#39;The solution is: &#39;, results[0]
   .solution);}
   );

Copy after login

Execute the following command and the output will be:

$ node test.js
The solution is: 2

Copy after login

数据库连接参数说明：

参数	描述
host	主机地址（默认：localhost）
user	用户名
password	密码
port	端口号（默认：3306）
database	数据库名
charset	连接字符集（默认：'UTF8_GENERAL_CI'，注意字符集的字母都要大写）
localAddress	此IP用于TCP连接（可选）
socketPath	连接到unix域路径，当使用 host 和 port 时会被忽略
timezone	时区（默认：'local'）
connectTimeout	Connection timeout (default: no limit; unit: milliseconds)
stringifyObjects	Whether to serialize objects
typeCast	Whether to convert column values into local JavaScript type values (default: true)
queryFormat	Custom query statement formatting method
supportBigNumbers	When the database supports bigint or decimal type columns, you need to set this option to true (default: false)
bigNumberStrings	supportBigNumbers and bigNumberStrings enable forcing bigint or decimal columns to be returned as JavaScript string types (default: false)
dateStrings	Force timestamp, datetime, and data types to be returned as string types instead of JavaScript Date types (default: false)
debug	Enable debugging ( Default: false)
multipleStatements	Whether to allow multiple MySQL statements in one query (default: false)
flags	is used to modify the connection flags
ssl	Use the ssl parameter (the same format as the crypto.createCredenitals parameter) or a ssl configuration file name containing The string, currently only bundles the Amazon RDS configuration file

更多说明可参见：https://github.com/mysqljs/mysql

数据库操作( CURD )

在进行数据库操作前，你需要将本站提供的 Websites 表 SQL 文件websites.sql 导入到你的 MySQL 数据库中。

本教程测试的 MySQL 用户名为 root，密码为 123456，数据库为 test，你需要根据自己配置情况修改。

查询数据

将上面我们提供的 SQL 文件导入数据库后，执行以下代码即可查询出数据：

查询数据

var mysql  = require(&#39;mysql&#39;);   
var connection = mysql.createConnection
({       host     : &#39;localhost&#39;,       
  user     : &#39;root&#39;,               
   password : &#39;123456&#39;,         
   port: &#39;3306&#39;,                     
   database: &#39;test&#39;, });  
   connection.connect(); 
   var  sql = &#39;SELECT * FROM websites&#39;;
   //查connection.query(sql,function (err, result) 
   {        if(err)
   {          console.log(&#39;[SELECT ERROR] - &#39;,err.message);          return;        }
           console.log(&#39;--------------------------SELECT----------------------------&#39;);       
           console.log(result);       
           console.log(&#39;------------------------------------------------------------\n\n&#39;);  }); 
           connection.end();

Copy after login

执行以下命令输出就结果为：

$ node test.js--------------------------SELECT----------------------------[ RowDataPacket {
    id: 1,
    name: &#39;Google&#39;,
    url: &#39;https://www.google.cm/&#39;,
    alexa: 1,
    country: &#39;USA&#39; },
  RowDataPacket {
    id: 2,
    name: &#39;淘宝&#39;,
    url: &#39;https://www.taobao.com/&#39;,
    alexa: 13,
    country: &#39;CN&#39; },
  RowDataPacket {
    id: 3,
    name: &#39;菜鸟教程&#39;,
    url: &#39;http://www.runoob.com/&#39;,
    alexa: 4689,
    country: &#39;CN&#39; },
  RowDataPacket {
    id: 4,
    name: &#39;微博&#39;,
    url: &#39;http://weibo.com/&#39;,
    alexa: 20,
    country: &#39;CN&#39; },
  RowDataPacket {
    id: 5,
    name: &#39;Facebook&#39;,
    url: &#39;https://www.facebook.com/&#39;,
    alexa: 3,
    country: &#39;USA&#39; } ]------------------------------------------------------------

Copy after login

插入数据

我们可以向数据表 websties 插入数据：

插入数据

var mysql  = require(&#39;mysql&#39;);   
var connection = mysql.createConnection
({       host     : &#39;localhost&#39;,         
user     : &#39;root&#39;,               
password : &#39;123456&#39;,         port: &#39;3306&#39;,                     database: &#39;test&#39;, });  
connection.connect(); var  addSql = &#39;INSERT INTO websites(Id,name,url,alexa,country) 
VALUES(0,?,?,?,?)&#39;;var  addSqlParams = [&#39;菜鸟工具&#39;, &#39;https://c.runoob.com&#39;,&#39;23453&#39;, &#39;CN&#39;];
//增connection.query(addSql,addSqlParams,function (err, result) {        if(err)
{         console.log(&#39;[INSERT ERROR] - &#39;,err.message);         return;        }                
console.log(&#39;--------------------------INSERT----------------------------&#39;);       
//console.log(&#39;INSERT ID:&#39;,result.insertId);               console.log(&#39;INSERT ID:&#39;,result);               
console.log(&#39;-----------------------------------------------------------------\n\n&#39;);  }); 
connection.end();

Copy after login

执行以下命令输出就结果为：

$ node test.js--------------------------INSERT----------------------------INSERT ID: OkPacket {
  fieldCount: 0,
  affectedRows: 1,
  insertId: 6,
  serverStatus: 2,
  warningCount: 0,
  message: &#39;&#39;,
  protocol41: true,
  changedRows: 0 }-----------------------------------------------------------------

Copy after login

执行成功后，查看数据表，即可以看到添加的数据：

更新数据

我们也可以对数据库的数据进行修改：

更新数据

执行以下命令输出就结果为：

--------------------------UPDATE----------------------------UPDATE affectedRows 1-----------------------------------------------------------------

Copy after login

执行成功后，查看数据表，即可以看到更新的数据：

删除数据

我们可以使用以下代码来删除 id 为 6 的数据:

删除数据

var mysql  = require(&#39;mysql&#39;);   
var connection = mysql.createConnection
({       host     : &#39;localhost&#39;,         
user     : &#39;root&#39;,                password : &#39;123456&#39;,         
port: &#39;3306&#39;,                     database: &#39;test&#39;, });  
connection.connect(); var delSql = &#39;DELETE FROM websites where id=6&#39;;
//删connection.query(delSql,function (err, result) {        if(err)
{          console.log(&#39;[DELETE ERROR] - &#39;,err.message);          
return;        }                
console.log(&#39;--------------------------DELETE----------------------------&#39;);       
console.log(&#39;DELETE affectedRows&#39;,result.affectedRows);       
console.log(&#39;-----------------------------------------------------------------\n\n&#39;);  }); 
connection.end();

Copy after login

执行以下命令输出就结果为：

--------------------------DELETE----------------------------DELETE affectedRows 1-----------------------------------------------------------------

Copy after login

执行成功后，查看数据表，即可以看到 id=6 的数据已被删除：