☰

mysql引用问题

tag-icon • tag-icon bash ubuntu quoting mysql
mysql引用问题

我正在运行以下命令并收到与引用或其他内容相关的错误。

#!/bin/bash
test='my_table'
date='2020-10-31 00:00:00.000000'

mysql --user=halloween --password='ghost_123' --database='ghost_database' <<END
use ghost_datebase;
CREATE EVENT myevent    ON SCHEDULE AT \"$date"\ ON COMPLETION NOT PRESERVE  ENABLE DO  SELECT * FROM "$test";
END

然后我收到以下错误：

ERROR at line 2: Unknown command '\"'.

答案1

双引号标识END此处文档开头的标签。然后在此处文档中使用正确的 MySQL 语法 - 不要转义引号，除非它们会在 MySQL 中正常转义

mysql --user='halloween' --password='ghost_123' --database='ghost_database' <<"END"
CREATE EVENT myevent ON SCHEDULE AT "$date" ON COMPLETION NOT PRESERVE ENABLE DO SELECT * FROM "$test";
END

请注意，我并没有尝试修复您的 SQL 语法。我，我不完全确定双引号对于表名（甚至对于字符串）是否正确。

如果您没有任何要插入的变量，那么用单引号引用here-doc标识符是正确的。 （引用与字符串的引用相同。）

答案2

qouted 字符串成为此处文档：

<<END
CREATE EVENT $name ON SCHEDULE
  AT Str_to_date('$date', '%Y-%m-%d %T.%f')
  ON COMPLETION NOT PRESERVE ENABLE DO SELECT * FROM $test;
END

我已在 MariaDB 数据库上成功运行以下命令：

export DATE='2020-10-28 14:00:00.000000'
export NAME=eventje
export TEST=test

mysql --password='xYzAbC'  --database=ghp <<END
DROP EVENT $NAME;
CREATE EVENT $NAME ON SCHEDULE
  AT Str_to_date('$DATE', '%Y-%m-%d %T.%f')
  ON COMPLETION NOT PRESERVE ENABLE DO SELECT * FROM $TEST;
END

答案3

我能够使用以下脚本找到解决方案：

$date 变量周围只有一个双引号


date='2020-10-31 00:00:00.000000'

#CREATE EVENT myevent ON SCHEDULE AT "$date" ON COMPLETION NOT PRESERVE ENABLE DO SELECT * FROM '$test';
mysql --user=halloween --password='ghost_123' --database='ghost_database' <<END
use ghost_database;
CREATE EVENT myevent ON SCHEDULE AT "$date" ON COMPLETION NOT PRESERVE ENABLE DO SELECT * FROM ghost_database;
END

``````````````````


This script can be run separately if needed using an input argument:
In this example below the input argument is 'ghost'.  It could technically lookup whatever variables or be passed the date variable in epoch and simply convert it.

`````````````````
while getopts i: option

do
case "${option}"
in
i) ghost=${OPTARG};;
esac

相关内容