mysql - 将空值和逗号导入 MYSQL

Question

我正在像这样将文件导入 MYSQL:

LOAD DATA LOCAL INFILE 'C:\\Users\\Justin.Mathieu\\Documents\\data.csv' into table Ingredients
fields terminated by ','
optionally enclosed by '"' 
lines terminated by '\n'
(iVendor, iName, iOrderNumber, iCaseQty, iUnitSize, iDrainSize, iUnitSoldBy, iCaseCost, iVolumeConversion, iUnitLabel, iIngredients)

以及一些导入数据的示例:

VendorName,"Apple Whole Peeled Cored Grd A Fz",61318,1,\N,\N,\N,38.17,\N,\N,\N
VendorName,"Apples- Applesauce Sweetened",10738,6,\N,\N,-1,24,\N,\N,\N
VendorName,"Applesauce Unswtn Fancy",10676,6,106,\N,-1,23.97,\N,\N,"Apples, Water, Erythorbic Acid (To Maintain Color)"
VendorName,Apron 24"X42" White Reg Wt - Dispenser ,81463,1,\N,\N,\N,9.98,\N,\N,\N
VendorName,"Artichoke Heart 40-50Cnt Quartered,     Marintated",10722,6,88,\N,-1,71.46,\N,,"Artichoke Hearts, Vegetable Oil (Soya Or Sunflower), Water, Vinegar"
VendorName,"Artichoke Heart Crse Ct",77334,6,\N,55,\N,49.16,\N,\N,\N
VendorName,"Artichoke Heart Quarters",11804,6,\N,\N,-1,62.46,\N,\N,"Artichoke Hearts, Water, Salt And Citric Acid"
VendorName,"Artichoke Quarters Mixed Cuts",D6808,6,\N,\N,\N,60.26,\N,\N,\N
VendorName,"Bacon Bit Real Ref",56188,2,\N,\N,\N,51.22,\N,\N,\N
VendorName,"Barley Pearled Dry Us #1 Standard",33050,2,\N,\N,\N,21.38,\N,\N,\N

第一个问题是使用\N。导入后查看我的数据库时，我在 iIngredients 字段中得到的不是空值，而是“N”。

第二个问题是大部分行没有被导入，这是我遇到的错误示例:

164 row(s) affected, 64 warning(s): 
1262 Row 3 was truncated; it contained more data than there were input columns 
1262 Row 5 was truncated; it contained more data than there were input columns 
1262 Row 9 was truncated; it contained more data than there were input columns 
1262 Row 10 was truncated; it contained more data than there were input columns 
1262 Row 11 was truncated; it contained more data than there were input columns 
1262 Row 12 was truncated; it contained more data than there were input columns 
Records: 164  Deleted: 0  Skipped: 0  Warnings: 94

我已经验证每一行都有任何可能包含逗号的数据被包裹在“”中。

任何想法可能会发生什么？

编辑: 我能够通过对字符串行使用 ""和对非字符串行使用\N 来解决\N 问题。

编辑 2: 在被截断的行中，iIngredients 字段包含下一行的所有数据，就好像它没有检测到以 ".< 结尾的行的换行符一样/p>

Answer 1

经过一番摸索，我明白了。

问题是，当我将文件转换为为空字段插入“/N”时(在 C# 中)，我使用的是:

Write(rowData + Environment.NewLine);

显然 MySQL 真的不喜欢那样，所以我将其更改为:

Write(rowData + "\n");

这似乎工作得很好。