如何在 MySQL 中运行 SQL 脚本？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8940230/
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 run SQL script in MySQL?
提问by user1160432
I want to execute a text file containing SQL queries, in MySQL.
我想在 MySQL 中执行一个包含 SQL 查询的文本文件。
I tried to run source /Desktop/test.sqland received the error:
我试图运行source /Desktop/test.sql并收到错误：
mysql> . \home\sivakumar\Desktop\test.sql ERROR: Failed to open file '\home\sivakumar\Desktop\test.sql', error: 2
mysql> . \home\sivakumar\Desktop\test.sql 错误：无法打开文件 '\home\sivakumar\Desktop\test.sql'，错误：2
Any idea on what I am doing wrong?
知道我做错了什么吗？
回答by Thomas Edwards
If you're at the MySQL command line mysql>you have to declare the SQL file as source.
如果您在 MySQL 命令行mysql>，则必须将 SQL 文件声明为source.
mysql> source \home\user\Desktop\test.sql;
回答by Eugen Rieck
You have quite a lot of options:
你有很多选择：
- use the MySQL command line client:
mysql -h hostname -u user database < path/to/test.sql - Install the MySQL GUI tools and open your SQL file, then execute it
- Use phpmysql if the database is available via your webserver
- 使用 MySQL 命令行客户端：
mysql -h hostname -u user database < path/to/test.sql - 安装 MySQL GUI 工具并打开您的 SQL 文件，然后执行它
- 如果数据库可通过您的网络服务器使用，请使用 phpmysql
回答by Paul Preibisch
you can execute mysql statements that have been written in a text file using the following command:
您可以使用以下命令执行已写入文本文件的 mysql 语句：
mysql -u yourusername -p yourpassword yourdatabase < text_file
if yourdatabase has not been created yet, log into your mysql first using:
如果您的数据库尚未创建，请先使用以下命令登录您的 mysql：
mysql -u yourusername -p yourpassword yourdatabase
then:
然后：
mysql>CREATE DATABASE a_new_database_name
then:
然后：
mysql -u yourusername -p yourpassword a_new_database_name < text_file
that should do it!
应该这样做！
More info here: http://dev.mysql.com/doc/refman/5.0/en/mysql-batch-commands.html
更多信息：http: //dev.mysql.com/doc/refman/5.0/en/mysql-batch-commands.html
回答by talsibony
My favorite option to do that will be:
我最喜欢的选择是：
mysql --user="username" --database="databasename" --password="yourpassword" < "filepath"
I use it this way because when you string it with "" you avoiding wrong path and mistakes with spaces and - and probably more problems with chars that I did not encounter with.
我这样使用它是因为当你用 "" 将它串起来时，你可以避免错误的路径和空格错误 - 可能还有更多我没有遇到的字符问题。
With @elcuco comment I suggest using this command with [space] before so it tell bash to ignore saving it in history, this will work out of the box in most bash.
使用@elcuco 注释，我建议在使用 [space] 之前使用此命令，这样它会告诉 bash 忽略将其保存在历史记录中，这在大多数 bash 中都是开箱即用的。
in case it still saving your command in history please view the following solutions:
如果它仍然将您的命令保存在历史记录中，请查看以下解决方案：
Execute command without keeping it in history
extra security edit
额外的安全编辑
Just in case you want to be extra safe you can use the following command and enter the password in the command line input:
以防万一您想更加安全，您可以使用以下命令并在命令行输入中输入密码：
mysql --user="username" --database="databasename" -p < "filepath"
回答by Bhushan
All the top answers are good. But just in case someone wants to run the query from a text file on a remote server ANDsave results to a file (instead of showing on console), you can do this:
所有顶级答案都很好。但是为了以防万一有人想在远程服务器上运行从一个文本文件中的查询和结果保存到一个文件中（而不是显示在控制台上），你可以这样做：
mysql -u yourusername -p yourpassword yourdatabase < query_file > results_file
Hope this helps someone.
希望这可以帮助某人。
回答by Zac Smith
I came here searching for this answer as well, and here is what I found works the best for me: Note I am using Ubuntu 16.x.x
我也来这里寻找这个答案，这是我发现最适合我的方法：注意我使用的是 Ubuntu 16.xx
- Access mysql using:
- 访问 mysql 使用：
mysql -u <your_user> - p
mysql -u <your_user> - p
- At the mysql prompt, enter:
- 在 mysql 提示符下，输入：
source file_name.sql
source file_name.sql
Hope this helps.
希望这可以帮助。
回答by Shubham Verma
Give the path of .sql file as:
将 .sql 文件的路径指定为：
source c:/dump/SQL/file_name.sql;
回答by Shankar Kalyankar
mysql> source C:\Users\admin\Desktop\fn_Split.sql
Do not specify single quotes.
不要指定单引号。
If the above command is not working, copy the file to c: drive and try again. as shown below,
如果上述命令不起作用，请将文件复制到 c: 驱动器并重试。如下所示，
mysql> source C:\fn_Split.sql
回答by Jorge E. Hernández
Never is a good practice to pass the password argument directly from the command line, it is saved in the ~/.bash_historyfile and can be accessible from other applications.
直接从命令行传递密码参数绝不是一个好习惯，它保存在~/.bash_history文件中并且可以从其他应用程序访问。
Use this instead:
改用这个：
mysql -u user --host host --port 9999 database_name < /scripts/script.sql -p
Enter password:
回答by Мартин Петров
mysql -uusername -ppassword database-name < file.sql
