如何将文本文件数据导入MySQL？是否需编写转换脚本？

问题描述 #

我正在开发一个Web应用，只有MySQL控制台访问权限。现在有一份特定格式的文本文件，需要把里面的数据导入已创建的website.Categories和website.Questions表中。我打算写个JavaScript脚本把文本转换成MySQL的INSERT语句再执行，想确认这是不是最简单的实现方式。

数据库表结构 #

CREATE TABLE website.Categories (
    ID int NOT NULL,
    CategoryName varchar(500) NOT NULL,
    PRIMARY KEY (ID)
);
CREATE TABLE website.Questions(
ID int NOT NULL,
Category int,
QuestionText varchar(5000) NOT NULL,
AnswerText varchar(5000) NOT NULL,
PRIMARY KEY (ID)
);

拟实现的伪代码 #

- 逐行读取文本
- 初始化计数器
- 读取第一行，生成`INSERT INTO website.categories VALUES (_____)`语句
- 当当前行不为空时，读取后续每一行，用`?`分割字符串
- 生成`INSERT INTO website.questions VALUES (counter, __question___, __answer___)`语句
- 遇到空行时，计数器递增
- 将下一行作为新分类插入
- 重复上述流程

文本文件内容示例 #

chemistry
what is the formula for hydrogen peroxide?h2o2
what is the state of matter of water at room temperature?liquid
what is the lightest element?hydrogen
which silvery element was used in early thermometers?mercury
what does water turn into when boils?Steam
what element is glass made from?Silicon

geography
what is the capital of Austrailia?Canberra
what is the capital of Turkey?Ankara
what is the capital of Malaysia?Kuala Lumpur
what ocean is to the west of the United States?pacific
what ocean is to the east of Canada?atlantic
Which is the largest country in the world?Russia
Which river flows through Rome?Tiber
In which country is Mount Kilimanjaro?Tanzania
what is the longest river in the world?Nile
on which continent will you find Brazil?South America
On which continent are the Atlas Mountains?Africa

animals
which country is known for having pandas?China
what animal is known for eating bamboo?panda
which animal carries babies in its pouch?kangaroo
what are eggs of salmon called?roe
which animal has stripes?zebra
what is the fastest land animal?Cheetah
how many hearts do octopuses have?3
what is the baby goat called?kid
what do you call an animal that eats plants?herbivore
what is the longest snake in the world?Python
what is a female donkey called?Jenny
Which animal is the Florida Cracker?A Sheep

instruments
which device is used to measure temperature?thermometer
who invented radio?marconi
who invented waste dumpsters?demptser
what gas is usually filled in lighters?butane
what does a manometer measure?Pressure

physics
what unit is sound loudness measured in?decibel
what is the unit of power?watt
which circuit component is known for storing charge?capacitor
what is the unit of electrical resistance?ohm

实现方案分析 #

你的思路完全可行，而且在只有MySQL控制台权限的场景下，这种方式直接易上手。下面给你分析几种可选方案，帮你判断哪种最适合：

1. JavaScript脚本生成INSERT语句（你的方案）

这是最稳妥的选择，不需要依赖服务器额外权限，步骤清晰可控：

• 用Node.js读取文本文件，逐行处理：
  • 维护分类ID、问题ID两个计数器，初始值都设为1
  • 遇到不含?的非空行时，生成分类INSERT语句，记录当前分类ID
  • 遇到带?的行时，拆分问题和答案，生成关联当前分类ID的问题INSERT语句
  • 遇到空行时，重置当前分类ID，等待下一个分类行
• 最后把生成的SQL语句复制到MySQL控制台执行即可

给你一个现成的Node.js脚本示例：

const fs = require('fs');
const path = require('path');

// 替换成你的文本文件路径
const filePath = path.resolve(__dirname, 'questions.txt');
const content = fs.readFileSync(filePath, 'utf8').split('\n');

let categoryId = 1;
let questionId = 1;
let currentCategoryId = null;
const sqlStatements = [];

content.forEach(line => {
  line = line.trim();
  if (!line) {
    currentCategoryId = null;
    return;
  }

  if (!line.includes('?')) {
    // 处理分类行，转义单引号避免SQL语法错误
    const escapedName = line.replace(/'/g, "\\'");
    sqlStatements.push(`INSERT INTO website.Categories VALUES (${categoryId}, '${escapedName}');`);
    currentCategoryId = categoryId;
    categoryId++;
  } else {
    // 处理问答行，拆分并转义特殊字符
    const [question, answer] = line.split('?');
    const escapedQuestion = question.replace(/'/g, "\\'");
    const escapedAnswer = answer.replace(/'/g, "\\'");
    sqlStatements.push(`INSERT INTO website.Questions VALUES (${questionId}, ${currentCategoryId}, '${escapedQuestion}', '${escapedAnswer}');`);
    questionId++;
  }
});

// 将SQL语句写入文件，方便复制到控制台执行
fs.writeFileSync('insert-data.sql', sqlStatements.join('\n'));
console.log('SQL脚本已生成：insert-data.sql');

2. MySQL LOAD DATA INFILE（需服务器支持）

如果你的MySQL服务器允许使用LOAD DATA INFILE命令，这种方式会更高效，适合大体积文本：

• 先把文本预处理成CSV格式（比如用脚本拆分分类和问答，生成两个CSV文件）
• 执行LOAD DATA INFILE命令直接导入，示例：

  LOAD DATA INFILE '/path/to/categories.csv'
  INTO TABLE website.Categories
  FIELDS TERMINATED BY ','
  ENCLOSED BY '"'
  LINES TERMINATED BY '\n';
  

  但这种方式需要服务器能访问到文件，且你有上传文件到服务器的权限，门槛比脚本方案高。

3. 表格工具预处理（适合小体量文本）

如果文本行数不多，也可以用Excel/Google Sheets手动处理：

• 把文本复制到表格，拆分出分类、问题、答案列
• 用表格公式自动生成INSERT语句，比如分类行的公式：="INSERT INTO website.Categories VALUES ("&A2&", '"&B2&"');"
• 最后复制所有生成的语句到MySQL控制台执行

总结 #

如果文本量不大，你的JavaScript脚本方案就是最简单的实现方式——不需要额外工具，逻辑清晰，还能避免手动处理的错误。如果文本量极大，且服务器支持LOAD DATA INFILE，可以考虑后者，但脚本方案始终是最通用稳妥的选择。

内容的提问来源于stack exchange，提问作者Leisure