我可以动态地将一个数组插入到我从正则表达式匹配生成的 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!");

});

Answer 1

要插入多条记录，SQL 格式类似于：INSERT INTO table_name (column_name_1, column_name_2, column_name_3)VALUES&nbsp; &nbsp; (?, ?, ?),&nbsp; &nbsp; (?, ?, ?),&nbsp; &nbsp; (?, ?, ?)&nbsp; &nbsp; ...;所以我们需要按照上面的格式构造查询，根据它我已经用更改重写了你的部分代码var tweets__modified = tweets__contentText.match(/\b(\w+)\b/g);console.log(tweets__modified);// creating a string for numbers of records we want to createvar sql_insert_statement = tweets__modified.map((record) => '(?, ?, ?, ?, ?, ?, ?, ?)').join(', ');// appending the above string to create a final SQL queryvar 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&nbsp;// we have all the columns data for a record&nbsp;// times the number of recrodsvar insertStatementItems = tweets__modified.reduce((acc, record) => {&nbsp;&nbsp; &nbsp; acc = [...acc, record, tweets__contentText, tweets__conversationId, tweets__replies, tweets__retweets, tweets__favorites, tweets__dateTime, tweets__tweetId];&nbsp; &nbsp; return acc; // was missing}, []);console.log({ insertStatement, insertStatementItems });// Insert data of current row into databasedb.query(insertStatement, insertStatementItems, (err, results, fields) => {&nbsp; &nbsp; if (err) {&nbsp; &nbsp; &nbsp; &nbsp; console.log("Unable to insert item at row ", i + 1);&nbsp; &nbsp; &nbsp; &nbsp; return console.log(err);&nbsp; &nbsp; }});注意：但是请注意，您的代码还有另一个问题，如果您期望console.log("All items stored into database successfully!");在所有插入操作之后打印这一行，那是错误的。由于它是异步代码，因此在执行任何操作之前将首先打印此行。我添加了两段代码来解决这个问题。如果您的环境支持异步/等待，那么这段代码可能对您有所帮助const fileName = "items.csv";csvtojson().fromFile(fileName).then(async (source) => {&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; // Console log initial CSV data&nbsp; &nbsp; // console.log(source);&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; db_insert_promises = []&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; for (var i = 0; i < source.length; i++) {&nbsp; &nbsp; &nbsp; &nbsp; var tweets__contentText = source[i]["tweets__contentText"],&nbsp; &nbsp; &nbsp; &nbsp; tweets__conversationId = source[i]["tweets__conversationId"],&nbsp; &nbsp; &nbsp; &nbsp; tweets__replies = source[i][" tweets__replies"],&nbsp; &nbsp; &nbsp; &nbsp; tweets__retweets = source[i]["tweets__retweets"],&nbsp; &nbsp; &nbsp; &nbsp; tweets__favorites = source[i]["tweets__favorites"],&nbsp; &nbsp; &nbsp; &nbsp; tweets__dateTime = source[i]["tweets__dateTime"],&nbsp; &nbsp; &nbsp; &nbsp; tweets__tweetId = source[i]["tweets__tweetId"]&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; var tweets__modified = tweets__contentText.match(/\b(\w+)\b/g);&nbsp; &nbsp; &nbsp; &nbsp; console.log(tweets__modified);&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; var sql_insert_statement = tweets__modified.map((record) => '(?, ?, ?, ?, ?, ?, ?, ?)').join(', ');&nbsp; &nbsp; &nbsp; &nbsp; var insertStatement = `INSERT INTO ctdata values ${sql_insert_statement}`;&nbsp; &nbsp; &nbsp; &nbsp; var insertStatementItems = tweets__modified.reduce((acc, record) => {&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; acc = [...acc, record, tweets__contentText, tweets__conversationId, tweets__replies, tweets__retweets, tweets__favorites, tweets__dateTime, tweets__tweetId];&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return acc; // was missing&nbsp; &nbsp; &nbsp; &nbsp; }, []);&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; // Insert data of current row into database&nbsp; &nbsp; &nbsp; &nbsp; db_insert_promise_for_tweet = await new Promise(function(resolve, reject) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; db.query(insertStatement, insertStatementItems, (err, results, fields) => {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (err) return reject(err);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return resolve(results, fields);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; });&nbsp; &nbsp; &nbsp; &nbsp; });&nbsp; &nbsp; }&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; console.log("All items stored into database successfully!");}).catch(console.error);如果您的环境不支持异步/等待，那么这段代码可能会对您有所帮助const fileName = "items.csv";csvtojson().fromFile(fileName).then(source => {&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; // Console log initial CSV data&nbsp; &nbsp; // console.log(source);&nbsp; &nbsp; db_insert_promises = []&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; for (var i = 0; i < source.length; i++) {&nbsp; &nbsp; &nbsp; &nbsp; var tweets__contentText = source[i]["tweets__contentText"],&nbsp; &nbsp; &nbsp; &nbsp; tweets__conversationId = source[i]["tweets__conversationId"],&nbsp; &nbsp; &nbsp; &nbsp; tweets__replies = source[i][" tweets__replies"],&nbsp; &nbsp; &nbsp; &nbsp; tweets__retweets = source[i]["tweets__retweets"],&nbsp; &nbsp; &nbsp; &nbsp; tweets__favorites = source[i]["tweets__favorites"],&nbsp; &nbsp; &nbsp; &nbsp; tweets__dateTime = source[i]["tweets__dateTime"],&nbsp; &nbsp; &nbsp; &nbsp; tweets__tweetId = source[i]["tweets__tweetId"]&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; var tweets__modified = tweets__contentText.match(/\b(\w+)\b/g);&nbsp; &nbsp; &nbsp; &nbsp; console.log(tweets__modified);&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; var sql_insert_statement = tweets__modified.map((record) => '(?, ?, ?, ?, ?, ?, ?, ?)').join(', ');&nbsp; &nbsp; &nbsp; &nbsp; var insertStatement = `INSERT INTO ctdata values ${sql_insert_statement}`;&nbsp; &nbsp; &nbsp; &nbsp; var insertStatementItems = tweets__modified.reduce((acc, record) => {&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; acc = [...acc, record, tweets__contentText, tweets__conversationId, tweets__replies, tweets__retweets, tweets__favorites, tweets__dateTime, tweets__tweetId];&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return acc; // was missing&nbsp; &nbsp; &nbsp; &nbsp; }, []);&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; // Insert data of current row into database&nbsp; &nbsp; &nbsp; &nbsp; db_insert_promise_for_tweet = new Promise(function(resolve, reject) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; db.query(insertStatement, insertStatementItems, (err, results, fields) => {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (err) return reject(err);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return resolve(results, fields);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; });&nbsp; &nbsp; &nbsp; &nbsp; });&nbsp; &nbsp; &nbsp; &nbsp; db_insert_promises.push(db_insert_promise_for_tweet);&nbsp; &nbsp; }&nbsp; &nbsp; return Promise.all(db_insert_promises);}).then((result_of_all_insert_query) => {&nbsp; &nbsp; console.log({ result_of_all_insert_query });&nbsp; &nbsp; console.log("All items stored into database successfully!");}).catch(console.error);