nodejs数据持久化--MySQL（1）SQL原生操作

安装数据库依赖 mysql2地址

npm install --save mysql2

配置数据库连接

const mysql = require('mysql2/promise')
// 数据库连接配置
const cfg = {
  host: 'localhost',
  user: 'XXXX',
  password: 'XXXXXX',
  database: 'XXX'
}
const connection = await mysql.createConnection(cfg)
console.log('连接成功')

连接成功

创建数据表

// 创建table
const CREATE_SQL = `CREATE TABLE IF NOT EXISTS test(
  id INT NOT NULL AUTO_INCREMENT,
  message VARCHAR(45) NULL,
  PRIMARY KEY(id)
)`
let ret= await connection.execute(CREATE_SQL)
console.log('create: ', ret)

create:  [ ResultSetHeader {
  fieldCount: 0,
  affectedRows: 0,
  insertId: 0,
  info: '',
  serverStatus: 2,
  warningStatus: 0 },
undefined ]

插入数据

// 插入数据
const INSERT_SQL = `INSERT INTO test(message) VALUES(?)`
const ret = await connection.execute(INSERT_SQL, ['abc'])
console.log('insert: ', ret)

insert:  [ ResultSetHeader {
  fieldCount: 0,
  affectedRows: 1,
  insertId: 2,
  info: '',
  serverStatus: 2,
  warningStatus: 0 },
undefined ]

查询数据

// 查询数据
const SELECT_SQL = `SELECT * FROM test`
const [rows, fields] = await connection.execute(SELECT_SQL)
console.log('rows: ', JSON.stringify(rows))

rows:  [
  {"id":7,"message":"0.673798058185288"},
  {"id":8,"message":"0.8518648744645418"},
  {"id":9,"message":"0.20853542581425888"},
  {"id":10,"message":"0.7010336551877239"},
  {"id":11,"message":"0.8393335355970917"}
]

完整代码

(async () => {
  const mysql = require('mysql2/promise')

  // 数据库连接配置
  const cfg = {
    host: 'localhost',
    user: 'XXXX',
    password: 'XXXXXX',
    database: 'XXX'
  }
  const connection = await mysql.createConnection(cfg)
  console.log('连接成功')

  // 创建table
  const CREATE_SQL = `CREATE TABLE IF NOT EXISTS test(
    id INT NOT NULL AUTO_INCREMENT,
    message VARCHAR(45) NULL,
    PRIMARY KEY(id)
  )`
  ret = await connection.execute(CREATE_SQL)
  console.log('create: ', ret)

  // 插入数据
  const INSERT_SQL = `INSERT INTO test(message) VALUES(?)`
  ret = await connection.execute(INSERT_SQL, [Math.random()])
  console.log('insert: ', ret)

  // 查询数据
  const SELECT_SQL = `SELECT * FROM test`
  const [rows, fields] = await connection.execute(SELECT_SQL)
  console.log('rows: ', JSON.stringify(rows))
})()