Nodejs 与 MySQL数据库交互 |
您所在的位置：网站首页 › wampserver怎么用mysql › Nodejs 与 MySQL数据库交互 |
Nodejs 与 MySQL数据库交互
|
本文将介绍如何使用mysqljs/mysql模块实现Node.js应用程序与MySQL数据库的交互。 准备 准备Nodejs环境，Nodejs官网准备MySQL数据库，MySQL官网 安装mysqljs/mysql模块介绍 This is a Node.js module available through the npm registry. This is a node.js driver for mysql. It is written in JavaScript, does not require compiling, and is 100% MIT licensed. 在Node.js应用程序根目录，使用npm命令安装mysql模块 npm install mysql 使用 1. 连接数据库 step1：导入mysql模块 const mysql = require("mysql"); step2：调用createConnection()创建连接对象 // createConnection(config),config：Object|String 提供两种创建连接对象的方式 // config 详情参考 https://github.com/mysqljs/mysql#connection-options // Object 传参方式 const conn = mysql.createConnection({ host:"localhost", port:3306, user:"root", password:"1234qwer", database:"ex_test" }); // String 传参方式，可读性差，不推荐 // URL格式的连接配置，如 mysql://user:pass@host/db?debug=true const conn2 = mysql.createConnection("mysql://root:1234qwer@localhost:3306/ex_test?debug=true"); // 数据库连接配置，也可以使用如下方式进行配置 conn.config.database = "ex_test"; step3：调用conn.connect()建立连接 // connect()方法接受一个具有err参数的回调函数，如果发生任何错误，它将提供详细的错误 conn.connect(function (err){ if(err){ console.log(err.message); }else{ console.log("---------------[conn.connect] succeed.---------------") } }) step4：调用conn.query()操作数据库 // 对数据库的CRUD，都是用query()方法，后边再详细说明 conn.query("select 1",function (err,results){ console.log(results); }) step5：调用conn.end()关闭连接 // end()接受一个回调函数，并且会在query结束之后才触发，如果query出错，仍然会终止连接，错误会传递到回调函数中处理 conn.end(function (err){ if(err){ console.log(err.message); }else{ console.log("---------------[conn.end] succeed.---------------") } }); // 要立即强制连接，可以使用destroy()方法 // destroy()立即终止数据库连接，即使还有query没有完成，它不会像end()方法那样采取任何回调参数 conn.destroy(); 2. 操作数据库对数据库的操作(CRUD、建库、建表等)，都是使用query()方法，该方法接收三个参数： 第1个参数：sql语句，或者option Object； 第2个参数：为sql语句中的占位符提供的值，是单个值，或多个值组成的数组，或对象； 第3个参数：回调函数，回调函数有3个参数（error,result,fields），error是发生错误时的错误信息，results是sql执行结果，fields是涉及的字段（如果有）； 详情参考：https://github.com/mysqljs/mysql#performing-queries 2.1 创建库 // 创建数据库 nodejs_mysql_db（不存在时才创建） let db_sql = "CREATE DATABASE IF NOT EXISTS `nodejs_mysql_db` DEFAULT CHARSET utf8;" conn.query(db_sql,function (err,results){ if(err){return console.log(err);} console.log(results.affectedRows); // 如果不报错，无论是否已创建 results.affectedRows=1 }) 2.2 创建表 // 创建数据表 student (不存在时才创建) let tb_sql = `CREATE TABLE IF NOT EXISTS students ( Sid INT(8) NOT NULL AUTO_INCREMENT, Sname VARCHAR(10) NOT NULL, Sage INT(11) DEFAULT NULL, Ssex VARCHAR(10) DEFAULT NULL, PRIMARY KEY (Sid) ) ENGINE=INNODB DEFAULT CHARSET=utf8;` // 上边在创建数据库连接对象时，连接的database是ex_test，这里给切换到新建的nodejs_mysql_db数据库 conn.config.database = "nodejs_mysql_db"; conn.query(tb_sql,function (err,results){ if(err){return console.log(err);} console.log(results); }) 2.3 新增数据 // 方式1：每个?占位符 代表1个列属性，使用数组方式传值，数组长度与占位符个数一致，数组的每个元素将依次传给?占位符 // Arrays are turned into list, e.g. ['a', 'b'] turns into 'a', 'b' let sql_insert1 = "INSERT INTO students (Sname,Sage,Ssex) VALUES (?,?,?)"; let student1 = ["张敏","20","女"]; // console.log(mysql.format(sql_insert1,student1)); // 可以使用 mysql.format() 将sql语句转化为标准sql let qr1 = conn.query(sql_insert1,student1,function (err,results){ if(err){return console.log(err);} let ret = {affectedRows:results.affectedRows,insertId:results.insertId} console.log(ret); }) console.log(qr1.sql); // 查看query实际执行的sql语句 // 方式2：每个 代表1组数据，使用数组方式传值 // Nested arrays are turned into grouped lists (for bulk inserts), // e.g. [['a', 'b'], ['c', 'd']] turns into ('a', 'b'), ('c', 'd') let sql_insert2 = "INSERT INTO students (Sname,Sage,Ssex) VALUES ?"; let student2 = [["赵莹莹","21","男"],["钱娟娟","19","女"]]; // console.log(mysql.format(sql_insert2,[student2])); let qr2 = conn.query(sql_insert2,[student2],function (err,results){ if(err){return console.log(err);} let ret = {affectedRows:results.affectedRows,insertId:results.insertId} console.log(ret); }) console.log(qr2.sql); // 方式3：使用 INSERT INTO ... SET // Objects are turned into key = 'val' pairs let sql_insert3 = "INSERT INTO students SET ?"; let student3 = {Sname:"李少臣",Sage:"21",Ssex:"男"}; // console.log(mysql.format(sql_insert3,student3)); let qr3 = conn.query(sql_insert3,student3,function (err,results){ if(err){return console.log(err);} let ret = {affectedRows:results.affectedRows,insertId:results.insertId} console.log(ret); }) console.log(qr3.sql);运行结果 运行结果 运行结果 运行结果 在【1. 连接数据库】小节中介绍过 one-by-one 的连接创建/管理的方式mysql.createConnection(config)，mysql模块还支持连接池的方式进行数据库连接 3.1 创建连接池创建数据库连接池mysql.createPool(config) const mysql = require("mysql"); const pool = mysql.createPool({ connectionLimit:5, //设置最大连接数为5 host:"localhost", user:"root", password:"1234qwer" }) // 可以通过如下方式新增或更改连接配置 pool.config.connectionConfig.database = "nodejs_mysql_db";pool-option 除了和 connection-options 相同的参数外，还有几个扩展的参数： acquireTimeout: 连接获取过程中发生超时之前的毫秒数。这与connectTimeout略有不同，因为获取池连接并不总是需要进行连接。如果连接请求已排队，则该请求在队列中花费的时间不计入此超时。（默认值：10000）waitForConnections: 决定当没有可用连接并且已达到限制时池的操作。如果为true，则池将对连接请求进行排队，并在连接请求可用时调用它。如果为false，则池将立即回调并返回一个错误。（默认值：true）connectionLimit: 一次创建的最大连接数。（默认值：10）queueLimit: 在从getConnection返回错误之前，池将排队的最大连接请求数。如果设置为0，则排队的连接请求数没有限制。（默认值：0） 3.2 使用连接池使用连接池查询有两种方式 方式1：直接使用 pool.query() // 直接使用（多个pool.query()可能是不同的连接，它们并行运行） pool.query("SELECT Sid,Sname FROM students",function (err,results){ if(err){return console.log(err)} console.log("All students: ",results); // pool.end(); //结束连接池的所有连接 })方式2：共享连接 pool.getConnection() // 共享连接（使用pool.getConnection()可以为后续查询共享连接状态） // pool.getConnection() -> connection.query() -> connection.release() pool.getConnection(function (err,conn){ conn.query("SELECT Sid,Sname FROM students LIMIT 1",function (err,results){ if(err){return console.log(err)} console.log(results); }) conn.query("SELECT COUNT(*) AS count FROM students",function (err,results){ if(err){return console.log(err)} console.log("total students is: "+results[0].count); }) console.log({threadId:conn.threadId,state:conn.state}); conn.release(); // 释放连接到连接池 // pool.end(); }) 3.3 关闭连接池在3.2节中 使用 conn.release() 释放连接，释放后的连接会放回到连接池，可继续供其他使用者使用。要关闭连接并将其从池中删除，请使用 conn.destroy()。如果想关闭连接池，可使用 pool.end()方法，该方法接收一个回调函数，回调函数有err参数，可以使用该回调函数来知道所有连接何时结束。一旦调用了pool.end，就无法再执行pool.getConnection和其他操作。 pool.end(function (err){ if(err){return console.log(err)} console.log("The pool connections are terminated now.") }) 4. 数据库重连数据库可能由于宕机、重启、连接超时等原因，导致连接中断，这种就需要有重连接机制，来增强应用的健壮性。 可以代码中通过监听连接的error事件，判断返回errorcode是否为：PROTOCOL_CONNECTION_LOST ，如果是用setTimeout定时3秒重连！ const mysql = require("mysql"); let db_config = { host: 'localhost', user: 'root', password: '1234qwer', database: 'nodejs_mysql_db' }; let connection; let conn_times = 0; function handleDisconnect() { connection = mysql.createConnection(db_config); connection.connect(function(err) { conn_times++; if(err) { console.log("第 " + conn_times + " 次连接数据库...失败！"); console.log("连接数据库失败，3s后即将尝试重连..."); setTimeout(handleDisconnect, 3000); }else{ console.log("第 " + conn_times + " 次连接数据库...成功！"); conn_times=0; } }); connection.on('error', function(err) { console.log("数据库连接异常！！！"); if(err.code === 'PROTOCOL_CONNECTION_LOST') { handleDisconnect(); } }); } handleDisconnect();
参考：escaping-query-values 为了避免SQL注入攻击，在SQL查询中使用任何用户提供的数据之前，应始终对其进行转义。可以使用mysql.escape()、connection.escape()、pool.escape() let Sid = "the Sid provided by user"; let sql1 = "SELECT * FROM students WHERE Sid = " + conn.escape(Sid); conn.query(sql1,function (err,results){ if (err) throw err; // ... })也可以使用 ? 作为想要转义值的占位符，多个占位符与传递的值顺序上是一一对应的 // 只有1个占位符，conn.query()的第2个参数的[]可以省略 let Sid = "the Sid provided by user"; let sql2 = "SELECT * FROM students WHERE Sid = ?"; conn.query(sql2,Sid,function (err, results){ if (err) throw err; // ... }); // 多个占位符，conn.query()的第2个参数是数组 let values = ["陈粒","19","女",5]; let sql3 = "UPDATE students SET Sname=?,Sage=?,Ssex=? WHERE Sid=?" conn.query(sql3,values,function (err,results){ if (err) throw err; // ... })不同类型的值，将以不同的方式进行转义，具体如下： Numbers 保持不变Booleans 转化为 true / falseDate objects 转化为 ‘YYYY-mm-dd HH:ii:ss’ 字符串Buffers 转化为16进制字符串 e.g. X’0fa5’Strings 安全转义Arrays 转化为列表，e.g. [‘a’, ‘b’] 转为 ‘a’, ‘b’Nested arrays 嵌套数组转化为分组列表(批量插入)，e.g. [[‘a’, ‘b’], [‘c’, ‘d’]] 转化为 (‘a’, ‘b’), (‘c’, ‘d’)Objects (含有.toSqlString方法的Objects)，.toSqlString()的返回值将作为 raw SQL 使用Objects 每个属性将转化为 key = ‘val’ 键值对，属性值是函数的将跳过，属性值是object的将调用toString()转为字符串undefined / null 转为 NULL利用上述转化规则可以做一些巧妙的事情，举例如下： // Nested arrays 嵌套数组转化为分组列表(批量插入)，e.g. [['a', 'b'], ['c', 'd']] 转化为 ('a', 'b'), ('c', 'd') // 批量插入，以分组列表的方式替换占位符 let sql_insert2 = "INSERT INTO students (Sname,Sage,Ssex) VALUES ?"; let student2 = [["赵莹莹","21","男"],["钱娟娟","19","女"]]; conn.query(sql_insert2,[student2],function (err,results){ if(err){return console.log(err);} // ... }) // Objects 每个属性将转化为 key = 'val' 键值对，属性值是函数的将跳过，属性值是object的将调用toString()转为字符串 // 以键值对的方式使用替换占位符 let sql_insert3 = "INSERT INTO students SET ?"; let student3 = {Sname:"李少臣",Sage:"21",Ssex:"男"}; let qr3 = conn.query(sql_insert3,student3,function (err,results){ if(err){return console.log(err);} // ... }) // Objects (含有.toSqlString方法的Objects)，.toSqlString()的返回值将作为 raw SQL 使用 // 下边这个例子是官网给的 let CURRENT_TIMESTAMP = { toSqlString: function() { return 'CURRENT_TIMESTAMP()'; } }; let sql = mysql.format('UPDATE posts SET modified = ? WHERE id = ?', [CURRENT_TIMESTAMP, 42]); console.log(sql); // UPDATE posts SET modified = CURRENT_TIMESTAMP() WHERE id = 42 6. 转义标识符参考：escaping-query-identifiers 如果您不能信任由用户提供的SQL标识符（数据库/表/列名），可以使用mysql.escapeId(identifier), connection.escapeId(identifier) or pool.escapeId(identifier) 对其进行转义 let sorter = 'date'; let query = 'SELECT * FROM posts ORDER BY ' + mysql.escapeId(sorter); console.log(query); // SELECT * FROM posts ORDER BY `date` // 支持添加特定的标识符 let sorter = 'date'; let query = 'SELECT * FROM posts ORDER BY ' + mysql.escapeId('posts.' + sorter); console.log(query); // SELECT * FROM posts ORDER BY `posts`.`date` // 可以使用??作为标识符的占位符 let userId = 1; let columns = ['username', 'email']; let query = connection.query('SELECT ?? FROM ?? WHERE id = ?', [columns, 'users', userId], function(err, results) { // ... }); console.log(query.sql); // SELECT `username`, `email` FROM `users` WHERE id = 1 7. 准备查询参考：preparing-queries 准备查询，该函数会选择合适的转义方法转义参数 let sql = "SELECT * FROM ?? WHERE ?? = ?"; let inserts = ['users', 'id', userId]; sql = mysql.format(sql, inserts); 8. 自定义格式化方式参考：custom-format 如果您喜欢使用其他类型的查询转义格式，那么可以使用连接配置选项来定义自定义格式函数 connection.config.queryFormat = function (query, values) { if (!values) return query; return query.replace(/\:(\w+)/g, function (txt, key) { if (values.hasOwnProperty(key)) { return this.escape(values[key]); } return txt; }.bind(this)); }; connection.query("UPDATE posts SET title = :title", { title: "Hello MySQL" }); 9. 多语句查询参考：multiple-statement-queries 因为多语句查询容易被SQL注入攻击，默认是不允许的，可以使用 const connection = mysql.createConnection({multipleStatements: true})开启该功能 connection.query('SELECT 1; SELECT 2', function (error, results, fields) { if (error) throw error; // `results` is an array with one element for every statement in the query: console.log(results[0]); // [{1: 1}] console.log(results[1]); // [{2: 2}] });暂时介绍这么多吧，更多更详细的用法，请参考官网：mysqljs/mysql 其他参考资料： nodejs操作MySQL数据库 Nodejs学习笔记（四）— 与MySQL交互（felixge/node-mysql） nodejs中mysql用法 |
从执行结果可以看出，大概每过3秒会尝试重连1次数据库服务，直到连接成功【本文地址】
今日新闻 |
推荐新闻 |