SQL保留关键字在从文本文件导入数据时导致错误 [英] SQL reserved keywords causing errors while importing data from text file

问题描述

我有以下代码。我正在尝试使用该网站上的一个用户建议使用php将文本文件导入到sql表中。

I have the following code. I am trying to import a text file in to sql table using php as suggested by one of the users on this site.

不幸的是，由于我的文本文件在文件中包含Max和Min字样，我的导入错误中途。

Unfortunately my import got errors half way due to my text file having "Max and Min" words in the file.

我试图找出我能做些什么来避免它。我发现的大部分内容都是关于在列名中使用保留字。但我的不是列名作为数据插入列中的列名。

I tried to find out what i can do to avoid it. Most of the stuff i found was about using reserved words in the column name. But mine is not a column name it is inserted in the columns as data.

这可以避免，因为我不知道文本文件中有多少其他保留字，我需要让我的代码每天自动运行。我不能每次都流产。这是一个巨大的文本文件，所以我不能每次都手动替换关键字。

Can this be avoided as I don't know how many other reserved words are present in the text file and I need to make my code run automatically everyday. I can't have it aborting every time. It is a huge text file so I can't manually replace keywords everytime either.

mysqli_query("CREATE TABLE IF NOT EXISTS `add_feature_id` (
`id_f` INT(10) unsigned NOT NULL AUTO_INCREMENT,
`id_product` INT(10) unsigned NOT NULL,
`id_feature` INT(10) unsigned NOT NULL, 
`value` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL
DEFAULT   NULL,PRIMARY KEY ( `id_f` ) )",$conn);

$fd = fopen('trial.txt', 'r');
$fheader = fgets($fd); 

while (($data = fgetcsv($fd,0, "~")) !== FALSE) {
$id_product = $data[0];
$id_feature = $data[1];
$unitval = $data[2];
$value = mysql_real_escape_string($unitval);

mysqli_query("INSERT INTO   `add_feature_id`(`id_product`,`id_feature`,`value`) 
VALUES ($id_product,$id_feature,'$value')",$conn) or die(mysql_error());
}

fclose($fd);

$result = mysqli_query("SELECT * FROM `add_feature_id`",$conn);
//I print my result here but i get error while insert is executed

错误我得到的是

你的SQL语法有错误;查看与您的MySQL服务器版本对应的手册，以便在'
最大和最小要求附近使用正确的语法

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' Max and Min Requirements

低入门级要求
Minimu '第2行

Low entry level requirement Minimu' at line 2

这是我的文本文件中发生错误的部分最大和最小要求，如文本文件中所示下面

This is the part of my text file where the error occurs Max and Min Requirements as can be seen in the text file below

IMSKU~AttributeID~Value~Unit~StoredValue~StoredUnit(header row)

1006854 ~ 16257 ~Licensing Program: Max and Min Requirements<ul><li>Low entry level  requirement</li><li>Minimum 1 server (Band S) OR 5 desktop (Band A)</li></ul> ~  ~ 0.00 ~

推荐答案

你的数据无效第32325行：

You've got invalid data on line 32325:

1062708~16257~Express Licensing Program:<ul><li>Targeted at small - medium companies (1-500 units)</li><li>Minimum purchase requirements for licenses</li><li>Includes all Symantec software products</li><li> Certificated-based program - requires no legal review</li><li>Band identified via number of units per transaction</li></ul>
<br />Max and Min Requirements<ul><li>Low entry level requirement</li><li>Minimum 1 server (S-Band) OR 5 desktop (A-H Band)</li></ul>~~0.00~
1062708~16260~2~~0.00~

那条中间线。

它失败的原因是因为 $ id_product 和 $ id_feature 不是单引号/或转义。

The reason it fails is because $id_product and $id_feature aren't in single quotes/or escaped.

你应该做的是准备/执行此（或摆脱它无效数据）。

What you should do is prepare/execute this (or get rid of that invalid data).

这篇关于SQL保留关键字在从文本文件导入数据时导致错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！