Node .js, connect to MySQL, operate on the database, add, delete, modify, and check code cases

npm installs the driver

Installation driver: npm install mysql

Installation driver: npm install mysql --save

Connect to the database

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 operation

Insert operation

// 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!');
});           

output

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

Modify the operation

// 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!');
});           

output

[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!           

Delete the operation

// 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!');
});           

output

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

Query operations

// 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!');
});           

output

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

Related references

NodeJS, with third-party packages, install and uninstall modules, package.json function and version: https://www.toutiao.com/article/7084450878815568391/

NodeJS, http module, url module case, CommonJS module specification, modularity: https://www.toutiao.com/article/7083054220609323520/

NodeJS, module, os os os module, path simplified path, fs file operation module: https://www.toutiao.com/article/7075261760265241118/

NodeJS, Express, a fast, open, and minimalist web development framework based on Node .js: https://www.toutiao.com/article/7072239233926251045/

NodeJS, Koa, a new web framework based on the Node .js platform, is the next generation web development framework: https://www.toutiao.com/article/7072250917642535458/