如何在MySQL中运行SQL脚本?

I am new to MySQL. I want to execute a text file containing SQL queries.

我是MySQL新手。我想执行一个包含SQL查询的文本文件。

I tried to run source /Desktop/test.sql and received the error,

我尝试运行源代码/桌面/测试。sql接收错误，

mysql> . \home\sivakumar\Desktop\test.sql ERROR: Failed to open file '\home\sivakumar\Desktop\test.sql', error: 2

16 个解决方案

#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主机名-u用户数据库< path/to/test.sql
• 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.

我这样使用它是因为当你把它和“”连在一起时，你避免了错误的路径和空格的错误——可能还有更多我没有遇到的chars问题。

---

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

Give the path of .sql file as:

将.sql文件的路径设置为:

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

#7

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

#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 -pEnter password:

#9

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.

使用没有报价。即使路径包含空格(')，也不要使用引号。

#10

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

很有可能，您只需要更改斜线/黑线:from

 \home\sivakumar\Desktop\test.sql

to

来

 /home/sivakumar/Desktop/test.sql

So the command would be:

所以命令是:

source /home/sivakumar/Desktop/test.sql

#11

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

#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

源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你的数据库< text_file没有在远程服务器上工作(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 > >源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.

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

#16

You can use SQLyog's Execute SQL script to execute the .sql file. Select the file and press Execute. :-) You get 30 day free trial.

您可以使用SQLyog的Execute SQL脚本执行. SQL文件。选择文件并按Execute。你得到30天免费试用。

And, you are getting the error because you have quotes around the file name.

你会得到错误，因为你引用了文件名。

The file must be located on the client host where you're running mysql. The filename must either be an absolute pathname listing the full name of the file, or a pathname that's specified relative to the directory in which you invoked mysql. For example, if you started mysql on a Windows machine in the C:\mysql directory and your script file is my_commands.sql in the C:\scripts directory, both of the following SOURCE commands tell mysql to execute the SQL statements in the file:

文件必须位于正在运行mysql的客户端主机上。文件名必须是列出文件全名的绝对路径名，或者是相对于调用mysql的目录指定的路径名。例如，如果您在C:\mysql目录下的Windows机器上启动了mysql，那么您的脚本文件就是my_commands。在C:\scripts目录中的sql，以下两个源命令都告诉mysql执行文件中的sql语句:

mysql> SOURCE C:\scripts\my_commands.sql;

mysql >源C:\ \ my_commands.sql脚本;

mysql> SOURCE ..\scripts\my_commands.sql;

mysql >源. . \ \ my_commands.sql脚本;

The other way to execute a script file is by naming it on the mysql command line. Invoke mysql and use the < input redirection operator to specify the file from which to read query input:

执行脚本文件的另一种方法是在mysql命令行中命名它。调用mysql并使用< input redirection操作符指定要从中读取查询输入的文件:

shell> mysql db_name < input_file

shell> mysql db_name < input_file