与破折号的MySQL架构名称不允许我执行命令行查询 [英] MySQL schema name with dash does not allow me to execute command line query

问题描述

我创建一个shell脚本，从主机到主机自动我的数据库迁移，提取我多字preSS现场表到分离 SQL 文件，所以我可以决定什么网站我每次都会迁移。

所以在我的shell脚本我有以下的code：

 模式=互联网安全
PROJECT_FOLDER_NAME =互联网安全
vagrant_export_folder =/无功/网络/项目/ $ {} PROJECT_FOLDER_NAME /数据库/ tmp目录查询=MySQL的-uroot -proot -e \\选择blog_id为ID，路径弹头从$ {}模式.wp_blogs
查询=$查询INTO OUTFILE'$ vagrant_export_folder / blogs.csv'
查询=$查询字段TERMINATED BY'，'
查询=$查询ENCLOSED BY'\\\\\\'
查询=$ query行TERMINATED BY'\\ n'\\＆GT;的/ dev / null的游民SSH --commandCD $ vagrant_export_folder＆放大器;＆安培; $查询
 

但通过执行这个脚本我得到以下错误：

 位于第1行错误1064（42000）：你有一个错误的SQL语法;检查对应于你的MySQL服务器版本使用附近的'-safe.wp_blogs INTO OUTFILE'正确的语法手动/无功/网络/项目/网络安全/数据库的/ tmp /博客在1号线
 

连接到127.0.0.1关闭。

这是问题出在架构名称破折号网​​络安全。是否有办法来纠正这个问题？

我知道，有一个选项，只是重新命名模式，但不幸的是我在不稳定的无业游民时运行时，Xubuntu的，有破坏我的数据库了好几次，当我试着规定的修改在我的数据库中。

我也有尝试以下组合，但我得到类似如下错误信息：

 `$ {}模式`.`wp_blogs`
位于第1行错误1064（42000）：你在你的SQL语法错误;检查对应于你的MySQL服务器版本在1号线INTO OUTFILE'/var/www/projects/internet-safe/database/tmp/blogs.csv字段TE附近使用正确的语法手册
`$ {}模式`.wp_blogs
在1号线ERROR 1046（3D000）：没有数据库选择`$ {}模式.wp_blogs`
位于第1行错误1064（42000）：你在你的SQL语法错误;检查对应于你的MySQL服务器版本在1号线INTO OUTFILE'/var/www/projects/internet-safe/database/tmp/blogs.csv字段TE附近使用正确的语法手册
 

解决方案

最好的办法是当然，简单地重命名你的数据库。

通常，当特殊字符出现在模式/表/列名，你可以用反引号解决这个问题。既然你想运行一个shell脚本，它会反引号内跨preT东西作为一个命令，很遗憾。

在MySQL中，你可以设置的sql_modeANSI_QUOTES。以

也这样做管理员/根（在MySQL中）

  MySQL的＆GT; SET GLOBAL的sql_mode =ANSI_QUOTES;
 

首先检查，如果你有一个已经设置的任何模式

  MySQL的＆GT; SHOW VARIABLES LIKE'的sql_mode;
 

如果已经有模式设置，包括那些在 SET GLOBAL的sql_mode 语句（由分离，）。

您也可以使应用此模式每次启动MySQL服务器的时间设定在的my.cnf 文件这个选项。

的作用是，也被允许作为标识符引号字符。但要小心，你不使用在查询中指定任何文本，使用单引号，而不是有

• 阅读更多关于它在这里

I have create a shell script to automate my database migration from host to host, that extracts my Multi WordPress site tables into separated sql files, so I can decide what site I will migrate each time.

So in my shell script I have the following code:

schema="internet-safe"
PROJECT_FOLDER_NAME="internet-safe"
vagrant_export_folder="/var/www/projects/${PROJECT_FOLDER_NAME}/database/tmp"

query="mysql -uroot -proot -e \"SELECT blog_id AS ID, path AS Slug FROM ${schema}.wp_blogs "
query="$query INTO OUTFILE '$vagrant_export_folder/blogs.csv' "
query="$query FIELDS TERMINATED BY ',' "
query="$query ENCLOSED BY '\\\"' "
query="$query LINES TERMINATED BY '\n' \" > /dev/null"

vagrant ssh --command "cd $vagrant_export_folder && $query"

But by executing this script I get the following error:

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-safe.wp_blogs  INTO OUTFILE '/var/www/projects/internet-safe/database/tmp/blogs' at line 1

Connection to 127.0.0.1 closed.

An the problem is the dash in the schema name internet-safe. Is there a way to correct this issue ?

I know that there is an option, to just rename the schema, but, unfortunately I run on unstable vagrant, on xUbuntu, that has destroy my database several times, while I try to provision modifications in my databases.

I also have try the following combinations but I get error messages like the following:

`${schema}`.`wp_blogs`


ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE '/var/www/projects/internet-safe/database/tmp/blogs.csv'  FIELDS TE' at line 1


`${schema}`.wp_blogs


ERROR 1046 (3D000) at line 1: No database selected



`${schema}.wp_blogs`


ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE '/var/www/projects/internet-safe/database/tmp/blogs.csv'  FIELDS TE' at line 1

解决方案

Best option would be of course to simply rename your database.

Usually when special characters appear in schema/table/column names, you can fix this with backticks. Since you're trying to run a shell script, it will interpret anything within backticks as a command, unfortunately.

In MySQL you can set the sql_mode "ANSI_QUOTES". Do so as administrator/root (in MySQL) with

mysql> SET GLOBAL sql_mode="ANSI_QUOTES";

Check first, if you have any modes set already with

mysql> SHOW VARIABLES LIKE 'sql_mode';

If there are already modes set, include those in the SET GLOBAL sql_mode statement (separated by ,).

You can also set this option in your my.cnf file, so that this mode is applied every time you start your MySQL server.

The effect is, that " is also allowed as identifier quote character. But be careful, that you don't use " in queries to specify any text, use single-quotes there instead.

• read more about it here

这篇关于与破折号的MySQL架构名称不允许我执行命令行查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！