仅在Bash命令行执行Update查询时出现语法错误

我尝试在Bash脚本中执行以下查询：

echo 'update LOCKSTATUS'
if ! mysql -h site.com -u user -pPassword -D db -e "UPDATE tblcustomfieldsvalues a
JOIN
  (SELECT tblclients.userid,
          tblclients.company,
          tblcustomfieldsvalues.`value`,
          CoreUsers.`LOCKED_STATUS`,
          tblcustomfieldsvalues.id
   FROM tblclients
   LEFT JOIN tblcustomfieldsvalues ON tblcustomfieldsvalues.relid = tblclients.userid
   AND tblcustomfieldsvalues.fieldto = 'customers'
   AND tblcustomfieldsvalues.fieldid = 16
   LEFT JOIN CoreUsers ON CoreUsers.USERID = tblclients.company) b
SET a.`value` = b.`LOCKED_STATUS`
WHERE a.id = b.id
  AND b.`LOCKED_STATUS` IS NOT NULL;"; then
  echo "Error: LOCK STATUS"
  exit 1
fi

该查询在MySQL客户端中执行完全正常，但Bash脚本返回错误：

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '
          CoreUsers.,
          tblcustomfieldsvalues.id
   FROM tblclients
...' at line 5

问题原因 #

错误提示显示CoreUsers.后的LOCKED_STATUS字段消失，这是因为Bash会解析双引号内的特殊字符——你SQL里的`LOCKED_STATUS`被Bash当成了命令执行占位符，导致字段名被替换为空，最终出现语法错误。

解决方案 #

方法1：改用单引号包裹SQL语句 #

单引号在Bash中不会解析内部特殊字符，把外层的双引号换成单引号，同时将SQL内部的单引号替换为双引号即可：

echo 'update LOCKSTATUS'
if ! mysql -h site.com -u user -pPassword -D db -e 'UPDATE tblcustomfieldsvalues a
JOIN
  (SELECT tblclients.userid,
          tblclients.company,
          tblcustomfieldsvalues.`value`,
          CoreUsers.`LOCKED_STATUS`,
          tblcustomfieldsvalues.id
   FROM tblclients
   LEFT JOIN tblcustomfieldsvalues ON tblcustomfieldsvalues.relid = tblclients.userid
   AND tblcustomfieldsvalues.fieldto = "customers"
   AND tblcustomfieldsvalues.fieldid = 16
   LEFT JOIN CoreUsers ON CoreUsers.USERID = tblclients.company) b
SET a.`value` = b.`LOCKED_STATUS`
WHERE a.id = b.id
  AND b.`LOCKED_STATUS` IS NOT NULL;'; then
  echo "Error: LOCK STATUS"
  exit 1
fi

方法2：转义SQL中的反引号 #

如果坚持使用双引号包裹SQL，需要用反斜杠转义所有SQL里的反引号，避免Bash解析：

echo 'update LOCKSTATUS'
if ! mysql -h site.com -u user -pPassword -D db -e "UPDATE tblcustomfieldsvalues a
JOIN
  (SELECT tblclients.userid,
          tblclients.company,
          tblcustomfieldsvalues.\`value\`,
          CoreUsers.\`LOCKED_STATUS\`,
          tblcustomfieldsvalues.id
   FROM tblclients
   LEFT JOIN tblcustomfieldsvalues ON tblcustomfieldsvalues.relid = tblclients.userid
   AND tblcustomfieldsvalues.fieldto = 'customers'
   AND tblcustomfieldsvalues.fieldid = 16
   LEFT JOIN CoreUsers ON CoreUsers.USERID = tblclients.company) b
SET a.\`value\` = b.\`LOCKED_STATUS\`
WHERE a.id = b.id
  AND b.\`LOCKED_STATUS\` IS NOT NULL;"; then
  echo "Error: LOCK STATUS"
  exit 1
fi

方法3：将SQL写入单独文件执行 #

对于复杂查询，更稳妥的方式是把SQL写入单独文件，再通过Bash调用执行，彻底规避引号解析问题：

1. 创建update_lockstatus.sql文件：

UPDATE tblcustomfieldsvalues a
JOIN
  (SELECT tblclients.userid,
          tblclients.company,
          tblcustomfieldsvalues.`value`,
          CoreUsers.`LOCKED_STATUS`,
          tblcustomfieldsvalues.id
   FROM tblclients
   LEFT JOIN tblcustomfieldsvalues ON tblcustomfieldsvalues.relid = tblclients.userid
   AND tblcustomfieldsvalues.fieldto = 'customers'
   AND tblcustomfieldsvalues.fieldid = 16
   LEFT JOIN CoreUsers ON CoreUsers.USERID = tblclients.company) b
SET a.`value` = b.`LOCKED_STATUS`
WHERE a.id = b.id
  AND b.`LOCKED_STATUS` IS NOT NULL;

2. 在Bash脚本中执行：

echo 'update LOCKSTATUS'
if ! mysql -h site.com -u user -pPassword -D db < update_lockstatus.sql; then
  echo "Error: LOCK STATUS"
  exit 1
fi

内容的提问来源于stack exchange，提问作者R_life_R