mysql - LOAD DATA INFILE 和 LINES TERMINATED 错误

Question

当我从本地文件加载数据时，数据在某些行的开头被截断，我尝试了以“/r/n”、“/r”和“/n”结尾的行，它们只加载了一行在表中..

MyTable 作为一列，而 MyFile.txt 只有一列，每行末尾用换行符分隔。

欢迎任何建议!

## Here is an example of the data I have to load 
ENSG00000000003.10
ENSG00000000005.5
ENSG00000000419.8
ENSG00000000457.8
ENSG00000000460.12
ENSG00000000938.8
ENSG00000000971.11
ENSG00000001036.8




mysql> LOAD DATA LOCAL INFILE "C:/MyFile.txt" INTO TABLE MyTable;
Query OK, 57281 rows affected (0.73 sec)
Records: 57281  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM MyTable limit 5;
+---------------------+
| Ensembl             |
+---------------------+
 |ENSG00000000003.10
  |NSG00000000005.5
  |NSG00000000419.8
  |NSG00000000457.8
 |ENSG00000000460.12
+---------------------+

Answer 1

我也遇到了这个问题，所以我最终在加载每个文件之前为一些“testLines”编写了一个迷你解析器。

public static void findTerminator(File file) throws FileNotFoundException {
    BufferedReader lines = new BufferedReader(new FileReader(file));
    int countLines = 0;
    int testLines = 15;
    int c;
    int[] terminators = { 0x0A, 0x0D, 0x0D0A }; //\n, \r, \r\n
    int[] counters = { 0, 0, 0 };
    try {
        while (((c = lines.read()) != -1) && (countLines <= testLines)) {
            for (int d = 0; d < terminators.length; d++) {
                if (c == terminators[d]) { 
                    counters[d]++; 
                    countLines++;
                }
            }
        }
    } 
    catch (IOException e) { e.printStackTrace(); }

    int max = 0;
    int maxindex = 0;
    for (int i = 0; i < counters.length; i++) {
        if (max < counters[i]) { 
            max = counters[i]; 
            maxindex = i; 
        }
    }
    terminator = (char)terminators[maxindex];
    System.out.println("Terminator: '" + terminators[maxindex] + "'");
}