MySQL 使用shell脚本将数据插入远程MYSQL数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3900496/
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
Using shell script to insert data into remote MYSQL database
提问by Amir
I've been trying to get a shell(bash) script to insert a row into a REMOTE database, but i've been having some trouble :(
我一直在尝试使用 shell(bash) 脚本将一行插入到远程数据库中，但我遇到了一些麻烦:(
The script is meant to upload a file to a server, get a URL,HASH, and a filesize, connect to a remote mysql database, and insert the data into an existing table. I've gotten it working until the remote MYSQL database bit.
该脚本旨在将文件上传到服务器，获取 URL、HASH 和文件大小，连接到远程 mysql 数据库，并将数据插入现有表中。我已经让它工作到远程 MYSQL 数据库位。
It looks like this:
它看起来像这样：
#!/bin/bash
zxw=randomtext
description=randomtext2
for file in "$@"
do
echo -n *****
ident= *****
data= ****
size=` ****
hash=`****
mysql --host=randomhost --user=randomuser --password=randompass randomdb
insert into table (field1,field2,field3) values('http://www.site.com/$hash','$file','$size');
echo "done"
done
I'm a total noob at programming so yeh :P
我在编程方面完全是个菜鸟，所以是的：P
Anyway, I added the \ to escape the brackets as I was getting errors. As it is right now, the script is works fine until connects to the mysql database. It just connects to the mysql database and doesn't do the insert command (and i dont even know if the insert command would work in bash).
无论如何，当我遇到错误时，我添加了 \ 来转义括号。就像现在一样，在连接到 mysql 数据库之前，脚本可以正常工作。它只是连接到 mysql 数据库并且不执行插入命令（我什至不知道插入命令是否可以在 bash 中工作）。
PS: I've tried both the mysql commands from the command line one by one, and they worked, though I defined the hash/file/size and didn't have the escaping "\".
PS：我已经一一尝试了命令行中的两个mysql命令，它们都有效，尽管我定义了哈希/文件/大小并且没有转义“\”。
Anyway, what do you guys think? Is what im trying to do even possible? If so how?
总之，大家怎么看？我试图做的甚至可能吗？如果是这样怎么办？
Any help would be appreciated :)
任何帮助，将不胜感激 ：）
回答by Paul Tomblin
The insert statement has to be sent to mysql, not another line in the shell script, so you need to make it a "here document".
insert 语句必须发送到 mysql，而不是 shell 脚本中的另一行，因此您需要将其设为“此处文档”。
mysql --host=randomhost --user=randomuser --password=randompass randomdb << EOF
insert into table (field1,field2,field3) values('http://www.site.com/$hash','$file','$size');
EOF
The << EOFmeans take everything before the next line that contains nothing but EOF(no whitespace at the beginning) as standard input to the program.
这<< EOF意味着将下一行之前的所有内容都包含在内EOF（开头没有空格）作为程序的标准输入。
回答by kmarks2
This might not be exactly what you are looking for but it is an option.
这可能不是您正在寻找的，但它是一种选择。
If you want to bypass the annoyance of actually including your query in the sh script, you can save the query as .sql file (useful sometimes when the query is REALLY big and complicated). This can be done with simple file IO in whatever language you are using.
如果您想绕过在 sh 脚本中实际包含查询的烦恼，您可以将查询保存为 .sql 文件（有时当查询非常大且复杂时很有用）。这可以通过您使用的任何语言的简单文件 IO 来完成。
Then you can simply include in your sh scrip something like:
然后你可以简单地在你的 sh 脚本中包含如下内容：
mysql -u youruser -p yourpass -h remoteHost < query.sql &
This is called batch mode execution. Optionally, you can include the ampersand at the end to ensure that that line of the sh script does not block.
这称为批处理模式执行。或者，您可以在末尾包含与号，以确保 sh 脚本的该行不会阻塞。
Also if you are concerned about the same data getting entered multiple times and your rdbms getting inconsistent, you should explore MySql transactions (commit, rollback, etc).
此外，如果您担心多次输入相同的数据并且您的 rdbms 变得不一致，您应该探索 MySql 事务（提交、回滚等）。
回答by Ignacio Vazquez-Abrams
Don't use raw SQL from bash; bash has no sane facility for sanitizing the data beforehand. Generate a CSV file and upload that instead.
不要使用来自 bash 的原始 SQL；bash 没有用于事先清理数据的健全设施。生成一个 CSV 文件并上传。
