使用 UBUNTU 16。
MySQL 查询在 bash 中失败,但在 MySQL 客户端中可以运行:
query=$(cat << EOF
CREATE DATABASE \`${mysql_local_database}\` /*!40100 COLLATE \'utf8_hungarian_ci\' */; use \`${mysql_local_database}\`; GRANT SELECT, EXECUTE, SHOW VIEW, ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, INDEX, INSERT, REFERENCES, UPDATE, LOCK TABLES ON \`${mysql_local_database}\`.* TO 'myuser'@'%'; FLUSH PRIVILEGES;
EOF
)
echo $query
mysql -u ${mysql_local_user} -p${mysql_local_pass} -h localhost "${query}"
执行 bash 脚本后出现以下错误:
CREATE DATABASE `mydb` /*!40100 COLLATE 'utf8_hungarian_ci' */; use `mydb`; GRANT SELECT, EXECUTE, SHOW VIEW, ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, INDEX, INSERT, REFERENCES, UPDATE, LOCK TABLES ON `mydb`.* TO 'user'@'%'; FLUSH PRIVILEGES;
ERROR 1102 (42000): Incorrect database name 'CREATE DATABASE `mydb` /*!40100 COLLATE 'utf8_hungarian_ci' */'
如果我在 MySQL 客户端(HeidiSQL)中执行打印的 MySQL 查询,它可以正常工作:
CREATE DATABASE `mydb` /*!40100 COLLATE 'utf8_hungarian_ci' */; use `mydb`; GRANT SELECT, EXECUTE, SHOW VIEW, ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, INDEX, INSERT, REFERENCES, UPDATE, LOCK TABLES ON `mydb`.* TO 'myuser'@'%'; FLUSH PRIVILEGES;
/* Affected rows: 1 Found rows: 0 Warnings: 0 Duration for 4 queries: 0,000 sec. */
请告知如何修复。
解决方案
正如@yoonix 指出的那样,是的,缺少了 -e 选项。
mysql -u ${mysql_local_user} -p${mysql_local_pass} -h localhost - e "${query}"
现在运行良好。谢谢您的帮助!
答案1
我不是 100% 确定 collate utf8_hungarian_ci 部分,但我能够通过省略反斜杠和勾号,将创建数据库行更改为:来让它创建数据库而不会出现错误:
CREATE DATABASE $mysql_local_database /*!40100 COLLATE utf8_hungarian_ci */ ; use $mysql_local_database ; GRANT SELECT, EXECUTE, SHOW VIEW, ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, INDEX, INSERT, REFERENCES, UPDATE, LOCK TABLES ON $mysql_local_database.* TO 'myuser'@'%'; FLUSH PRIVILEGES;
并且在这里添加了 -se:
mysql -u ${mysql_local_user} -p${mysql_local_pass} -h localhost -se "${query}"