如何从 bash 提供 mysql 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6150675/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to feed mysql queries from bash
提问by tirithen
I'm trying to make a bash script that creates a mysql user and database but I can't find a way to feed the sql into mysql, I'm trying with this format:
我正在尝试制作一个创建 mysql 用户和数据库的 bash 脚本，但我找不到将 sql 输入 mysql 的方法，我正在尝试使用以下格式：
mysql < echo "query"
But that is not working, see the example below:
但这不起作用，请参见下面的示例：
mysql --host=localhost --user=user --password=password < echo "CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'jakdJxct8W';
CREATE DATABASE IF NOT EXISTS 'testuser_dev' DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
GRANT ALL PRIVILEGES ON 'testuser_dev' . * TO 'testuser'@'localhost';
CREATE DATABASE IF NOT EXISTS 'testuser_qa' DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
GRANT ALL PRIVILEGES ON 'testuser_qa' . * TO 'testuser'@'localhost';"
How to feed mysql with the queries?
如何向 mysql 提供查询？
采纳答案by Prince John Wesley
Try like this:
像这样尝试：
echo "select 1" | mysql
回答by Sorrow
Have you tried mysql -e query?
你试过mysql -e query吗？
回答by dogbane
Try using a here documentlike this:
尝试使用这样的here 文档：
mysql --host=localhost --user=user --password=password << END
CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'jakdJxct8W';
CREATE DATABASE IF NOT EXISTS 'testuser_dev' DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
GRANT ALL PRIVILEGES ON 'testuser_dev' . * TO 'testuser'@'localhost';
CREATE DATABASE IF NOT EXISTS 'testuser_qa' DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
GRANT ALL PRIVILEGES ON 'testuser_qa' . * TO 'testuser'@'localhost';
END
Alternatively place all you commands in text file and run it:
或者将所有命令放在文本文件中并运行它：
mysql --host=localhost --user=user --password=password < commands.sql
回答by Ken
mysql --batch --silent -e 'SHOW TABLES';
Batch and silent are handy if you are planning to pipe the output
如果您打算通过管道传输输出，则批处理和静默很方便
回答by tim
The reason your attempt did not work was because the <expects a file name and you fed it a string. You would have to do something like
您的尝试无效的原因是因为它<需要一个文件名，而您给它提供了一个字符串。你必须做类似的事情
echo "YOURQUERYSTRINGHERE">tmpfile
mysql --host=localhost --user=user --password=password dbname <tmpfile
ken's suggestion of
肯的建议
mysql --host=localhost --user=user --password=password -e "QUERY" dbname
can work, but if you try to use bash variables in your query you can fall foul of parameter expansion. eg
可以工作，但是如果您尝试在查询中使用 bash 变量，您可能会遇到参数扩展的问题。例如
QUERY="select * from $MYTABLE WHERE name=\"[email protected]\";"
mysql --host=localhost --user=user --password=password -e "$QUERY" mydbname
may not do what you expect. One option is use
可能不会做你期望的。一种选择是使用
echo "$QUERY"|mysql --host=localhost --user=user --password=password mydbname
which works if the query string contains appropriate quoting. Another option is the "here" document as suggested by dogbane.
如果查询字符串包含适当的引用，这将起作用。另一种选择是 dogbane 建议的“here”文档。
回答by Daniel Pérez Rada
For big queries in a bash script, you can try:
对于 bash 脚本中的大查询，您可以尝试：
read -d '' SQL_QUERY_1 << EOF
SELECT prod.id as id, prod.title as title, comp.name as company, pho.id as photo_id, pho.image as photo_name
FROM products as prod
JOIN accounts as comp
ON comp.id = prod.account_id
JOIN photos as pho
ON pho.id = prod.featured_photo_id;
EOF
echo ${SQL_QUERY_1} | mysql
回答by willgriffin
cat <<EOD | mysql [-u user] [-ppassword] [database]
select 1;
select 2;
select 3;
EOD
in your case
在你的情况下
cat <<EOD | mysql -u root -p
CREATE DATABASE IF NOT EXISTS testuser_dev DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
GRANT ALL PRIVILEGES ON testuser_dev.* TO "testuser"@"localhost";
CREATE DATABASE IF NOT EXISTS testuser_qa DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
GRANT ALL PRIVILEGES ON testuser_qa.* TO "testuser"@"localhost";
FLUSH PRIVILEGES;
EOD
