koa使用数据库mysql-pro/事务

koa使用数据库(mysql-pro,此模块可以使用事务,占位符等功能)

    //核心代码
    const koa = require('koa');
    const pathlib = require('path');
    const router = require('koa-router')
    const mysql = require('mysql-pro');

    //使用new来初始化mysql
    const db = new mysql({
        mysql:{
            host:'localhost',
            port:3306,
            user:'root',
            password:'root',
            database:'koadb'
        }
    });
    //初始化router
    let r1 = router();
    let server = new koa();//初始化koa
    server.listen(8087);
    server.use(r1.routes());
    r1.get('/user',async ctx=>{
        let id = ctx.request.query.id;//http://localhost:8087/user?id=1获取?后面的参数id的值
        try{
            await db.startTransaction();//开启事务
            let data = await db.executeTransaction('select * from user');
            let query = await db.executeTransaction('select * from user where id=?',[id]);//占位符,防止sql注入
            await db.stopTransaction();//关闭事务,此处应该是提交事务
            ctx.response.body=[data,query];
        }catch(e){
            ctx.response.body='出错';
        }
    })    

知乎实战项目(往数据库导入数据)

    //将数据导入mysql的核心代码
    const fs=require('fs');//文件模块
    const Mysql=require('mysql-pro');//数据库模块

    //创建数据库连接信息
    const db=new Mysql({
    mysql: {
        host: 'localhost',
        port: 3306,
        user: 'root',
        password: 'root',
        database: 'zhihu'
    }
    });

    //读取.topics数据文件,读出为二进制文件,toString方法转化为string;使用json.parse函数将json字符串转化为对象
    const arr=JSON.parse(fs.readFileSync('.topics').toString());

    //创建数据库的映射对象,以及初始ID
    let topics={}, topic_ID=1;
    let authors={}, author_ID=1;
    let questions={}, question_ID=1;
    let answers={}, answer_ID=1;

    // console.log(arr[0]);

    //循环读取的数据文件
    arr.forEach(question=>{
    
    //topic,每个问题的标签,每个问题有多个标签,我们的数据库表中,topic表只有id与title
    question.topics=question.topices.map(json=>{
        let {title}=json;//解构赋值,json中有title与url,此处获取title
        title=title.replace(/^\s+|\s+$/g, '');//将title中前后空格处理掉

        //判读如果topics中没有title,便设置该title对应的id即topic_ID++;
        if(!topics[title]){
        topics[title]=topic_ID++;
        }

        return topics[title];//return 当前question中的topics的title的值
    }).join(',');//将topics用逗号分隔,返回字符串
    
    //author,作者,取最佳回答中的作者与其他回答中的所有作者,此处用剩余参数(见1-11笔记);
    [question.bestAnswer.author, ...question.answers.map(answer=>answer.author)].forEach((author,index)=>{
        let old_id=author.id;//最佳答案的作者id
        
        //判读如果authors中没有当前作者id,便设置该作者的id对应的author对象;
        if(!authors[old_id]){
        authors[author.id]=author;
        author.id=author_ID++;//把原author对象的id设置为我们的定义的ID
        
        if(index==0){
            //delete 操作符用于删除对象的某个属性；如果没有指向这个属性的引用，那它最终会被释放。
            delete question.bestAnswer.author;//删掉原有的对象
            question.bestAnswer.author_ID=author.id;//用对象ID代替
        }else{
            delete question.answers[index-1].author;
            question.answers[index-1].author_ID=author.id;
        }

        }

        return authors[old_id];//return 原始ID的值
    });
    
    

    //question
    let ID=question_ID;
    questions[question_ID++]=question;//直接将问题复制给id
    //  console.log(questions);
    

    //answers,同上,循环赋值给id
    [question.bestAnswer, ...question.answers].forEach(answer=>{
        answer.id=answer_ID;
        answer.question_ID=ID;
        answers[answer_ID++]=answer;
    });
    
    });

    (async()=>{
    function dataJoin(...args){
        return "('"+args.map(item=>{
        item=item||'';
        item=item.toString().replace(/'/g, '\\\'');
        
        return item;
        }).join("','")+"')";
    }

    //topics
    let aTopics=[];
    for(let title in topics){
        let ID=topics[title];

        aTopics.push(dataJoin(ID, title));
    }
    let topic_sql=`INSERT INTO topics VALUES${aTopics.join(',')}`;

    //authors
    let aAuthors=[];
    for(let id in authors){
        let author=authors[id];
        aAuthors.push(dataJoin(author.id, author.type, author.name, author.gender, author.userType, author.img_url, author.headline, author.followerCount));
    }

    
    let author_sql=`INSERT INTO author VALUES${aAuthors.join(',')}`;
    
    //questions
    let aQuestions=[];
    for(let ID in questions){
        let question=questions[ID];
        
        //  console.log(ID); 
        aQuestions.push(dataJoin(ID, question.title, question.question_content, question.topics, question.attention_count, question.view_count, question.bestAnswer.id));
    }
    let question_sql=`INSERT INTO question VALUES${aQuestions.join(',')}`;

    //answers
    let aAnswers=[];
    for(let ID in answers){
        let answer=answers[ID];
        // console.log(answer.author_ID);
        
        aAnswers.push(dataJoin(ID,answer.author_ID, answer.question_ID, answer.content, answer.createdTime));
    }
    let answer_sql=`INSERT INTO answer VALUES${aAnswers.join(',')}`;

    //插入数据
    // await db.startTransaction();
    // await db.executeTransaction(topic_sql);
    // await db.executeTransaction(author_sql);
    // await db.executeTransaction(question_sql);
    // await db.executeTransaction(answer_sql);
    // await db.stopTransaction();
    //将数据打印到文件中
    // fs.writeFileSync('topic_sql',topic_sql,'utf8')
    // fs.writeFileSync('author_sql',author_sql,'utf8')
    // fs.writeFileSync('question_sql',question_sql,'utf8')
    // fs.writeFileSync('answer_sql',answer_sql,'utf8')

    console.log('完成');
    })();

扩展

事务
    事务拥有ACID四个特性
        A 原子性;只会全部发生或者全部不发生;
        C 持久性;只要事务提交了,就是永久性生效的;
        I 隔离性;各个事务之间是独立的;
        D 一致性;食事务前后状态是一致的;
控制台命令 >
    node a.js>a.txt
    运行a.js将js中consloe.log的内容打印到a.txt中