Node.js | MySQL 連接

環境準備

MySQL的安裝可以參閱Docker - 第四章 | 安裝MySQL。

顯示目前的資料庫清單：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.04 sec)

mysql>

新建資料庫：

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
7 rows in set (0.03 sec)

mysql>

選擇資料庫：

1 2	mysql> use test; Database changed

新增資料表和資料：

DROP TABLE IF EXISTS `websites`;
CREATE TABLE `websites` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL DEFAULT '' COMMENT '網站名稱',
  `url` varchar(255) NOT NULL DEFAULT '',
  `alexa` int(11) NOT NULL DEFAULT '0' COMMENT 'Alexa 排名',
  `country` char(10) NOT NULL DEFAULT '' COMMENT '國家',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;


BEGIN;
INSERT INTO `websites` VALUES 
('1', 'Google', 'https://www.google.com.tw/', '129', 'TW'), 
('2', 'shopee', 'https://shopee.tw/', '599', 'TW'), 
('3', 'J.J.\'s Blogs', 'https://morosedog.gitlab.io/', '99999999', 'TW'), 
('4', 'Facebook', 'https://zh-tw.facebook.com/', '4', 'USA');
COMMIT;

執行結果如下：

mysql> DROP TABLE IF EXISTS `websites`;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> CREATE TABLE `websites` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `name` char(20) NOT NULL DEFAULT '' COMMENT '',
    ->   `url` varchar(255) NOT NULL DEFAULT '',
    ->   `alexa` int(11) NOT NULL DEFAULT '0' COMMENT 'Alexa ',
    ->   `country` char(10) NOT NULL DEFAULT '' COMMENT '',
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO `websites` VALUES
    -> ('1', 'Google', 'https://www.google.com.tw/', '129', 'TW'),
    -> ('2', '', 'https://shopee.tw/', '599', 'TW'),
    -> ('3', 'J.J.\'s Blogs', 'https://morosedog.gitlab.io/', '99999999', 'TW'),
    -> ('4', 'Facebook', 'https://zh-tw.facebook.com/', '4', 'USA');
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

安裝 mysql 驅動

要使用Node.js訪問MySQL資料庫，你需要一個MySQL驅動程序。

1	npm install mysql --save

連接資料庫

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

執行結果如下：

1 2	$ node test.js The solution is: 2

連接資料庫參數

參數	說明
host	主機地址（默認：localhost）
user	用戶名
password	密碼
port	端口號（默認：3306）
database	資料庫名
charset	連接字符集（默認：’UTF8_GENERAL_CI’，注意字符集的字母都要大寫）
localAddress	此IP用於TCP連接（可選）
socketPath	連接到unix域路徑，當使用 host 和 port 時會被忽略
timezone	時區（默認：’local’）
connectTimeout	連接超時（默認：不限制；單位：毫秒）
stringifyObjects	是否序列化對象
typeCast	是否將列值轉化為本地JavaScript類型值（默認：true）
queryFormat	自定義query語句格式化方法
supportBigNumbers	資料庫支持bigint或decimal類型列時，需要設此option為true （默認：false）
bigNumberStrings	supportBigNumbers和bigNumberStrings啟用強制bigint或decimal列以JavaScript字符串類型返回（默認：false）
dateStrings	強制timestamp,datetime,data類型以字符串類型返回，而不是JavaScript Date類型（默認：false）
debug	開啟調試（默認：false）
multipleStatements	是否許一個query中有多個MySQL語句（默認：false）
flags	用於修改連接標誌
ssl	使用ssl參數（與crypto.createCredenitals參數格式一至）或一個包含ssl配置文件名稱的字符串，目前只捆綁Amazon RDS的配置文件

資料庫操作 (CURD)

查詢資料

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

執行結果如下：

$ node test.js
--------------------------SELECT------------------ ----------
[
  RowDataPacket {
    id: 1,
    name: 'Google',
    url: 'https://www.google.com.tw/',
    alexa: 129,
    country: 'TW'
  },
  RowDataPacket {
    id: 2,
    name: "shopee",
    url: 'https://shopee.tw/',
    alexa: 599,
    country: 'TW'
  },
  RowDataPacket {
    id: 3,
    name: "J.J.'s Blogs",
    url: 'https://morosedog.gitlab.io/',
    alexa: 99999999,
    country: 'TW'
  },
  RowDataPacket {
    id: 4,
    name: 'Facebook',
    url: 'https://zh-tw.facebook.com/',
    alexa: 4,
    country: 'USA'
  }
]
--------------------------------------------- ---------------

新增資料

var mysql = require('mysql');
 
var connection = mysql.createConnection({
  host : 'localhost',
  user : 'root',
  password : 'root',
  port: '3306',
  database: 'test'
});
 
connection.connect();
 
var addSql = 'INSERT INTO websites(Id,name,url,alexa,country) VALUES(0,?,?,?,?)';
var addSqlParams = ['PChome', 'https://shopping.pchome.com.tw/','607', 'TW'];
//增
connection.query(addSql,addSqlParams,function (err, result) {
        if(err){
         console.log('[INSERT ERROR] - ',err.message);
         return;
        }
 
       console.log('--------------------------INSERT------------------ ----------');
       console.log('INSERT ID:',result);
       console.log('--------------------------------------------- --------------------\n\n');
});
 
connection.end();

執行結果如下：

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

更新資料

var mysql = require('mysql');
 
var connection = mysql.createConnection({
  host : 'localhost',
  user : 'root',
  password : 'root',
  port: '3306',
  database: 'test'
});
 
connection.connect();
 
var modSql = 'UPDATE websites SET name = ?,url = ? WHERE Id = ?';
var modSqlParams = ['Pchome 24H', 'https://24h.pchome.com.tw/',6];
//改
connection.query(modSql,modSqlParams,function (err, result) {
   if(err){
         console.log('[UPDATE ERROR] - ',err.message);
         return;
   }
  console.log('--------------------------UPDATE------------------ ----------');
  console.log('UPDATE affectedRows',result.affectedRows);
  console.log('--------------------------------------------- --------------------\n\n');
});
 
connection.end();

執行結果如下：

$ node test.js
--------------------------UPDATE------------------ ----------
UPDATE affectedRows 1
--------------------------------------------- --------------------

刪除資料

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

執行結果如下：

$ node test.js
--------------------------DELETE------------------ ----------
DELETE affectedRows 1
--------------------------------------------- --------------------