Node.js，连接MySQL，对数据库进行操作，增、删、改、查代码案例

npm安装驱动

安装驱动：npm install mysql

安装驱动：npm install mysql --save

连接数据库

main.js

// 1、调用MySQL模块
var mysql = require('mysql');

// 2、创建connection
var connection = mysql.createConnection({
  host: '127.0.0.1',
  user: 'root',
  password: 'root1234',
  port: '3306',
  database: 'database',
});

// 3、连接connection
connection.connect(function (error) {
  if (error) {
    console.log('[query] - :' + error);
    return;
  }
  console.log('[connection connect]  succeed!');
});

// 4、执行SQL
let sql = "select * from manufactor";
connection.query(sql, function (error, result, fields) {
  if (error) {
    throw error;
  }
  // console.log('result', result);
  console.log('result.length', result.length);
  if (result.length > 0) {
    result.forEach(item => {
      console.log(item.id, JSON.stringify(item));
    });
  }
});

// 5、关闭connection
connection.end(function (error) {
  if (error) {
    return;
  }
  console.log('[connection end] succeed!');
});           

CURD操作

插入操作

// 1、调用MySQL模块
var mysql = require('mysql');

// 2、创建connection
var connection = mysql.createConnection({
  host: '127.0.0.1',
  user: 'root',
  password: 'root1234',
  port: '3306',
  database: 'database',
});

// 3、连接connection
connection.connect(function (error) {
  if (error) {
    console.log('[query] - :' + error);
    return;
  }
  console.log('[connection connect]  succeed!');
});

// 4、执行SQL
let addSql = 'INSERT INTO food_unit(id,unit,name) VALUES(?,?,?)';
let addSqlParams = [1, 'g', '克'];
connection.query(addSql, addSqlParams, function (error, result) {
  if (error) {
    throw error;
  }
  console.log('result:', result);
  console.log('result.insertId:', result.insertId);
});

// 5、关闭connection
connection.end(function (error) {
  if (error) {
    return;
  }
  console.log('[connection end] succeed!');
});           

输出

[connection connect]  succeed!
result: OkPacket {
  fieldCount: 0,
  affectedRows: 1,
  insertId: 1,
  serverStatus: 2,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0
}
result.insertId: 1           

修改操作

// 1、调用MySQL模块
var mysql = require('mysql');

// 2、创建connection
var connection = mysql.createConnection({
  host: '127.0.0.1',
  user: 'root',
  password: 'root1234',
  port: '3306',
  database: 'database',
});

// 3、连接connection
connection.connect(function (error) {
  if (error) {
    console.log('[query] - :' + error);
    return;
  }
  console.log('[connection connect]  succeed!');
});

// 4、执行SQL
let updateSql = 'UPDATE food_unit set unit=?,name=? where id=?';
let updateSqlParams = ['kg','千克',1];
connection.query(updateSql,updateSqlParams,function (error, result) {
  if (error) {
    throw error;
  }
  console.log('result:',result);   
  console.log('result.affectedRows:',result.affectedRows);        
});

// 5、关闭connection
connection.end(function (error) {
  if (error) {
    return;
  }
  console.log('[connection end] succeed!');
});           

输出

[connection connect]  succeed!
result: OkPacket {
  fieldCount: 0,
  affectedRows: 1,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '(Rows matched: 1  Changed: 1  Warnings: 0',
  protocol41: true,
  changedRows: 1
}
result.affectedRows: 1
[connection end] succeed!           

删除操作

// 1、调用MySQL模块
var mysql = require('mysql');

// 2、创建connection
var connection = mysql.createConnection({
  host: '127.0.0.1',
  user: 'root',
  password: 'root1234',
  port: '3306',
  database: 'database',
});

// 3、连接connection
connection.connect(function (error) {
  if (error) {
    console.log('[query] - :' + error);
    return;
  }
  console.log('[connection connect]  succeed!');
});

// 4、执行SQL
let deleteSql = 'DELETE FROM food_unit where id=?';
let deleteSqlParams = [1];
connection.query(deleteSql, deleteSqlParams, function (error, result) {
  if (error) {
    throw error;
  }
  console.log('result:', result);
  console.log('result.affectedRows:', result.affectedRows);
});

// 5、关闭connection
connection.end(function (error) {
  if (error) {
    return;
  }
  console.log('[connection end] succeed!');
});           

输出

[connection connect]  succeed!
result: OkPacket {
  fieldCount: 0,
  affectedRows: 1,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0
}
result.affectedRows: 1           

查询操作

// 1、调用MySQL模块
var mysql = require('mysql');

// 2、创建connection
var connection = mysql.createConnection({
  host: '127.0.0.1',
  user: 'root',
  password: 'root1234',
  port: '3306',
  database: 'database',
});

// 3、连接connection
connection.connect(function (error) {
  if (error) {
    console.log('[query] - :' + error);
    return;
  }
  console.log('[connection connect]  succeed!');
});

// 4、执行SQL
let selectSql = 'SELECT * FROM food_unit where id=?';
let selectSqlParams = [1];
connection.query(selectSql, selectSqlParams, function (error, result) {
  if (error) {
    throw error;
  }
  console.log('result:', result);
  if (result.length > 0) {
    result.forEach(item => {
      // 1 {"id":1,"unit":"kg","name":"千克"}
      console.log(item.id, JSON.stringify(item));
    });
  }
});

// 5、关闭connection
connection.end(function (error) {
  if (error) {
    return;
  }
  console.log('[connection end] succeed!');
});           

输出

[connection connect]  succeed!
result: [ RowDataPacket { id: 1, unit: 'g', name: '克' } ]
1 {"id":1,"unit":"g","name":"克"}
[connection end] succeed!           

相关参考内容

NodeJS，用第三方包，安装及卸载模块，package.json作用及版本：https://www.toutiao.com/article/7084450878815568391/

NodeJS，http模块、url模块案例，CommonJS模块规范，模块化：https://www.toutiao.com/article/7083054220609323520/

NodeJS，模块，os操作系统模块、path简化路径、fs文件操作模块：https://www.toutiao.com/article/7075261760265241118/

NodeJS，Express，基于Node.js的快速、开放、极简的Web开发框架：https://www.toutiao.com/article/7072239233926251045/

NodeJS，Koa，基于Node.js平台新的web框架，是下一代web开发框架：https://www.toutiao.com/article/7072250917642535458/