Node.js将一个文件夹的文件装载到MySQL数据库BLOB列,并将BLOB列下载到另一个文件夹

﹏ヽ暗。殇╰゛Y 2023-09-29 09:54 7阅读 0赞

/*
     DDL:
     CREATE TABLE `bindata` (
     `id` INT NOT NULL AUTO_INCREMENT,
     `data` BLOB,
     PRIMARY KEY (`id`)
     ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
     */
    
    /* global Buffer */
    
    const fs = require('fs');
    const mysql = require("mysql");
    
    pool = mysql.createPool({
        "connectionLimit": 10,
        "host": "localhost",
        "port": 3306,
        "user": "root",
        "password": "root",
        "database": "blob",
        "timezone": 'utc',
        // Enables query format like this:
        // `connection.query("UPDATE x SET col=:v1" , { v1: 999 }, ...`
        //
        // NOTE: How to insert binary data:
        // ```
        // // Read BLOB:
        // pool.query(`SELECT * FROM example`, function(err, res) {
        //   const buf = new Buffer(res[0].data); // `data` column type is BLOB!
        //   // Write BLOB:
        //   pool.query("INSERT INTO example(data) VALUES(BINARY(:buf))", { buf }, ...);
        // }
        // ```
        queryFormat: function (query, values) {
            if (!values)
                return query;
            return query.replace(/\:(\w+)/g, function (txt, key) {
                if (values.hasOwnProperty(key)) {
                    return this.escape(values[key]);
                }
                return txt;
            }.bind(this));
        }
    });
    
    // directory path
    const dir = './InputFiles/';
    
    // list all files in the directory
    try {
        const files = fs.readdirSync(dir);
    
        // files object contains all files names
        // log them on console
        files.forEach(file => {
            console.log(file);
            // Read buffer of an image file:
            const data = readImageFile("./InputFiles/" + file); // `data`'s type is Buffer
            const name = file.toString();
            pool.query("INSERT INTO `bindata`(name,data) VALUES(:name,BINARY(:data))", {name, data}, function (err, res) {
                if (err)
                    throw err;
                console.log("BLOB data inserted!");
            });
    
        });
    
    } catch (err) {
        console.log(err);
    }
    
    
    function readImageFile(file) {
        // read binary data from a file:
        const bitmap = fs.readFileSync(file);
        const buf = Buffer.from(bitmap);
        return buf;
    }

/*
     DDL:
     CREATE TABLE `bindata` (
     `id` INT NOT NULL AUTO_INCREMENT,
     `data` BLOB,
     PRIMARY KEY (`id`)
     ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
     */
    
    const fs = require("fs");
    const mysql = require("mysql");
    
    pool = mysql.createPool({
        "connectionLimit": 10,
        "host": "localhost",
        "port": 3306,
        "user": "root",
        "password": "root",
        "database": "blob",
        "timezone": 'utc',
        // Enables query format like this:
        // `connection.query("UPDATE x SET col=:v1" , { v1: 999 }, ...`
        //
        // NOTE: How to insert binary data:
        // ```
        // // Read BLOB:
        // pool.query(`SELECT * FROM example`, function(err, res) {
        //   const buf = new Buffer(res[0].data); // `data` column type is BLOB!
        //   // Write BLOB:
        //   pool.query("INSERT INTO example(data) VALUES(BINARY(:buf))", { buf }, ...);
        // }
        // ```
        queryFormat: function (query, values) {
            if (!values)
                return query;
            return query.replace(/\:(\w+)/g, function (txt, key) {
                if (values.hasOwnProperty(key)) {
                    return this.escape(values[key]);
                }
                return txt;
            }.bind(this));
        }
    });
    
    
    const outputfile = "output.png";
    
    
    // Check to read it from DB:
    pool.query("select * from `bindata`", function (err, res) {
        if (err)
            throw err;
        Object.keys(res).forEach(function (key) {
            var row = res[key];
            console.log(row.name);
            const data = row.data;
            console.log("BLOB data read!");
            // Converted to Buffer:
            const buf = new Buffer(data, "binary");
            // Write new file out:
            fs.writeFileSync("DownloadFiles/" + row.name, buf);
            console.log("New file output:", row.name);
        });
    
    });