javascript - 我可以将一个数组动态插入到我从正则表达式匹配生成的 mysql 中吗？

Question

首先，如果标题不准确，我深表歉意，但我很难找到准确描述我的问题的词。

我正在做一个项目，我试图记录我从刮板编译的 Twitter 信息，目前是 CSV 格式。

我使用 csvtojson 将该信息转换为 JSON，但遇到了问题。

我试图将推文中的每个单词单独分开，然后将 CSV 中该特定行的其余信息应用于该特定单词。

这是“成功”结果的示例：

我只是不确定如何拆分我创建的数组，我可以将每个单词作为新行应用到数组中。我假设我需要分开插入数据的方式，但我不确定该怎么做？

const fileName = "items.csv";

csvtojson().fromFile(fileName).then(source => {

// Console log initial CSV data
// console.log(source);

for (var i = 0; i < source.length; i++) {
    var tweets__contentText = source[i]["tweets__contentText"],
        tweets__conversationId = source[i]["tweets__conversationId"],
        tweets__replies = source[i][" tweets__replies"],
        tweets__retweets = source[i]["tweets__retweets"],
        tweets__favorites = source[i]["tweets__favorites"],
        tweets__dateTime = source[i]["tweets__dateTime"],
        tweets__tweetId = source[i]["tweets__tweetId"]

    var tweets__modified = tweets__contentText.match(/\b(\w+)\b/g);

    console.log(tweets__modified);

    var insertStatement = `INSERT INTO ctdata values(?, ?, ?, ?, ?, ?, ?, ?)`;
    var items = [tweets__modified, tweets__contentText, tweets__conversationId, tweets__replies, tweets__retweets, tweets__favorites, tweets__dateTime, tweets__tweetId];

    // Insert data of current row into database
    db.query(insertStatement, items, (err, results, fields) => {
        if (err) {
            console.log("Unable to insert item at row ", i + 1);
            return console.log(err);
        }
    });
}
console.log("All items stored into database successfully!");
});

标签： javascriptmysqlnode.js

Answer 1

要插入多条记录，SQL 格式类似于：

INSERT INTO table_name (column_name_1, column_name_2, column_name_3)
VALUES
    (?, ?, ?),
    (?, ?, ?),
    (?, ?, ?)
    ...
;

因此，我们需要以上述格式构造查询，据此我已根据更改重写了您的部分代码

var tweets__modified = tweets__contentText.match(/\b(\w+)\b/g);
console.log(tweets__modified);

// creating a string for numbers of records we want to create
var sql_insert_statement = tweets__modified.map((record) => '(?, ?, ?, ?, ?, ?, ?, ?)').join(', ');

// appending the above string to create a final SQL query
var insertStatement = `INSERT INTO ctdata ("tweets__singleWord", "tweets__contentText", "tweets__conversationId", "tweets__replies", "tweets__retweets", "tweets__favorites", "tweets__dateTime", "tweets__tweetId") values ${sql_insert_statement}`;

// creating a SQL query data in which 
// we have all the columns data for a record 
// times the number of recrods
var insertStatementItems = tweets__modified.reduce((acc, record) => { 
    acc = [...acc, record, tweets__contentText, tweets__conversationId, tweets__replies, tweets__retweets, tweets__favorites, tweets__dateTime, tweets__tweetId];
    return acc; // was missing
}, []);

console.log({ insertStatement, insertStatementItems });

// Insert data of current row into database
db.query(insertStatement, insertStatementItems, (err, results, fields) => {
    if (err) {
        console.log("Unable to insert item at row ", i + 1);
        return console.log(err);
    }
});

---

注意：但请注意，您的代码还有另一个问题，如果您希望console.log("All items stored into database successfully!");在所有插入操作之后打印此行，那是错误的。由于它是异步代码，因此在执行任何操作之前将首先打印此行。我添加了两段代码来解决这个问题。

如果您的环境支持异步/等待，那么此代码可能会对您有所帮助

const fileName = "items.csv";

csvtojson().fromFile(fileName)
.then(async (source) => {
    
    // Console log initial CSV data
    // console.log(source);
    
    db_insert_promises = []
    
    for (var i = 0; i < source.length; i++) {
        var tweets__contentText = source[i]["tweets__contentText"],
        tweets__conversationId = source[i]["tweets__conversationId"],
        tweets__replies = source[i][" tweets__replies"],
        tweets__retweets = source[i]["tweets__retweets"],
        tweets__favorites = source[i]["tweets__favorites"],
        tweets__dateTime = source[i]["tweets__dateTime"],
        tweets__tweetId = source[i]["tweets__tweetId"]
        
        var tweets__modified = tweets__contentText.match(/\b(\w+)\b/g);
        console.log(tweets__modified);
        
        var sql_insert_statement = tweets__modified.map((record) => '(?, ?, ?, ?, ?, ?, ?, ?)').join(', ');
        var insertStatement = `INSERT INTO ctdata values ${sql_insert_statement}`;
        var insertStatementItems = tweets__modified.reduce((acc, record) => { 
            acc = [...acc, record, tweets__contentText, tweets__conversationId, tweets__replies, tweets__retweets, tweets__favorites, tweets__dateTime, tweets__tweetId];
            return acc; // was missing
        }, []);
        
        // Insert data of current row into database
        db_insert_promise_for_tweet = await new Promise(function(resolve, reject) {
            db.query(insertStatement, insertStatementItems, (err, results, fields) => {
                if (err) return reject(err);
                return resolve(results, fields);
            });
        });
    }
    
    console.log("All items stored into database successfully!");
})
.catch(console.error);

如果您的环境不支持异步/等待，那么此代码可能会对您有所帮助

const fileName = "items.csv";

csvtojson().fromFile(fileName)
.then(source => {
    
    // Console log initial CSV data
    // console.log(source);

    db_insert_promises = []
    
    for (var i = 0; i < source.length; i++) {
        var tweets__contentText = source[i]["tweets__contentText"],
        tweets__conversationId = source[i]["tweets__conversationId"],
        tweets__replies = source[i][" tweets__replies"],
        tweets__retweets = source[i]["tweets__retweets"],
        tweets__favorites = source[i]["tweets__favorites"],
        tweets__dateTime = source[i]["tweets__dateTime"],
        tweets__tweetId = source[i]["tweets__tweetId"]
        
        var tweets__modified = tweets__contentText.match(/\b(\w+)\b/g);
        console.log(tweets__modified);
        
        var sql_insert_statement = tweets__modified.map((record) => '(?, ?, ?, ?, ?, ?, ?, ?)').join(', ');
        var insertStatement = `INSERT INTO ctdata values ${sql_insert_statement}`;
        var insertStatementItems = tweets__modified.reduce((acc, record) => { 
            acc = [...acc, record, tweets__contentText, tweets__conversationId, tweets__replies, tweets__retweets, tweets__favorites, tweets__dateTime, tweets__tweetId];
            return acc; // was missing
        }, []);
        
        // Insert data of current row into database
        db_insert_promise_for_tweet = new Promise(function(resolve, reject) {
            db.query(insertStatement, insertStatementItems, (err, results, fields) => {
                if (err) return reject(err);
                return resolve(results, fields);
            });
        });
        db_insert_promises.push(db_insert_promise_for_tweet);
    }

    return Promise.all(db_insert_promises);
})
.then((result_of_all_insert_query) => {
    console.log({ result_of_all_insert_query });
    console.log("All items stored into database successfully!");
})
.catch(console.error);