MySQL“加载本地数据文件" [英] MySQL "LOAD LOCAL DATA INFILE"
问题描述
我遇到了一个问题,我似乎无法克服它.我正在使用 LOAD LOCAL DATA INFILE
从文本文件(例如两列 29.12 23.42 以空格分隔)中获取信息.最初这仅适用于 MySQL 中的一列,但是我不得不添加一个 ID AUTO INCREMENT
以便我可以使用最新的条目,此外我现在想将另一列存储在我的数据库中.这是我的代码,它可能很明显,但我似乎无法找到它:
I have hit a problem and i cant seem to get over it. I am taking information from a text file(two columns 29.12 23.42 for example separated by a space), using LOAD LOCAL DATA INFILE
. Initially this was working with only one column in MySQL, however i have had to add an ID AUTO INCREMENT
so i can use the most recent entry, in addition i want to now store the other column in my Database. Here is my code, its probably pretty obvious but I can't seem to find it:
<?PHP
$username = "root";
$password = "";
$hostname = "localhost";
$table = "Received_Data";
// Connect to Database and Table
$dbhandle = mysql_connect($hostname, $username, $password)
or die("Unable to connect to MySQL");
echo "Connected to MySQL<br>";
$selectdatabase = mysql_select_db("IWEMSData",$dbhandle)
or die("Could not select IWEMSData");
echo "Connected to IWEMSData<br>";
// Clear Current Table and ReCreate
$dt = /*"UPDATE Received_Data SET PotVal = ''";*/ "DROP TABLE Received_Data";
mysql_query($dt);
$ctb = "CREATE TABLE Received_Data
(
Current DECIMAL(30,2) NOT NULL,
PotVal DECIMAL(30,2) NOT NULL,
ID BIGINT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(ID,PotVal,Current)
)";
mysql_query($ctb, $dbhandle);
echo "Received_Data Table has been created";
// Text File to read in
$Textfile = 'IWEMSData.txt';
mysql_query('
LOAD DATA LOCAL INFILE "IWEMSData.txt"
REPLACE INTO TABLE Received_Data
FIELDS TERMINATED BY ""
LINES TERMINATED BY "\\r\\n
(PotVal, Current)
";')
or die('Error Loading Data File.<br>' . mysql_error());
// Close Database connection when complete
mysql_close($dbhandle);
?>
所以我想要的是第 1 列是 ID,它会在每个条目上自动递增.第二行是 PotVal,第三行是 Current.然后我只想存储在第二列和第三列中.
So what i want is Column 1 is ID which will AUTO INCREMENT upon each entry. The second row is PotVal and the third row is Current. Then i want to only store in the second and third column.
我遇到的问题是 ID 和 Current 显示的值不正确,而且我似乎只能得到一行.
The problem i am getting is that ID and Current are displaying the incorrect values and i can only seem to get one row.
提前致谢!
推荐答案
mysql_query('
LOAD DATA LOCAL INFILE "IWEMSData.txt"
REPLACE INTO TABLE Received_Data
FIELDS TERMINATED BY "" /*<- terminated by nothing? Shouldn't there be a space in it?*/
LINES TERMINATED BY "\\r\\n /*<- the closing " is missing*/
(PotVal, Current)
";') /*<- ah, here's the missing " What does it do here?*/
这样写:
mysql_query('
LOAD DATA LOCAL INFILE "IWEMSData.txt"
REPLACE INTO TABLE Received_Data
FIELDS TERMINATED BY " "
LINES TERMINATED BY "\\r\\n"
(PotVal, Current)
;')
这篇关于MySQL“加载本地数据文件"的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!