通过 Bash 为 SQL 表中的日期时间条目添加 NULL 值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16365891/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Adding a NULL value for a datetime entry in an SQL table through Bash
提问by Sleepykrooks
I am attempting to populate an SQL table through reading text files via bash to hand off to the table. I have it all working except when trying to add a null value to a datetime field.
我正在尝试通过 bash 读取文本文件来填充 SQL 表以移交给表。除了尝试向日期时间字段添加空值时，我都可以正常工作。
mysql --host 10.100.10.19 --port 3306 -utest -ptest << EOF
INSERT INTO imaging.imagestatus (StatusDate, FileName, FolderName, Status, User, CaptureDate, Kind, Preview) VALUES ('2013-05-01T15:25:55', 'fileName1', 'folderName1', 'Status1', 'User1', 'NULL', 'kind1', 'preview1');
EOF
I keep getting:
我不断得到：
ERROR 1292 (22007) at line 1: Incorrect datetime value: 'NULL' for column 'CaptureDate' at row 1
for the value labeled 'NULL' which is a data type datetime in the table. I have tried the combinations '\N', '\N', 'NULL', 'null' and ' '
对于标记为“NULL”的值，它是表中的数据类型日期时间。我尝试了组合 '\N'、'\N'、'NULL'、'null' 和 ' '
I started to use a temporary date: 00-00-00 00:00:00 but I want it to match other null entry formats or just a blank space in the table. Thanks for the help!
我开始使用临时日期：00-00-00 00:00:00 但我希望它与其他空条目格式或表格中的空白相匹配。谢谢您的帮助！
ANSWER-- As was implemented
答案 - 已实施
The value in the text file name "NULL" that is pulled in can be swapped with a variable like a=NULL to get a correct NULL value for a datetime input.
拉入的文本文件名“NULL”中的值可以与像 a=NULL 这样的变量交换，以获得日期时间输入的正确 NULL 值。
回答by Colin
If you're using quotes, then you're saying that you want the date to be the string 'NULL'. Did you try just using NULL?
如果您使用引号，则表示您希望日期为字符串 'NULL'。你试过只使用 NULL 吗？
INSERT INTO imaging.imagestatus (StatusDate, FileName, FolderName, Status, User, CaptureDate, Kind, Preview)
VALUES ('2013-05-01T15:25:55', 'fileName1', 'folderName1', 'Status1', 'User1', NULL, 'kind1', 'preview1');
