NodeJs中MySQL数据库的使用

1. nodejs安装mysql模块

mysql 8 之前的版本中加密规则是mysql_native_password,而在之后版本，加密规则是caching_sha2_password ，node不支持mysql 8，连接时会报如下错误

Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client

1.1 node不支持mysql 8的解决办法

ALTER USER ‘root’@‘localhost’ IDENTIFIED BY ‘root’ PASSWORD EXPIRE NEVER;

ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘root’;

FLUSH PRIVILEGES;

1.2 nodejs项目中引入mysql2模块

npm i mysql2

1.3 mysql2模块在项目中的使用

const mysql = require('mysql2')
const db = mysql.createPool({
    host: 'XXX',
    user: 'root',
    password: 'root',
    database: 'XXX'
})
db.query('select 1',(err,results) =>{
    if (err){
        return console.log(err.message);
    }
    console.log(results);
})

1.3.1 插入数据

const user = {userId: 8, userName: 'Tom'}
const isql = 'INSERT INTO t (id,c) values (?,?)'
db.query(isql, [user.userId, user.userName], (err, results) => {
    if (err) {
        return console.log(err.message);
    }
    if (results.affectedRows === 1) {
        console.log('插入数据成功!');
    }
})

1.3.2 便捷插入

// id,c 和 表t的列名一致
const user = {id: 9, c: 'Mike'}
const isql = 'INSERT INTO t SET ? '
db.query(isql, user, (err, results) => {
    if (err) {
        return console.log(err.message);
    }
    if (results.affectedRows === 1) {
        console.log('插入数据成功!');
    }
})

1.3.3 更新数据

const user = {id: 9, c: 'Mike1'}
const isql = 'UPDATE t SET c = ? where id = ?'
db.query(isql, [user.c,user.id], (err, results) => {
    if (err) {
        return console.log(err.message);
    }
    if (results.affectedRows === 1) {
        console.log('更新数据成功!');
    }
})

1.3.4 更新数据的便捷方式

const user = {id: 8, c: 'Mike1'}
const isql = 'UPDATE t SET  ? where id = ?'
db.query(isql, [user,user.id], (err, results) => {
    if (err) {
        return console.log(err.message);
    }
    if (results.affectedRows === 1) {
        console.log('更新数据成功!');
    }
})

1.3.5 删除数据

const user = {id: 8}
const isql = 'DELETE FROM  t where id = ?'
db.query(isql, user.id, (err, results) => {
    if (err) {
        return console.log(err.message);
    }
    if (results.affectedRows === 1) {
        console.log('删除数据成功!');
    }
})