php - MYSQL加载数据本地Infile&特殊字符(&PHP)

Question

我想了解如何编写查询以将我的 goodreads.com 图书列表 (csv) 导入我自己的数据库。我正在使用 PHP。

$query = "load data local infile 'uploads/filename.csv' into table books 
            RETURN 1 LINES 
    FIELDS TERMINATED BY ','
    ENCLOSED BY '\"'
    LINES TERMINATED BY '\n' 
    (Title, Author, ISBN)";

这导致数据在错误的列中，我也无法获得 ISBN，无论我尝试什么，猜测等号 (=) 都会导致一些问题。

这是 CSV 的前两行:

Book Id,Title,Author,Author l-f,Additional Authors,ISBN,ISBN13,My Rating,Average Rating,Publisher,Binding,Number of Pages,Year Published,Original Publication Year,Date Read,Date Added,Bookshelves,Bookshelves with positions,Exclusive Shelf,My Review,Spoiler,Private Notes,Read Count,Recommended For,Recommended By,Owned Copies,Original Purchase Date,Original Purchase Location,Condition,Condition Description,BCID
11487807,"Throne of the Crescent Moon (The Crescent Moon Kingdoms, #1)","Saladin Ahmed","Ahmed, Saladin","",="0756407117",="9780756407117",0,"3.87","DAW","Hardcover","288",2012,2012,,2012/02/19,"to-read","to-read (#51)","to-read",,"",,,,,0,,,,,

我想做的只是将所有书籍的标题、作者和 ISBN 导入到具有以下字段的表中:id(自动)、标题、作者、isbn。我也想排除第一行，因为我不想要列标题，但是当我尝试这样做时，它每次都会失败。

我的查询有什么问题？

Answer 1

不幸的是，如果您想使用 native MySQL CSV 解析器，则必须创建一个与 CSV 文件的列数相匹配的表。导入完成后，只需删除不需要的列即可。