将 SQL 文件导入 mysql

Import SQL file into mysql

提问by kushalbhaktajoshi

I have a database called nitm. I haven't created any tables there. But I have a SQL file which contains all the necessary data for the database. The file is nitm.sqlwhich is in C:\ drive. This file has size of about 103 MB. I am using wamp server.

我有一个名为nitm. 我没有在那里创建任何表。但是我有一个 SQL 文件，其中包含数据库的所有必要数据。该文件nitm.sql位于C:\ drive. 该文件的大小约为 103 MB。我正在使用 wamp 服务器。

I have used the following syntax in MySQL console to import the file:

我在 MySQL 控制台中使用了以下语法来导入文件：

mysql>c:/nitm.sql;

But this didn't work.

但这没有用。

采纳答案by kushalbhaktajoshi

Finally, i solved the problem. I placed the `nitm.sql` file in `bin` file of the `mysql` folder and used the following syntax.

最后，我解决了这个问题。我将 `nitm.sql` 文件放在 `mysql` 文件夹的 `bin` 文件中，并使用以下语法。

C:\wamp\bin\mysql\mysql5.0.51b\bin>mysql -u root nitm < nitm.sql

And this worked.

这奏效了。

回答by d-_-b

From the mysql console:

从 mysql 控制台：

mysql> use DATABASE_NAME;

mysql> use DATABASE_NAME;

mysql> source path/to/file.sql;

mysql> source path/to/file.sql;

make sure there is no slash before path if you are referring to a relative path... it took me a while to realize that! lol

如果您指的是相对路径，请确保路径前没有斜线……我花了一段时间才意识到这一点！哈哈

回答by Robert Anthony Tribiana

If you are using wamp you can try this. Just type use your_Database_namefirst.

如果你使用 wamp，你可以试试这个。use your_Database_name先打字就行。

1. Click your wamp server icon then look for MYSQL > MSQL Consolethen run it.

2. If you dont have password, just hit enter and type :

   mysql> use database_name;
   mysql> source location_of_your_file;
   

   If you have password, you will promt to enter a password. Enter you password first then type:

   mysql> use database_name;
   mysql> source location_of_your_file;
   

1. 单击您的 wamp 服务器图标，然后查找MYSQL > MSQL Console然后运行它。

2. 如果您没有密码，只需按回车键并输入：

   mysql> use database_name;
   mysql> source location_of_your_file;
   

   如果您有密码，您将提示输入密码。先输入密码，然后输入：

   mysql> use database_name;
   mysql> source location_of_your_file;
   

location_of_your_fileshould look like C:\mydb.sql

location_of_your_file应该看起来像 C:\mydb.sql

so the commend is mysql>source C:\mydb.sql;

所以推荐是mysql>source C:\mydb.sql;

This kind of importing sql dump is very helpful for BIG SQL FILE.

这种导入sql dump对BIG SQL FILE非常有帮助。

I copied my file mydb.sqto directory C:.It should be capital C: in order to run

我将我的文件复制mydb.sq到目录。C:它应该是大写的 C: 为了运行

and that's it.

就是这样。

回答by user3219217

In windows, if the above suggestion gives you an error (file not found or unknown db) you may want to double the forward slashes:

在 Windows 中，如果上述建议给您一个错误（找不到文件或未知数据库），您可能需要将正斜杠加倍：

In the mysql console:

在 mysql 控制台中：

mysql> use DATABASE_NAME;

mysql> source C://path//to//file.sql;

回答by peterb

Ok so, I'm using Linux but I think this holds true for Windows too. You can do this either directly from the command prompt

好的，我使用的是 Linux，但我认为这也适用于 Windows。您可以直接从命令提示符执行此操作

> mysql -u <user name> -p<password> <database name> < sqlfilename.sql

Or from within the mysql prompt, you can use:

或者在 mysql 提示符下，您可以使用：

mysql>source sqlfilename.sql

But both these approaches have their own benefits in the results they display. In the first approach, the script exits as soon as it encounters an error. And the better part, is that it tells you the exact line number in the source file where the error occurred. However, it ONLY displays errors. If it didn't encounter any errors, the scripts displays NOTHING. Which can be a little unnerving. Because you're most often running a script with a whole pile of commands.

但是这两种方法在它们显示的结果中都有自己的好处。在第一种方法中，脚本在遇到错误时立即退出。更好的部分是它告诉您发生错误的源文件中的确切行号。但是，它只显示错误。如果没有遇到任何错误，脚本将不显示任何内容。这可能有点令人不安。因为您最常运行带有一大堆命令的脚本。

Now second approach (from within the mysql prompt) has the benefit that it displays a message for every different MySQL command in the script. If it encounters errors, it displays the mysql error message but continues on through the scripts. This can be good, because you can then go back and fix all the errors before you run the script again. The downside is that it does NOT display the line numbers in the script where the errors were encountered. This can be a bit of a pain. But the error messages are as descriptive so you could probably figure out where the problem is.

现在第二种方法（来自 mysql 提示符）的好处是它为脚本中的每个不同的 MySQL 命令显示一条消息。如果遇到错误，它会显示 mysql 错误消息，但会继续执行脚本。这可能很好，因为您可以在再次运行脚本之前返回并修复所有错误。缺点是它不会在脚本中显示遇到错误的行号。这可能有点痛苦。但是错误消息具有描述性，因此您可能会找出问题所在。

I, for one, prefer the directly-from-OS-command line approach.

一方面，我更喜欢直接来自操作系统的命令行方法。

回答by Nanhe Kumar

If you are using xampp

如果您使用的是 xampp

C:\xampp\mysql\bin\mysql -uroot -p nitm < nitm.sql

回答by Lmwangi

You are almost there use

你快到了，用

mysql> \. c:/nitm.sql;

You may also access help by

您还可以通过以下方式获得帮助

mysql> \?

回答by Adrian P.

For localhost on XAMPP. Open a cmd window and type

对于 XAMPP 上的本地主机。打开一个cmd窗口并输入

cd C:\xampp\mysql\bin
mysql.exe -u root -p

Attention! No semi-colon after -pEnter your password and type

注意力！-p 后没有分号输入您的密码并输入

use database_name;

to select the database you need.

选择您需要的数据库。

Check if your table is there

检查你的桌子是否在那里

show tables;

Import from your sql file

从您的 sql 文件导入

source sqlfile.sql;

I have put my file on C:\xampp\mysql\bin location in order to don't mix up with locations of sql file.

我已将我的文件放在 C:\xampp\mysql\bin 位置，以免与 sql 文件的位置混淆。

回答by Akshay Pethani

Try:

尝试：

mysql -u username -p database_name < file.sql

Check MySQL Options.

检查MySQL 选项。

Note: It is better to use the full path of the SQL file file.sql.

注意：最好使用 SQL 文件的完整路径file.sql。

回答by Robert Quinn

In Linux I navigated to the directory containing the .sql file before starting mysql. The system cursor is now in the same location as the file and you won't need a path. Use source myData.sql where my date is replaced with the name of your file.

在 Linux 中，我在启动 mysql 之前导航到包含 .sql 文件的目录。系统光标现在与文件位于同一位置，您不需要路径。使用 source myData.sql ，其中 my date 替换为您的文件名。

cd whatever directory

mysql - p

---

---

connect targetDB

连接目标数据库

source myData.sql

Done

完毕