安装数据库依赖 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))
})()