从.txt文件插入mysql表 [英] insert in mysql table from .txt file

问题描述

我有一种情况，我必须从.txt文件插入mysql表.并且数据采用以下格式.左右数据都分别插入了两列.第三个专栏秀是id

i have a situation where i have to insert in mysql table from a .txt file. and data is in format of below. both the data from left and right show seperatly insert into 2 columns. and the 3rd colum show be id

koijjh12 : 12
lkoiujjf : 12
uoytresf : 15
kjhgfd56 : 50

我们可以使用php脚本将该.txt文件导入到mysql数据库中吗?

can we use php script to import that .txt file to the mysql database.

推荐答案

您当然可以通过PHP脚本来完成，但是最好的方法是使用

You certainly can do it via a PHP script, but the best way is to use the Mysql load data infile syntax like this:

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test
  FIELDS TERMINATED BY ','  LINES TERMINATED BY '\n';

在这种情况下，字段似乎以:终止，因此您可以像这样轻松地对其进行更改:

In you case, it looks like the fields are terminated by : so you can change it easily enough like this:

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test
  FIELDS TERMINATED BY ' : '  LINES TERMINATED BY '\n';

您可以从任何mysql查询函数中运行该命令，方法是将其作为查询插入.这将比您在PHP中可以手动完成的任何操作快得多[em] (例如，将文本读入PHP，然后将其插入数据库中).

You can run this command from any mysql query function by inserting it as the query. This will be much faster than anything you can do in PHP manually (as in reading the text into PHP, then inserting it into the database).

请注意，如果您在字符串中使用此字符，则需要使用另一个\来换行符中的\，如下所示:LINES TERMINATED BY '\\n';

Be aware that if you use this in a string, you will need to escape the \ in the newline character with another \ like this: LINES TERMINATED BY '\\n';

这篇关于从.txt文件插入mysql表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！