node连接查询MySQL数据库

方式

通过mysql库进行连接

实操:普通方式

const express = require('express');
const mysql = require('mysql');

const app = express();

app.listen('3000', () => {
    console.log('server is runing,port is 3000');
});


const db = mysql.createConnection({
    host:       'localhost',
    user:       'root',
    password:   'root',
    database:   'test2'//这行代码在建立数据库之前一定要注释  不然报错； //且取出数据之前一定要取消注释  不然也会报错
});

db.connect((err)=>{
    if (err) throw err;
    console.log('mysql connected');
}); 

app.get('/',(req,res)=>{
    console.log('/');
});

//创建数据库
app.get('/createdb',(req,res)=>{ 
    console.log('/create');
    let sql = "CREATE DATABASE TEST2";
    db.query(sql ,(err,result) => {
        if (err) throw err;
        console.log(result);
        res.send('数据库已建立');
    });
});

//建表
app.get('/createtable',(req,res)=>{ 
    console.log('/createtable');
    let sql = "CREATE TABLE info(id int AUTO_INCREMENT,title VARCHAR(255), body VARCHAR(255), PRIMARY KEY(id))";
    db.query(sql ,(err,result) => {
        if (err) throw err;
        console.log(result);
        res.send('表已建立');
    });
});

//插入数据
app.get('/postdata',(req,res)=>{ 
    console.log('/postdata');
    let data = {
        title: 'title',
        body: 'body'
    };
    let sql = "INSERT INTO info SET ?";
    db.query(sql , data ,(err,result) => {
        if (err) throw err;
        console.log(result);
        res.send('数据已添加');
    });
});

//取出所有数据
app.get('/getdata',(req,res)=>{ 
    console.log('/getdata');
    let sql = "SELECT * FROM info";
    db.query(sql ,(err,result) => {
        if (err) throw err;
        console.log(result);
        res.json(result);
    });
});

//取出一条数据,重点是拼接
app.get('/getdata/:id',(req,res)=>{
    console.log('/getdata');
    let sql = `SELECT * FROM info WHERE id = '${req.params.id}' `;//字符串  一定要加单引号  不然可能引起服务器报错
    db.query(sql ,(err,result) => {
        if (err) throw err;
        console.log(result);
        res.json(result);
    });  
});

//更新数据
app.get('/updatedata/:id',(req,res)=>{ 
    console.log('/getdata');
    let newTitle = 'This is new title!';
    let sql = `UPDATE info SET title = '${newTitle}' WHERE id = '${req.params.id}'`;
    db.query(sql ,(err,result) => {
        if (err) throw err;
        console.log(result);
        res.json('更新数据成功');
    });
});

//删除数据
app.get('/deletedata/:id', (req,res)=>{
    console.log('/deletedata');
    let sql = `DELETE FROM info WHERE id = '${req.params.id}'`;
    db.query(sql, (err,result) => {
        if(err) throw err;
        console.log(result);
        res.json('数据删除成功');
    });
});

//end

第二种:连接池方式(推荐)

1. 单独创建配置文件mysqlConfig.js,内容如下:

   var mysql = require("mysql");
   
   //配置文件
   var pool = mysql.createPool({
       host: '127.0.0.1',
       port: 3306,
       user: 'root',
       password: 'root',
       database: 'toothpaste'
   });
   
   module.exports = pool;

2. 创建mysqlconn.js来放置连接池方法:

   var mysql = require('mysql');
   var pool = require('./mysqlConfig')
   
   
   var query = function(sql,callback){
       pool.getConnection(function(err,conn){
           if(err){
               callback(err,'no-result');
           }else{
               conn.query(sql,function(qerr,vals,fields){
                   //释放连接
                   conn.release();
                   //事件驱动回调
                   callback(qerr,vals,fields);
               });
           }
       });
   };
   
   module.exports = query;

3. 注意事项:一般一个数据库表对应一个js文件,在其中进行相应操作,公出,再在相应的路由中引入使用,
   判断数据库是否在调试中正确连接:

var express = require('express');
const query = require('../mysqlconn');

query(`show tables;`,(err,result)=>{
    if(err){
        console.log(err);
    }else{
        console.log(`db---ok`);
    }
});