Node.JS连接MySQL数据库执行增删改查提示ER_NOT_SUPPORTED_AUTH_MODE: Client does not support；如何在@mysql/xdevapi执行SQL

在Node.JS连接MySQL数据库时，使用mysql模块时会报错 ER_NOT_SUPPORTED_AUTH_MODE: Client does not support 经查是这个MySQL驱动不支持MySQL新的加密方式。

var mysql  = require('mysql');  
 
var connection = mysql.createConnection({     
  host     : 'localhost',     //本机地址
  user     : 'root',          //用户名
  password : '123456',        //密码
  port     : '3306',          //端口号
  database : 'test'           //要连接的数据库
}); 
 
connection.connect();
 
var  sql = 'SELECT * FROM table1';   //查询table1表的所有数据

connection.query(sql,function (err, result) {
        if(err){
          console.log('[SELECT ERROR] - ',err.message);
          return;
        }
 
       console.log('--------------------------SELECT----------------------------');
       console.log(result);
       console.log('------------------------------------------------------------\n\n');  
});

文案1：更改MySQL加密方式

文案出处： https://stackoverflow.com/questions/50093144/mysql-8-0-client-does-not-support-authentication-protocol-requested-by-server

mysql> alter user 'root'@'localhost' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.27 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.08 sec)

文案2：使用@mysql/xdevapi

也可以使用 xdevapi， 安装:

$ npm install @mysql/xdevapi

用 xdevapi 执行SQL

const mysqlx = require('@mysql/xdevapi');

const options = {
  host: 'your server',
  port: 3306,
  password: '12345678',
  user: 'username',
  schema: 'password'
};

;(async () => {
  const session = await mysqlx.getSession(options)
  const schema = session.getSchema(options.schema)

  // 验证Schema
  await schema.existsInDatabase()


  const res = await session.sql(`
    SELECT  id, event, project_id, entity_id, entity_type, create_by, message, create_at
    FROM audit_log
    WHERE create_at > '2021-10-26'
    ORDER BY project_id,create_by,create_at desc
  `).execute();
  
  let rows = res.fetchAll();

  console.log(rows);

  session.close();
})()