Node+Express+Mysql搭建API接口平台

    npm i express   //局部安装
    npm i express -g //全局安装

    npm i express-generator -g //全局安装

    express [name] //全局安装,name为项目名字
    cd [name]

    npm i (注:npm i 是 npm install 的简写)

    1.npm start 
    2.访问localhost:3000或127.0.0.1

    //安装
    npm i nodemon -g
    //启动项目
    npx nodemon app.js
    (//注:启动需完成以下操作 删除app.js下最后一行(model.express = app) 并添加app.listen('3000'))
    
    //也可以(可忽略)
    npm start (//注:启动需完成以下操作 修改package.json中
        "scripts": {
            "start": "npx nodemon app.js"
        }
    )

    npm i mysql

    const mysql = require('mysql')
    module.exports = {
        dbconfig: {
            //配置mysql连接属性
            host: 'localhost',//ip地址
            user: 'root', //用户名
            password: '123456', //密码
            database: 'test'
        },
        //连接方法(封装连接)
        sqlConnect: function (sql, arr, callback) {
            //创建连接池
            const pool = mysql.createPool(this.dbconfig)
            //连接数据库
            pool.getConnection((err, conn) => {
                if (err) {
                    console.log('连接失败')
                    return;
                }
                //调用事件驱动回调
                conn.query(sql, arr, callback)
                //释放连接
                conn.release()
            })
        }
    }

    router.get('/', function (req, res, next) {
    //调用db中sqlConnect方法
        db.sqlConnect('select * from cate', [], (err, result) => {
            if (err) return res.send(err)
            if (result.length != 0) {
            return res.send(result)
            } else {
            return res.send('没有记录')
            }
        })
    });

    var db = require('../util/db')

    exports.getCates = (req,res) =>{
        //调用db中sqlConnect方法
    db.sqlConnect('select * from cate', [], (err, result) => {
        if (err) return res.send(err)
        if (result.length != 0) {
        return res.send(result)
        } else {
        return res.send('没有记录')
        }
    })
    }

    exports.getPostCates = (req,res) =>{
    //调用db中sqlConnect方法
    db.sqlConnect('select * from post where cate_id = ?', [req.query.id], (err, result) => {
        if (err) return res.send('连接失败')
        if (result.length != 0) {
        return res.send(result)
        } else {
        return res.send('没有记录')
        }
    })
    }

    //随机生成4位验证码
    function rand(min, max) {
        return Math.floor(Math.random() * (max - min)) + min
    }

    var validatePhoneCode = []
    //验证码1分钟后将删除
    function Calculator(phone) {
        let timer = setTimeout(() => {
            console.log(phone + ':' + '验证码已删除')
            validatePhoneCode = validatePhoneCode.filter(item => item.phone != phone)
            clearTimeout(timer)
        }, 60000)

    }
    //检验手机号是否有验证码
    function userPhoneHasCode(phone) {
        for (let v of validatePhoneCode) {
            if (v.phone === phone) {
                return true
            }
        }
        return false
    }

    //验证码接口
    exports.sendRand = (req, res) => {
        let { phone } = req.body
        let random = rand(1000, 9999)
        if (!userPhoneHasCode(phone)) {
            validatePhoneCode.push({ phone, random })
            Calculator(phone)
            return res.send({
                code: random,
                msg: '验证码发送成功'
            })
        } else {
            return res.send({
                err: '403',
                msg: '验证码发送失败'
            })
        }

    }

//检验手机号是否有验证码
function userPhoneHasCode(phone) {
    for (let v of validatePhoneCode) {
        if (v.phone === phone) {
            return true
        }
    }
    return false
}

//判断是否登陆成功
function loginPhoneAndCode(phone, code) {
    console.log(phone, code)
    for (let v of validatePhoneCode) {
        if (v.phone == phone && v.code == code) {
            return true
        }
    }
    return false
}

//登陆接口
exports.login = (req, res) => {
    let { phone, code } = req.body
    if (userPhoneHasCode(phone)) {
        if (loginPhoneAndCode(phone, code)) {
            return res.send({
                code: '200',
                msg: '登陆成功'
            })
        } else {
            return res.send({
                code: '200',
                msg: '登陆失败'
            })
        }
    }
    res.send({
        code: '200',
        msg: '未获取验证码,无法登陆'
    })
}

    npm i @alicloud/pop-core --save

    //判断是否是第一次登陆并注册
    async function phoneAndCodeIsReg(phone) {
        let result = await db.sqlConnectSync('select * from user where phone = ?', [phone])
        if (result != 0) {
            return false
        }
        phoneReg(phone)
        return true
    }

    //第一次登陆并注册
    async function phoneReg(phone) {
        let result = await db.sqlConnectSync('insert into user set ?', { phone })
        if (result.affectedRows != 0) {
            return false
        }
        return true
    }


    //登陆接口
    exports.login = (req, res) => {
        let { phone, code } = req.body
        if (userPhoneHasCode(phone)) {
            if (loginPhoneAndCode(phone, code)) {
                phoneAndCodeIsReg(phone)
                return res.send({
                    code: '200',
                    msg: '登陆成功'
                })
            } else {
                return res.send({
                    code: '200',
                    msg: '登陆失败'
                })
            }
        }
        res.send({
            code: '200',
            msg: '未获取验证码,无法登陆'
        })
    }

    //注册用户基本信息和用户登陆信息
    async function userInfoReg(user) {
        console.log(user)
        let { username, password, user_id, name, age, sex, job, email, phone, create_time } = user
        //是否已用手机注册
        if (phoneAndCodeIsReg(phone)) {
            let result = await db.sqlConnectSync('update user set username = ?,password = ?,phone = ?,email = ?,create_time = ? where phone = ?', [username, password, phone, email, create_time, phone])
            if (result.affectedRows != 0) {
                console.log(username + ':已填写信息')
            }
            if (result instanceof Error) {
                console.log(username + ':填写信息失败')
                return false
            }

        }
        result = await db.sqlConnectSync('insert into user_info set ?', { user_id, name, age, sex, job })
        if (result.affectedRows != 0) {
            console.log(username + ':用户基本信息注册成功')
            return true
        }
        return false
    }

    //注册用户信息接口
    exports.login = (req, res) => {
        let user_info = req.body.user
        if (userInfoReg(user_info)) {
            return res.send({
                code: '200',
                msg: '信息注册成功'
            })
        }
        return res.send({
            code: '200',
            msg: '信息注册失败'
        })
    }

    // 通过手机号/用户名和密码登陆
    exports.loginPhoneorUnameAndPwd = async (req, res) => {
        let { username, password } = req.body
        if (!username && password) {
            return res.send({
                code: '200',
                msg: '用户名和密码不能为空'
            })
        }
        let result = await db.sqlConnectSync('select * from user where username = ? and password = ? or phone = ? password = ?', [username, password, username, password])
        if (result.length == 0) {
            return res.send({
                code: '200',
                msg: '登陆失败,用户名或密码错误'
            })
        }
        return res.send({
            code: '200',
            msg: '登陆成功'
        })
    }

    //获取用户基本信息
    async function getUserInfo(user_id) {
        let result = await db.sqlConnectSync('select * from user_info where user_id = ?', [user_id])
        if (result?.length != 0) {
            return result
        } else {
            return res.send({
                err: '403',
                msg: '查询失败'
            })
        }
    }

    //修改用户基本信息
    async function updateUserInfo(user) {
        let { user_id, name, age, sex, job } = user
        let result = await db.sqlConnectSync('update user_info set name = ?,age = ?,sex = ?,job = ? where user_id = ?', [name, age, sex, job, user_id])
        if (result?.affectedRows != 0) {
            return true
        } else {
            return false
        }
    }

    npm i multer --save 

    const multer  = require('multer')
    const upload = multer({ dest: './public/uploads/' }).single('file')

    //用户文件上传
    exports.userImgUpload = async (req, res) => {
        res.set({
            'content-type': 'application/json; charset=utf-8'
        })
        //判断文件是否为空
        if (req.file.length === 0) {
            res.render("error", { message: "上传文件不能为空！" });
            return
        } else {
            let file = req.file
            //修改名字，第一个参数为旧路径，第二个参数为新路径（注意：旧路径要和上面的dest保持一致）
            fs.renameSync('./public/uploads/' + file.filename, './public/uploads/' + file.originalname)
            if (file) {
                let { user_id } = req.query
                let img_url = 'http://localhost/uploads/' + file.originalname
                let result = await db.sqlConnectSync('update user set userpic = ? where id = ?', [img_url, user_id])
                if (result?.affectedRows != 0) {
                    return res.send({
                        code: '200',
                        msg: '上传成功'
                    })
                } else {
                    return res.send({
                        code: '500',
                        msg: result
                    })
                }
            }
        }
    }

    //视频上传
    exports.postUpload = async (req, res) => {
        let { user_id, title, url, path, isopen, create_time, posting } = req.body
        let arr = [user_id, title, url, path, isopen, create_time, posting]
        db.sqlConnect('insert into post(user_id, title, url, path, isopen, create_time, posting) values(?,?,?,?,?,?,?)', arr, (err, result) => {
            if (err) {
                console.log(err)
                return
            }
            console.log(result)
            if (result?.affectedRows != 0) {
                return res.send({
                    code: '200',
                    msg: '上传成功'
                })
            }
            return res.send({
                code: '200',
                msg: '上传失败'
            })
        })

    }

    var db = require('../util/db')

    //判断用户是否已关注
    getUserIsFollowed = (user_id, follow_id) => {
        db.sqlConnect('select * from follow where user_id = ? and follow_id = ?', [user_id, follow_id], (err, result) => {
            if (err) {
                console.log(err)
                return
            }
            if (result?.affectedRows != 0) {
                return true
            }
            return false
        })
    }

    //用户关注接口
    getUserFollowed = (req, res) => {
        let { user_id, follow_id } = req.body

        if (!getUserIsFollowed) {
            db.sqlConnect('insert into follow(user_id,follow_id) values(?,?)', [user_id, follow_id], (err, result) => {
                if (err) {
                    console.log(err)
                    return
                }
                if (result?.affectedRows != 0) {
                    return res.send({
                        code: '200',
                        msg: '用户关注成功'
                    })
                }
                return res.send({
                    code: '200',
                    msg: '用户关注失败'
                })
            })
        } else {
            return res.send({
                code: '200',
                msg: '用户已关注'
            })
        }

    }