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

最新推荐文章于 2023-08-22 20:08:16 发布

allway2

最新推荐文章于 2023-08-22 20:08:16 发布

阅读量603

点赞数

文章标签： javascript

本文链接：https://blog.csdn.net/allway2/article/details/124134154

版权

本文介绍了如何使用Node.js和MySQL进行Blob类型数据的处理，包括从文件系统读取图片并插入到`bindata`表中，以及从数据库检索并下载文件。重点在于数据库操作的示例和BLOB数据的处理方式。

摘要由CSDN通过智能技术生成

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

});