安裝mysql
npm i --save mysql
基本用法
創(chuàng)建管理一個連接
var mysql = require('mysql');
var connection = mysql.createConnection({
host: 'aliyuncs.com',
user: 'admin',
password: 'pwd'
});
connection.connect(function(err) {
if (err) {
console.error('error connecting: ' + err.stack);
return;
}
connection.query('SELECT * FROM ...', ..., function (error, results, fields) {
if (error) throw error;
//do something
connection.end();
});
});
轉義
為了避免SQL注入攻擊咒吐，在SQL查詢中使用任何用戶提供的數(shù)據(jù)之前，始終應該對其進行轉義属划。
手動轉義
mysql提供了mysql.escape(), connection.escape() 和 pool.escape()
var userId = 'user input values';
var sql = 'SELECT * FROM users WHERE userId = ' + connection.escape(userId);
connection.query(sql, function (error, results, fields) {
if (error) throw error;
//do something
});
自動轉義
使用?字符作為要轉義的值的占位符
connection.query('UPDATE users SET name = ?, address = ? WHERE userId = ?', [inputName, inputAddress, userId], function (error, results, fields) {
if (error) throw error;
//do something
});
連接和連接池
當我們一個api接口需要連接到數(shù)據(jù)庫進行增刪改查的時候恬叹，如果每次去創(chuàng)建連接然后去關閉，會大大的消耗數(shù)據(jù)庫服務的性能榴嗅。
連接池
連接是由池延遲創(chuàng)建的妄呕，基本流程是pool.getConnection() -> connection.query() -> connection.release()，有助于共享連接狀態(tài)以用于后續(xù)查詢嗽测。
var mysql = require('mysql');
var pool = mysql.createPool({
connectionLimit: 10,
host: 'aliyuncs.com',
user: 'admin',
password: 'pwd',
database: 'db',
timezone: 'Asia/Shanghai'
});
pool.getConnection(function(err, connection) {
if (err) throw err;
connection.query('SELECT * FROM ...', ..., function (error, results, fields) {
connection.release(); //釋放連接绪励，以便其他請求使用
if (error) throw error;
//do something
});
});
連接池組
其實就是多個連接池的組合
創(chuàng)建一個連接池組
var mysql = require('mysql');
var poolCluster = mysql.createPoolCluster();
poolCluster.add('pool1', {
connectionLimit: 10,
host: 'aliyuncs.com',
user: 'admin',
password: 'pwd',
database: 'db',
timezone: 'Asia/Shanghai',
database: 'db1'
});
poolCluster.add('pool2', {
connectionLimit: 10,
host: 'aliyuncs.com',
user: 'admin',
password: 'pwd',
database: 'db',
timezone: 'Asia/Shanghai',
database: 'db2'
});
使用連接池
poolCluster.getConnection('pool1', function (err, connection) { //使用pool1池
if (err) throw err;
connection.query('SELECT * FROM ...', ..., function (error, results, fields) {
connection.release();
if (error) throw error;
//do something
});
});
特別注意：因為nodejs事件循環(huán)機制肿孵，連接池組會一直保持活躍，直到mysql服務器關閉疏魏，所以當nodejs進程退出時要手動關閉mysql鏈接池組
process.on('exit', (code) => {
poolCluster.end(function (err) {});
});
事務組
多個連接請求“合并”成一個流程停做，來保證程序的正確運行
為什么需要事務組？
場景：當前有一個抽獎活動大莫，用戶點擊前端抽獎按鈕后調用api接口
后端邏輯：去道具表扣掉用戶道具 -> 去獎品表修改獎品為已發(fā)放給用戶發(fā)放獎品 -> 再中獎記錄表中生成一條中獎記錄
試想一下蛉腌，上面的三個步驟，任何一個步驟出問題都將導致重大bug
比如：道具扣掉了只厘，但是禮物沒發(fā)放烙丛，又比如禮物發(fā)放了沒有記錄
如何創(chuàng)建事務組
......
connection.beginTransaction(function(err) {
if (err) throw err;
connection.query('UPDATE props SET ...?', ..., function (error, results, fields) {
if (error) {
return connection.rollback(function() {
throw error;
});
}
connection.query('UPDATE SET ...', ..., function (error, results, fields) {
if (error) {
return connection.rollback(function() {
throw error;
});
}
connection.query('INSERT INTO record ...', ..., function (error, results, fields) {
if (error) {
return connection.rollback(function() {
throw error;
});
}
connection.commit(function(err) {
if (err) {
return connection.rollback(function() {
throw err;
});
}
//do something
});
});
});
});
});
以上代碼，任何一個連接操作出現(xiàn)問題羔味，執(zhí)行回滾河咽，整個流程都不會執(zhí)行，只有當三個連接操作都成功赋元，執(zhí)行提交忘蟹，改動才生效。
beginTransaction(), commit() 和 rollback() 分別對應了 START TRANSACTION, COMMIT, 和 ROLLBACK
事務的隔離級別
業(yè)務當中的事務并發(fā)是一個不可控點搁凸，這個時候會引發(fā)一系列的bug
事務并發(fā)帶來的問題
- 臟讀：事務1讀取了事務2中未提交的數(shù)據(jù)
- 虛讀（不可重復讀）：事務1多次讀取一條數(shù)據(jù)得到了不同的結果（事務2也修改了當前數(shù)據(jù)）
- 幻讀：事務1讀取到了事務2插入或者刪除的數(shù)據(jù)
MySql中的事務的隔離級別
| 隔離級別 | 描述 | 臟讀 | 不可重復讀 | 幻讀 |
|---|---|---|---|---|
| 讀未提交（RU） | 允許讀取尚未提交的數(shù)據(jù)變更 | 是 | 是 | 是 |
| 讀已提交（RC） | 允許讀取并發(fā)事務已經(jīng)提交的數(shù)據(jù) | 否 | 是 | 是 |
| 可重復讀（RR） | 對同一字段的多次讀取結果都是一致的媚值，除非數(shù)據(jù)是被本身事務自己所修改 | 否 | 否 | 否 |
| 串行化 | 最高的隔離級別，完全服從ACID的隔離級別 | 否 | 否 | 否 |
事務的隔離級別和并發(fā)度成反比的
需要注意的是與其他數(shù)據(jù)庫不同的是护糖，InnoDB 存儲引擎在 可重復讀（RR）事務隔離級別下使用的是Next-Key Lock 鎖算法褥芒，實現(xiàn)了串行化隔離級別的功能，同時沒有嚴重影響并發(fā)嫡良。
常見的防并發(fā)語句
防重復插入
INSERT INTO SELECT
INSERT INTO A(name1, name2, status) SELECT 1, 2, 0 WHERE NOT EXISTS (SELECT * FROM A WHERE create_time > '2020-01-01 00:00:00')
防重復修改
UPDATE WHERE帶上初始條件
UPDATE A SET status = 1 WHERE name1 = 1 AND status = 0
利用Promise封裝一個事務組方法
......
var transactionQuery = (database, callback) => {
return new Promise((resolve, reject) => {
poolCluster.getConnection(database, function (err, connection) {
if (err) {
reject({
msg: "系統(tǒng)內部錯誤喂很，請稍后再試"
});
return;
}
connection.beginTransaction(function (err) {
if (err) {
reject({
msg: "系統(tǒng)內部錯誤，請稍后再試"
});
return;
}
callback.apply(null, [connection, resolve, reject]);
});
});
});
}
//調用
const res = await common.transactionQuery('pool1', (connection, resolve, reject) => {
const rollback = (msg) => {
connection.rollback(() => {
if (msg) {
reject({
msg: msg
});
}
connection.release();
});
}
connection.query('UPDATE props SET ...?', ..., function (error, results, fields) {
if (error) {
rollback('系統(tǒng)內部錯誤：10001');
return;
}
connection.query('UPDATE lucky SET ...', ..., function (error, results, fields) {
if (error) {
rollback('系統(tǒng)內部錯誤：10002');
return;
}
connection.query('INSERT INTO record ...', ..., function (error, results, fields) {
if (error) {
rollback('系統(tǒng)內部錯誤：10003');
return;
}
connection.commit(() => {
resolve();
connection.release();
});
});
});
});
});
console.log(res);
UNION 與 UNION ALL
UNION會去重皆刺，UNION ALL 則不會，搭配GROUP BY 和 HAVING 可以執(zhí)行很多復雜的查詢
UNION要求執(zhí)行的多條SELECT查詢字段必須相同凌摄，當某張表不存在某個字段時可以使用AS去指定
eg: 查詢訂單表A和訂單表B每個用戶的支付總金額
poolCluster.getConnection('pool1', function (err, connection) { //使用pool1池
if (err) throw err;
connection.query('SELECT user_id, SUM(amount) AS total FROM A GROUP BY user_id UNION ALL SELECT uid AS user_id, SUM(amount) AS total FROM B GROUP BY user_id', [], function (error, results, fields) {
connection.release();
if (error) throw error;
//do something
});
});
eg: 查詢訂單表A和訂單表B每個用戶的支付總金額羡蛾，并支付金額大于100的用戶
poolCluster.getConnection('pool1', function (err, connection) { //使用pool1池
if (err) throw err;
connection.query('SELECT user_id, SUM(amount) AS total FROM A GROUP BY user_id UNION ALL SELECT uid AS user_id, SUM(amount) AS total FROM B GROUP BY user_id HAVING total > 100', [], function (error, results, fields) {
connection.release();
if (error) throw error;
//do something
});
});
SELECT嵌套
很多場景我們需要在多張表中查詢數(shù)據(jù)，嵌套寫法可以優(yōu)化性能
- SELECT查詢執(zhí)行了SELECT查詢的表（因為SELECT查詢執(zhí)行后其實也是一張表锨亏，所以可以進行二次SELECT）
SELECT table1.name, table2.total FROM (SELECT name, COUNT(DISTINCT user_id) AS total FROM temp WHERE DATE_FORMAT(create_time, '%Y-%m-%d') = '2021-08-12' GROUP BY name) AS table2, table1 WHERE table2.name = table1.name
- SELECT帶WHERE查詢執(zhí)行了SELECT查詢的結果集（SELECT查詢執(zhí)行后的結果集痴怨，可以進行二次SELECT）
SELECT * FROM (SELECT * FROM table2 WHERE all_number IN(SELECT number FROM table1 WHERE number = 51)) AS table3 WHERE table3.name LIKE '%亞%'
JOIN
LEFT JOIN：以左表為主，返回左表全部記錄和右表滿足ON條件的記錄器予，右表沒有的會以null填充
RIGHT JOIN：和LEFT JOIN相反
INNER JOIN：僅返回兩個表中滿足ON條件的記錄
JOIN中ON和WHERE的注意事項：
WHERE是對已經(jīng)搜索出的結果的過濾條件
ON是兩者JOIN形成新表時用到的匹配條件
INNER JOIN中WHERE在查詢語句中使用或者在ON后面使用浪藻，沒有區(qū)別
eg: 查詢訂單表A每筆訂單購買的產(chǎn)品名稱
poolCluster.getConnection('pool1', function (err, connection) { //使用pool1池
if (err) throw err;
connection.query('SELECT A.order_num, B.product_name FROM A LEFT JOIN B ON A.product_id = B.product_id', [], function (error, results, fields) {
connection.release();
if (error) throw error;
//do something
});
});
GROUP BY
用于對SELECT查詢后的數(shù)據(jù)進行分組，可以理解為先執(zhí)行SELECT再進行GROUP BY
例子乾翔，當有如下用戶消費金額表table
| id | date | name | cost |
|---|---|---|---|
| 1 | 2019-08-10 12:00:00 | 小明 | 5 |
| 2 | 2019-08-11 04:00:00 | 小明 | 200 |
| 3 | 2019-08-11 05:00:00 | 小紅 | 100 |
| 4 | 2019-08-11 06:00:00 | 小李 | 90 |
現(xiàn)需要查詢所有用戶的總消費額
執(zhí)行sql：
SELECT name, SUM(cost) AS total FROM `table` GROUP BY name
分析
SELECT省略
-
GROUP BY name
查詢后的表結構為：
| name | cost |
|---|---|
| 小明 | 5, 200 |
| 小紅 | 100 |
| 小李 | 90 |
SUM(cost) AS total
SUM在GROUP BY中是對分組后的每一組cost做了一個和的處理最終結果
| name | total |
|---|---|
| 小明 | 205 |
| 小紅 | 100 |
| 小李 | 90 |
這里還可以使用HAVING對聚合結果進行篩選
執(zhí)行sql：
SELECT name, SUM(cost) AS total FROM `table` GROUP BY name HAVING total > 200
最終結果就會變成：
| name | total |
|---|---|
| 小明 | 205 |
HAVING和WHERE的區(qū)別主要在于：
WHERE用于對數(shù)據(jù)表中字段的過濾爱葵，不可以用字段別名（可以用表別名）施戴，后面不能跟聚合函數(shù)
HAVING用于對分組過濾，可以用字段別名萌丈，后面可以跟聚合函數(shù)
使用別名
有些時候數(shù)據(jù)表里面的數(shù)據(jù)不符合我們的分組規(guī)則赞哗，這個時候就可以使用別名進行分組，因為是先SELECT再GROUP BY
現(xiàn)需要查詢當日消費的用戶總數(shù)：
執(zhí)行sql：
SELECT DATE_FORMAT(cur_time, '%Y年%m月%d日') AS time, COUNT(DISTINCT name) AS total FROM `table` GROUP BY time
DISTINCT
DISTINCT進行去重的主要原理是通過先對要進行去重的數(shù)據(jù)進行分組操作辆雾，然后從分組后的每組數(shù)據(jù)中取一條返回
分析
-
SELECT DATE_FORMAT(cur_time, '%Y年%m月%d日') AS time
查詢后的time字段：
| time |
|---|
| 2019年08月10日 |
| 2019年08月11日 |
| 2019年08月11日 |
| 2019年08月11日 |
-
GROUP BY time
查詢后的表結構為：
| time | name |
|---|---|
| 2019年08月10日 | 小明,小明 |
| 2019年08月11日 | 小紅,小李 |
COUNT(DISTINCT name) AS total
COUNT在GROUP BY中是對分組后的每一組去重和統(tǒng)計的處理最終結果
| time | total |
|---|---|
| 2019年08月10日 | 1 |
| 2019年08月11日 | 2 |
