英文:
How to properly assign NULL value from Bash script to MySQL
问题
我有一个需要更新MySQL数据库的Bash脚本。我有一个if语句来确定要放入数据库的内容，但我认为我混淆了NULL值和NULL字符串。我的代码如下：
db_host="localhost"
db_user="user"
db_pass="pass"
db_name="dbname"
MyCmd="mysql -N -h $db_host -u $db_user -p$db_pass -D $db_name"
if [[ $LastCh > 1516980000 ]]; then
NewCh="$(date -d @$LastCh + "%Y-%m-%d %H:%M:%S")"
elif [ -n "$NewCh" ]; then
NewCh=$($MyCmd -e "SELECT MAX(LastChange) FROM Status WHERE IDN=$idn AND LastChange>0")
# NewCh=$($MyCmd -e "SELECT MAX(LastChange) FROM Status WHERE IDN=$idn AND LastChange IS NOT NULL")
else
NewCh=NULL
fi
StatSQL="IDN, Name, LastIP, Activesince, LastChange"
$MyCmd -e "INSERT INTO Status ($StatSQL) VALUES ('$idn', '$name', '$ip', '$activesince', '$NewCh')"
因此，如果我在脚本中获取了最后更改的值，我将其分配给数据库，但如果没有获取到它，它需要从IDN字段的MAX值中获取值，但如果至少有一个NULL，它会显示NULL值。我怀疑这与我在某些情况下分配NULL时实际字符串为"NULL"以及我的WHERE子句无法正常工作有关，所以我添加了额外的条件，但也没有起作用（.. AND LastChange IS NOT NULL），但使用LastChange>0可以正常工作，但也不适用于每种情况。
1.) 如何修改我的NewCh变量以能够携带NULL值？或如何甚至检查数据库中的哪些字段为NULL，哪些为"NULL"（字符串）？
英文:
I have a bash script that needs to update MySQL DB. I have an if statement to determine what to put into db, but I think I'm mixing up NULL values with NULL strings. My code is like following:
db_host="localhost"
db_user="user"
db_pass="pass"
db_name="dbname"
MyCmd="mysql -N -h $db_host -u $db_user -p$db_pass -D $db_name"
if [[ $LastCh > 1516980000 ]]; then
NewCh="$(date -d @$LastCh +"%Y-%m-%d %H:%M:%S")"
elif [ -n "$NewCh" ]; then
NewCh=$($MyCmd -e "SELECT MAX(LastChange) FROM Status WHERE IDN=$idn AND LastChange>0")
# NewCh=$($MyCmd -e "SELECT MAX(LastChange) FROM Status WHERE IDN=$idn AND LastChange IS NOT NULL")
else
NewCh=NULL
fi
StatSQL="IDN, Name, LastIP, Activesince, LastChange"
$MyCmd -e "INSERT INTO Status ($StatSQL) VALUES ('$idn', '$name', '$ip', '$activesince', '$NewCh')"
So I assign last change to DB if I get it within my script, but if not it needs to get the value from MAXimum value of IDN field, but it would display me NULL value for MAX value, if at least one is NULL. I suspect that it has to do when I assign NULL in some cases that is actual string of "NULL" and my WHERE clause didn't work so I added extra condition which also didn't work (.. AND LastChange IS NOT NULL) but it did with LastChange>0, but also not for every case.
1.) How to modify my NewCh variable to be able to carry NULL value? or how to even check what fields in DB are NULL and what "NULL" (string)
答案1
得分: 1
Shell变量没有特殊的空值。将它们设置为某个特定的字符串，您始终希望作为null插入（可以是字符串NULL或空字符串或其他内容），然后在插入中进行测试：
insert into tbl (col1,col2) values (nullif('$col1','specialnullvalue'),...
英文:
Shell variables don't have a special null value. Set them to some specific string that you always want inserted as null (which could be the string NULL or an empty string or something else), and test for that in the insert:
insert into tbl (col1,col2) values (nullif('$col1','specialnullvalue'),...
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论