如何在MySQL中运行SQL脚本？

I want to execute a text file containing SQL queries, in MySQL.

我想在MySQL中执行包含SQL查询的文本文件。

I tried to run source /Desktop/test.sql and 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?

对我做错了什么的任何想法？

15 个解决方案

#1

If you’re at the MySQL command line mysql> you have to declare the SQL file as source.

如果您在MySQL命令行mysql>，则必须将SQL文件声明为源。

mysql> source \home\user\Desktop\test.sql;

#2

You have quite a lot of options:

你有很多选择：

• use the MySQL command line client: mysql -h hostname -u user database < path/to/test.sql
• 使用MySQL命令行客户端：mysql -h hostname -u user database
• Install the MySQL GUI tools and open your SQL file, then execute it
• 安装MySQL GUI工具并打开SQL文件，然后执行它
• Use phpmysql if the database is available via your webserver
• 如果数据库可通过您的网络服务器使用，请使用phpmysql

#3

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

#4

All the top answers are good. But just in case someone wants to run the query from a text file on a remote server AND save 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.

希望这有助于某人。

#5

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

执行命令而不将其保留在历史记录中

#6

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

#7

Give the path of .sql file as:

将.sql文件的路径指定为：

source c:/dump/SQL/file_name.sql;

#8

Never is a good practice to pass the password argument directly from the command line, it is saved in the ~/.bash_history file 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:

#9

Very likely, you just need to change the slash/blackslash: from

很可能，你只需要更改斜杠/ blackslash：from

 \home\sivakumar\Desktop\test.sql

to

至

 /home/sivakumar/Desktop/test.sql

So the command would be:

所以命令是：

source /home/sivakumar/Desktop/test.sql

#10

mysql -uusername -ppassword database-name < file.sql

#11

use the following from mysql command prompt-

从mysql命令提示符中使用以下命令 -

source \\home\\user\\Desktop\\test.sql;

Use no quotation. Even if the path contains space(' ') use no quotation at all.

不使用报价单。即使路径包含空格（''）也根本不使用引号。

#12

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.x.x

1. Access mysql using:
2. 访问mysql使用：

mysql -u <your_user> - p

mysql -u - p

2. At the mysql prompt, enter:
3. 在mysql提示符下，输入：

source file_name.sql

source file_name.sql

Hope this helps.

希望这可以帮助。

#13

Since mysql -u yourusername -p yourpassword yourdatabase < text_file did not work on a remote server (Amazon's EC2)...

由于mysql -u yourusername -p yourpassword yourdatabase 无法在远程服务器（amazon的ec2）上运行...

Make sure that the Database is created first.

确保首先创建数据库。

Then:

然后：

mysql --host=localhost --user=your_username --password=your_password your_database_name < pathTofilename.sql

#14

For future reference, I've found this to work vs the aforementioned methods, under Windows in your msql console:

为了将来的参考，我发现这与前面提到的方法相比，在msql控制台的Windows下：

mysql>>source c://path_to_file//path_to_file//file_name.sql;

mysql >> source c：//path_to_file//path_to_file//file_name.sql;

If your root drive isn't called "c" then just interchange with what your drive is called. First try backslashes, if they dont work, try the forward slash. If they also don't work, ensure you have your full file path, the .sql extension on the file name, and if your version insists on semi-colons, ensure it's there and try again.

如果您的根驱动器未被称为“c”，那么只需与驱动器的名称进行交换即可。首先尝试反斜杠，如果它们不起作用，请尝试正斜杠。如果它们也不起作用，请确保您拥有完整的文件路径，文件名上的.sql扩展名，如果您的版本坚持使用分号，请确保它在那里然后重试。

#15

I had this error, and tried all the advice i could get to no avail.

我有这个错误，并尝试了所有的建议，我可以得到无济于事。

Finally, the problem was that my folder had a space in the folder name which appearing as a forward-slash in the folder path, once i found and removed it, it worked fine.

最后，问题是我的文件夹在文件夹名称中有一个空格，在文件夹路径中显示为正斜杠，一旦我找到并删除它，它工作正常。