如何在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.

我这样使用它是因为，当你用“”字串起来的时候，你会避免错误的路径和错误，而且可能会有更多的问题，而我没有遇到。

#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

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.

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

#9

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

很有可能，您只需要更改斜杠/blackslash:从。

 \home\sivakumar\Desktop\test.sql

to

来

 /home/sivakumar/Desktop/test.sql

So the command would be:

所以命令是:

source /home/sivakumar/Desktop/test.sql

#10

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:

#11

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

#12

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

#13

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扩展名，如果您的版本坚持使用分号，请确保它在那里并再次尝试。

#14

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.

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

#15

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.

希望这个有帮助。

#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文件。选择文件并按下执行。你得到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目录中，两个源命令都告诉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。