How to use mysql2 - 10 common examples

async function mysql2Query(sql) {
  // recreate connection on fatal error
  if (mysql2Con._fatalError) {
    console.log('========== Reconnecting mysql2');
    mysql2Con = mysql2.createConnection({
      host: 'localhost',
      user: 'root',
      password: 'mysqlrootpassword',
      port: 23306,
      connectTimeout: 500,
      debug: true,
    });
    mysql2Con.on('error', (err) => {
      console.log('- STATS Mysql2 connection died:', err);
    });
  }
  console.log('================ MYSQL2 Running query ======');
  const res = await stdMysqlQuery(mysql2Con, sql);
  console.log('====================== done ================');
  return res;
}

});

// Read from the database when the page is loaded or after a word is successfully added
// Use the getWords function to get a list of words and definitions from the database
app.get("/words", function(request, response) {
  getWords()
    .then(function(words) {
      response.send(words);
    })
    .catch(function(err) {
      console.log(err);
      response.status(500).send(err);
    });
});

mysql
  .createConnection(options)
  .then(conn => {
    connection = conn;
    return connection.query(
      "CREATE TABLE IF NOT EXISTS words (id int auto_increment primary key, word varchar(256) NOT NULL, definition varchar(256) NOT NULL)"
    );
  })
  .then(result => {
    // Listen for a connection.
    app.listen(port, function() {
      console.log("Server is listening on port " + port);
    });
  })
  .catch(err => {
    console.log(err);
    process.exit(1);

exports.handler = async (event) => {
  const cmd = event.command
  const BRANCH = event.branch ? event.branch : 'develop'

  // clone repo using https://github.com/lambci/git-lambda-layer
  // configure 10s timeout on lambda
  await exec(`rm -rf /tmp/*`)
  await exec(`cd /tmp && git clone --depth 1 --single-branch --branch ${BRANCH} ${REPO}`)
  const { stdout, stderr } = await exec(`ls ${WRITABLE_LAMBDA_PATH}`)
  console.log('diffs found: ' + stdout.replace('\n', ' '))

  await mysql.createConnection(
    {
      host: DB_HOST,
      user: DB_USERNAME,
      password: DB_PASSWORD
    }
  ).then(connection => {
    connection.query(`CREATE DATABASE IF NOT EXISTS ${DB_NAME};`)
  })

  const sequelize = new Sequelize(DB_NAME, DB_USERNAME, DB_PASSWORD, {
    host: DB_HOST,
    logging: console.log,
    port: 3306,
    dialect: 'mysql',
    pool: {
      min: 0,

async function deleteUser(ctx) {
  const data = ctx.request.body
  let ids = data.ids
  let msg
  if (/^\d+(,\d+)*$/.test(ids)) {
    const arr = ids.split(',')
    const connection = await mysql.createConnection(config.mysqlDB)
    const [result] = await connection.execute(`DELETE from user where user_type<>1 and user_type<>2 and id in (${arr.map(() => '?').join(',')})`, arr)
    msg = result.affectedRows > 0 ? '' : '删除用户失败！'
    await connection.end()
  } else {
    msg = 'ID参数不合法'
  }
  ctx.body = {
    success: !msg,
    message: msg,
    data: {}
  }
}
// 用户上传头像

if (data.sort_id) {
    querying += ' and code = ?'
    arr.push(data.sort_id)
  }
  let reg = /^\d{4}-\d{1,2}-\d{1,2}$/
  if (reg.test(begin) && reg.test(end)) {
    if (begin === end) {
      querying += ' and to_days(occurrence) = to_days(?)'
      arr.push(begin)
    } else {
      querying += ' and occurrence BETWEEN ? AND ?'
      arr.push(begin, end)
    }
  }
  querying = querying.replace('and', 'where')
  const connection = await mysql.createConnection(config.mysqlDB)
  const [rows] = await connection.execute(`SELECT SQL_NO_CACHE COUNT(*) as total FROM ${table} ${querying}`, arr)
  const total = rows[0].total// 总数量
  const pages = Math.ceil(total / pageSize)
  if (page > pages) {
    page = Math.max(1, pages)// 以防没数据
  }
  querying += ' ORDER BY id DESC LIMIT ?, ?'
  arr.push((page - 1) * pageSize, pageSize)
  const [list] = await connection.execute(`SELECT * FROM ${table} ${querying}`, arr)
  await connection.end()
  ctx.body = {
    success: true,
    message: '',
    data: {
      page, total, data: list
    }

'use strict';

const fs = require('fs');
const mysql = require('mysql2');

const conn = mysql.createConnection({
  user: 'root',
  password: '',
  database: 'test',
  host: '127.0.0.1',
  port: '3306',
  ssl: {
    // key: fs.readFileSync('./certs/client-key.pem'),
    // cert: fs.readFileSync('./certs/client-cert.pem')
    ca: fs.readFileSync('./certs/ca-cert.pem')
  }
});

conn.query('select 1+1 as test', function(err, res) {
  console.log(res);
  conn.query('select repeat("a", 100) as test', function(err, res) {
    console.log(res);

const main = (event, context, callback) => {
  //  This is the main execution scope.  All non-system require(...)
  //  statements must be put here.

  //  This missing module is normally not allowed for inline lambda.  But
  //  autorequire will install the module automatically for us.
  const mysql = require('mysql2/promise');

  //  Create pool of database connections.
  const pool = mysql.createPool({
    host: 'iotdb.culga9y9tfvw.us-west-2.rds.amazonaws.com',
    user: 'root',
    password: 'iotattp4me',
    database: 'iotdb',
  });

  function handler(input, context2, callback2) {
    //  This is the main program flow after resolving the missing modules.
    if (input.domain) delete input.domain;  //  TODO: Contains self-reference loop.
    console.log('Input:', JSON.stringify(input, null, 2));
    console.log('Context:', context2);
    //  Don't index response to set desired state.
    if (input.state && input.state.desired) {
      return callback2(null, 'Ignoring response to set desired state');
    }
    let device = null;

'use strict';

const mysql = require('mysql2');
const through2 = require('through2');

const binlogStream = mysql.createBinlogStream({
  serverId: 123, // slave ID, first field in "show slave hosts" sql response
  // you can also specify slave host, username, password and port
  masterId: 0,
  filename: 'mysql-bin.000007',
  binlogPos: 120,
  flags: 1 // 1 = "non-blocking mode"
});

binlogStream.pipe(
  through2.obj((obj, enc, next) => {
    console.log(obj);
    next();
  })
);

function authenticate(params, cb) {
  console.log(params);
  const doubleSha = auth.doubleSha1('pass123');
  const isValid = auth.verifyToken(
    params.authPluginData1,
    params.authPluginData2,
    params.authToken,
    doubleSha
  );
  if (isValid) {
    cb(null);
  } else {
    // for list of codes lib/constants/errors.js
    cb(null, { message: 'wrong password dude', code: 1045 });
  }
}

function authenticate(params, cb) {
  console.log(params);
  const doubleSha = auth.doubleSha1('pass123');
  const isValid = auth.verifyToken(
    params.authPluginData1,
    params.authPluginData2,
    params.authToken,
    doubleSha
  );
  if (isValid) {
    cb(null);
  } else {
    // for list of codes lib/constants/errors.js
    cb(null, { message: 'wrong password dude', code: 1045 });
  }
}