将具有空值的csv文件导入phpmyadmin [英] Importing csv file with null values into phpmyadmin

问题描述

当我将一个csv文件导入MySQL(phpmyadmin)时，对于文件中未指定但默认值为null的所有整数值，会出现错误消息:#1366 - Incorrect integer value: '' for column 'id' at row 1..我有这些问题:

When I import a csv file into MySQL (phpmyadmin), for all integer values that are not specified in the file but have a default of null there is an error message: #1366 - Incorrect integer value: '' for column 'id' at row 1.. I have these questions:

a.如果数据库表的ID定义为自动递增，如何导入未指定行ID的csv文件?

a. How do I import a csv file that does not have the row-id specified if the DB table has that id defined as auto-increment?

b.在csv文件或phpmyadmin中的表列规范中，对于默认值为null的整数列，我需要什么?

b. What do I need in the csv file or in the table column specification in phpmyadmin for integer column that have a default of null?

这是csv文件中的示例行.

Here is are sample rows from the csv file.

id,year,month,date,day,description,ranking
,,3,1,,,
,,3,2,,,
,,3,3,,,
,,3,4,,,
,,3,5,,,
,,3,6,,,
,,3,7,,,
,,3,7,,"Saints Perpetua and Felicity, Martyrs",
,,3,8,,,
,,3,8,,"Saint John of God, Religious",
,,3,9,,,
,,3,9,,"Saint Frances of Rome, Religious",
,,3,10,,,

导致错误的列是ID，年份，排名.它们都是整数列.列ID是自动递增.其他列为INT(11)，默认值为NULL.谢谢.

The columns that cause the error are id, year, ranking. They are all integer columns. The column id is auto increment. The other columns are INT(11) with a default of NULL. Thanks.

推荐答案

CSV没有空"的概念.无法区分为空的字段和具有合法空值(例如，空字符串)的字段.在插入查询之前，必须在加载行时对行进行按摩，以将所有空字符串"替换为适当的NULL

CSV has no concept of "Nulls". It's impossible to differentiate between a field that is null, and a field that has a legitimately empty value (e.g. empty string). You'll have to massage the rows as you load them prior to query insertion, to replace any 'empty strings' with appropriate NULLs

例如

$row = fgetcsv(...);
$row[0] = 'NULL';

这篇关于将具有空值的csv文件导入phpmyadmin的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！