错误catch的最佳方法LOAD DATA LOCAL INFILE？ [英] Best way to error catch LOAD DATA LOCAL INFILE?

问题描述

我是一个MySQL新手完成了一大块我的webapp的核心代码。该代码导入一个CSV文件（本地），由FileMaker生成和清理，并保留。

I'm a MySQL newbie finishing up a chunk of code central to my webapp. The code imports a CSV file (locally), that is generated and cleaned up by FileMaker and leaves it be.

由于这将在很快的某个时候进入生产并可能是一个数据库下500mb），我想知道是否有任何更好的错误检查/捕获，我可以做，可能防止问题的路上或提醒我的服务器设置。我已经读过关于临时日志等的事情，我的MySQL管理还不到鼻烟。

Given this is will go to production at some point soon (and probably be a database under 500mb ), I would love to know if there is any better error checking /catching that I could do to possibly prevent issues down the road or be alerted to my server setup. I've read things about temp logs, etc. and my MySQL administration isn't up to snuff yet.

最基本的代码是：

$m = mysql_connect('localhost', 'mytable', 'mypassword');
$db = 'mydb';
mysql_select_db($db) or die("Import Error - Couldn't select database: " . mysql_error());

$sql = 'load data local infile "inventory.csv"
        into table ibl_account_details_temp fields terminated by ","
        optionally enclosed by "\""
        lines terminated by "\r"
        (store_id, SKU, account, item_number, brand, description, size, category, price, qty, fees)
        ';

echo mysql_query($sql) or die(myqsl_error());

PS编辑：也希望知道这种导入方法是否向SQL注入开放？

推荐答案

data infile是非常差的，我几乎每天都使用它，它变成简单的例程导入到某种临时表，并使用PHP和MySQL的组合来验证导入，可以认为这是额外的工作，但它确实有优势，给我完全控制什么是错误是简单地说，我使用它来尽可能高效地获得原始数据，然后建立我的错误检查和验证的规则在PHP脚本。

Unfortunately, error handling with load data infile is very poor. I use it almost every day, and it's become simple routine to import into a temporary table of some kind, and use a combination of PHP and MySQL to validate what was imported. One can argue that this is extra work, but it does have the advantage of giving me full control of what an "error" is. Simply put, I use it to get the raw data in place as efficiently as possible, then build my rules of error checking and validation in a php script.

这篇关于错误catch的最佳方法LOAD DATA LOCAL INFILE？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！