如何在导入到 phpmyadmin 时设置空值？
我正在尝试将 .csv 文件导入 phpmyadmin，其中几个字段故意留空。我需要将这些字段注册为空值，而不仅仅是保留为空白字符串。
我知道在字段属性中，您可以选择允许每个字段“空”与“非空”，但在导入时它仍然不会将单元格更改为空值。导入后，我可以手动检查每条记录上每个字段的空框，但考虑到我正在使用的数据量，这是不现实的。
有没有办法让 phpmyadmin 在导入时将这些空白单元格设置为空值？
I'm trying to import a .csv file into phpmyadmin where several fields are purposefully left blank. I need these field to register as null values and not just left as a blank string.
I know in the field properties you can select to allow "null" vs. "not null" for each field, but it still doesn't change cell to a null value while importing. After the import I can manually go check the null box for each field on each record, but that it unrealistic considering the amount of data I'm working with.
Is there a way to get phpmyadmin to set these blank cell to null values on import?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我也遇到过类似的问题。
如果您下载包含 NULL 值的 PhpMyAdmin CSV 文件，您会注意到 NULL 没有用引号括起来。因此，您将看到这样的一行：
"1";"2";NULL;NULL
"2";"2";NULL;NULL
等。
但是，如果您在 Open Office Calc 之类的工具中编辑 CSV 文件，它可能会更改此设置以在 NULL 周围加上引号，如下所示：
"1";"2";"NULL";"NULL"
"2";"2";"NULL";"NULL"
等。
应该做的是搜索并替换 ["NULL" = NULL]。
在您的情况下，因为您有空（空白）字段，所以您将考虑执行搜索和替换，如下所示：
[,, = ,NULL,]
并且可能在行末尾对 NULL 值进行第二次传递，例如所以:
[,\n = ,NULL\n]
I've been experience similar issues.
If you download a PhpMyAdmin CSV file with NULL values, you'll notice that NULL doesn't get encapsulated with quotes. So you'll have a line like this:
"1";"2";NULL;NULL
"2";"2";NULL;NULL
etc.
However, if you edit a CSV file in something like Open Office Calc, it might change this to put quotes around NULL, like so:
"1";"2";"NULL";"NULL"
"2";"2";"NULL";"NULL"
etc.
What should work is doing a search and replace for ["NULL" = NULL].
In your case, because you have empty (blank) fields, you'll be looking at doing a search and replace like this:
[,, = ,NULL,]
And probably a second pass for NULL values at the end of a line like so:
[,\n = ,NULL\n]
古老的问题，但以防万一像我这样的另一个 MySQL 菜鸟遇到它。
如果您负责 CSV 文件的创建，例如在备份自己的数据库时，jmbertucci 描述的查找/替换 rigamarole jmbertucci 是可以避免的。在phpMyAdmin中，如果您选择“自定义”导出方法，您将看到
replace NULL with:，默认为NULL。只需将其更改为"NULL"即可节省一步。Ancient question, but in case another MySQL noob like myself comes across it.
The find/replace rigamarole jmbertucci describes is avoidable if you're in charge of the creation of the CSV file, for example when you're backing up your own databases. In phpMyAdmin, if you select "custom" export method, you will see
replace NULL with:and the default isNULL. Simply change that to"NULL"and you save yourself a step.我遇到了同样的问题，jmbertucci 的答案非常有效。我确实遇到了一个额外的问题。对于像
"hello","world",,,,,,这样的一行数据，在一行中具有多组空值，执行搜索替换为 [,, = ， NULL,] 正如 jmbertucci 建议的那样，在第一次通过时不会按您的预期工作。相反，您最终会得到
"hello","world",NULL,,NULL,,NULL您应该继续执行搜索替换，直到最终替换 0 个匹配项
I ran into this same problem and jmbertucci's answer worked great. I did run into one additional problem. In the case with a row of data like such
"hello","world",,,,,,which has multiple sets of null values in a row doing a search replace with [,, = ,NULL,] as jmbertucci suggested won't work as you intend it to on the first pass. Instead you'll end up with
"hello","world",NULL,,NULL,,NULLYou should continue to do the search replace to until you end up with 0 occurrences replaced