mysql 将文本解析为SQL语句的最佳方法？

pes8fvy9 于 2023-01-25 发布在 Mysql

关注(0)|答案(1)|浏览(134)

我正在开发一个Web应用程序，我只能通过控制台访问MySQL。我有一个文本文件，我想将数据插入到DB中，但要做到这一点，我需要先做一个解释器，将文本文件中的行转换为插入语句吗？或者有没有一个更简单的方法，我忽略了？
我有一个这样的文本文件（格式在这篇文章的底部）
我想从文件中取出每一行，做一些字符串操作，并将其插入数据库。
DB看起来像这样

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)
);

我可以做一个JavaScript解释器，它将文本文件“编译”成一堆MySQL插入语句，然后我可以运行这些语句，有人能验证这是否是最简单的方法吗？
我必须做的伪代码示例：

- go one line at a time
- initialize counter
- take first line, surround it with "INSERT INTO 'website.categories' VALUES (_____)
- WHILE line not blank, take every line after that, split string using '?' as separator
- "INSERT INTO 'website.questions' VALUES (counter, __question___, __answer___)
- when blank increment counter
- insert next line into categories
- repeat

文本文件如下所示：

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

来源：https://stackoverflow.com/questions/75196838/best-way-to-parse-text-into-sql-statements

1条答案

按热度按时间

wwwo4jvm1#

我能够很容易地用Java完成这一点，我以为这需要几个小时，但不仅仅只花了不到10分钟。

Scanner myReader = new Scanner(myObj);
  while (myReader.hasNextLine()) {
    String data = myReader.nextLine();
    if(data.equals("")) {
        counter++;
    }else if(data.contains("?")){
        String lol[] = data.split("\\?");
        System.out.println("INSERT INTO website.Questions (ID,Category,QuestionText,AnswerText) Values (" +counter2+","+ counter + ",'"+lol[0]+"', '"+ lol[1]+"');");
        counter2++;
    }else{
        System.out.println("INSERT INTO website.Categories (ID, CategoryName) Values ("+ counter +", '" + data +"');");
    }

赞(0）回复(0）举报 2023-01-25

我来回答

mysql 将文本解析为SQL语句的最佳方法？

1条答案

相关问题

热门标签

最新问答