How to operate and use mysql in nodejs (with code)

The content of this article is about how to operate and use mysql in nodejs (with code). It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

Installation

npm install mysql

Test MySQL

Download mysql and install it yourself. I won’t explain it. It’s very simple.
Use the official example

var mysql      = require(‘mysql’); 
  var connection = mysql.createConnection({ 
    host     : ‘localhost’, 
    user     : ‘me’, 
    password : ‘secret’, 

    database : ‘my_db’ 

  });//创建mysql链接 

   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); 

  });//执行sql语句 

   connection.end();//关闭链接

Get to know the Connection Options

If you want to create a database connection, you must first understand the Options

　 host：主机地址 （默认：localhost） 

  user：用户名 

  password：密码 

  port：端口号 （默认：3306） 

  database：数据库名 

  charset：连接字符集（默认：’UTF8_GENERAL_CI’，注意字符集的字母都要大写） 

  localAddress：此IP用于TCP连接（可选） 

  socketPath：连接到unix域路径，当使用 host 和 port 时会被忽略 

  timezone：时区（默认：’local’） 

  connectTimeout：连接超时（默认：不限制；单位：毫秒） 

  stringifyObjects：是否序列化对象（默认：’false’ ；与安全相关(https://github.com/felixge/node-mysql/issues/501) 

  typeCast：是否将列值转化为本地JavaScript类型值 （默认：true） 

  queryFormat：自定义query语句格式化方法 (https://github.com/felixge/node-mysql#custom-format) 

  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：用于修改连接标志，更多详情：(https://github.com/felixge/node-mysql#connection-flags) 

  ssl：使用ssl参数（与crypto.createCredenitals参数格式一至）或一个包含ssl配置文件名称的字符串，目前只捆绑Amazon RDS的配置文件

Add, delete, Modify and check

Add, delete, modify and check are mainly determined by sql statements.

1. Add

var  userAddSql = ‘INSERT INTO userinfo(Id,UserName,UserPass) VALUES(0,?,?)’; 

  var  userAddSql_Params = [‘Wilson’, ‘abcd’]; 

  connection.query(userAddSql,userAddSql_Params,function (err, result) { 

    … 

  });

2. Delete

var userModSql = ‘DELETE FROM userinfo’; 

  connection.query(userModSql,function (err, result) { 

  … 

  });

3. Modify

var userModSql = ‘UPDATE userinfo SET UserName = ?,UserPass = ? WHERE Id = ?’; 

  var userModSql_Params = [‘钟慰’, ‘5678’,1]; 

  connection.query(userModSql,userModSql_Params,function (err, result) { 

  … 

  });

4. Query

var  userGetSql = ‘SELECT * FROM userinfo’; 

  connection.query(userGetSql ,function (err, result) { 

  … 

  });

Two methods and differences in ending a database connection

In the previous example, I will call a connection.end() at the end. Method, this method connection.connect() corresponds to a start and an end!
There are actually two methods to end the connection: end() and destroy(); The error will be returned to the err parameter of the callback function and can be handled in the callback function!

Destory() is relatively violent. It has no callback function and is executed immediately, regardless of whether the query is completed!

Disconnection and reconnection

var mysql = require(‘mysql’); 

  var db_config = { 

    host     : ‘192.168.0.200’, 

    user     : ‘root’, 

    password : ‘abcd’, 

    port: ‘3306’, 

    database: ‘nodesample’ 

  }; 

  var connection; 

  function handleDisconnect() { 

    connection = mysql.createConnection(db_config); 

    connection.connect(function(err) { 

      if(err) { 

        console.log(“进行断线重连：” + new Date()); 

        setTimeout(handleDisconnect, 2000);   //2秒重连一次 

        return; 

      } 

       console.log(“连接成功”); 

    }); 

    connection.on(‘error’, function(err) { 

      console.log(‘db error’, err); 

      if(err.code === ‘PROTOCOL_CONNECTION_LOST’) {  

        handleDisconnect(); 

      } else { 

        throw err; 

      } 

    }); 

  } 

  handleDisconnect();

sql link pool Pooling connections

1. To create a connection pool, use the createPool method, options and createConntion is consistent and can monitor connection events.

The connection pool will automatically disconnect and reconnect

var mysql = require(‘mysql’); 

  //创建连接池 

  var pool  = mysql.createPool({ 

    host     : ‘192.168.0.200’, 

    user     : ‘root’, 

    password : ‘abcd’ 

  }); 

  //监听connection事件 

  pool.on(‘connection’, function(connection) { 

      connection.query(‘SET SESSION auto_increment_increment=1’); 

  }); 

    

  连接池可以直接使用，也可以共享一个连接或管理多个连接（引用官方示例） 

  //直接使用 

  pool.query(‘SELECT 1 + 1 AS solution’, function(err, rows, fields) { 

    if (err) throw err; 

    console.log(‘The solution is: ‘, rows[0].solution); 

  }); 

  //共享 

  pool.getConnection(function(err, connection) { 

    // connected! (unless err is set) 

  });

2. Other connection pool configuration options

waitForConnections

When the connection pool has no connections or exceeds the maximum limit, set to true and the connection will be put into the queue. Set to false and an error will be returned.

connectionLimit connection number limit, default: 10
queueLimit maximum connection request queue limit , set to 0 to indicate no limit, default: 0

　

3. Release
Calling the connection.release() method will put the connection back into the connection pool, waiting for other Users use!
Others...

1. escape()

To prevent SQL injection, you can use pool.escape() and connect.escape(). Not much to say, you can try

like this using 'SELECT * FROM userinfo WHERE id = ' pool.escape('5 OR ID = 6')

2.escapeId()

If you cannot trust the SQL identifier (database name, table name, column name), you can use the conversion method escapeId();

Similar to this 'SELECT * FROM posts ORDER BY ' connect.escapeId("date");

3.format()

You can use mysql.format to prepare query statements. This function will Automatically choose the appropriate method to escape parameters.

var sql = “SELECT * FROM ? WHERE ? = ?”; 

     var inserts = [‘users’, ‘id’, 123]; 

      sql = mysql.format(sql, inserts);

4. Custom formatting function

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" });

Related recommendations:

js code to implement data transfer between pages

reacHow to implement the function of changing skin color

What are the methods in jQuery? Commonly used methods in jQuery (with code)

The above is the detailed content of How to operate and use mysql in nodejs (with code). For more information, please follow other related articles on the PHP Chinese website!