Mysql 简明教程

MySQL - Database Import

正如我们在前一教程“数据库导出”中学到的，现在我们将学习如何将导出的数据或备份导入到现有的 MySQL 数据库中。此过程被称为 database import 。

As we learned in the previous tutorial about 'Database Export', now we’ll learn how to import the exported data, or backup, into an existing MySQL database. This process is known as database import.

在 MySQL 中，要将现有转储或备份文件导入数据库，我们使用 mysql 命令行工具。

In MySQL, to import an existing dump or backup file into a database, we use the mysql command-line tool.

Importing Backup Data

我们可以使用 mysql 命令行工具将备份数据导入 MySQL 数据库。它采用用户名、数据库名称以及包含数据的备份文件。

We can import the backup data into an MySQL database using the mysql command-line tool. It takes the username, database name, and the backup file with the data.

Syntax

以下是 mysql 命令行工具的语法：

Following is the syntax of mysql command-line tool −

$ mysql -u username -p new_database_name < dumpfile_path

其中，

Where,

  1. username: This is the MySQL username to use when connecting to the MySQL server.

  2. new_database_name: The name of the database where you want to import the data.

  3. dumpfile_path: It is the path of the backup file. The data will be imported from this file.

  4. <: This symbol imports the data from the file named output_file_path.

Example

在此示例中，我们将导入在前一教程（“数据库导出”）中生成的文件“data-dump.sql”。该文件包含名为“CUSTOMERS”的表。

In this example, we will import the file named "data-dump.sql" that was generated in the previous tutorial (Database Export). The file contains a table named 'CUSTOMERS'.

在执行此操作之前，让我们以用户身份登录 MySQL 服务器，以创建新数据库：

Before doing that, let us login to MySQL server as a user to create a new databases −

$ mysql -u root -p

登录后，它会将你带入 MySQL 命令行。现在，使用以下查询创建一个名为 testdb 的新数据库：

After logging in, it will bring you into MySQL command-line. Now, create a new database named testdb using the below query −

CREATE DATABASE testdb;

当我们执行上述查询时，输出将获得如下：

When we execute the above query, the output is obtained as follows −

Query OK, 1 row affected (0.01 sec)

要退出 MySQL 命令行，请执行 \q 。现在，从常规命令行中，我们可以使用以下查询导入转储文件“data-dump.sql”。

To exit from the MySQL command-line, execute \q. Now, from the normal command line, we can import the dump file 'data-dump.sql' using the following query.

一旦我们执行以下语句，就需要输入 MySQL 服务器密码。

Once we execute the below statement, we need to enter our MySQL server password.

$ mysql -u root -p testdb < data-dump.sql

如果上述命令成功运行，它将不会显示任何可见的输出。相反，它会导入数据。如果在执行期间发生任何错误，MySQL 将它们显示到终端。

If the above command is runs successfully, it won’t show any visible output. Instead, it imports the data. If any error occur during the execution, MySQL will display them to the terminal.

Verification

要验证导入是否成功，执行以下查询以登录到 MySQL 命令行：

To verify whether the import was successful, execute the following query to login into MySQL command-line −

$ mysql -u root -p

现在，使用以下 MySQL 'Use' 查询选择 'testdb' 中的当前数据库：

Now, select the current database to 'testdb' using the following MySQL 'Use' query −

Use testdb;

执行以下查询以检查文件“data-dump.sql”中名为 CUSTOMERS 的表是否已被导入：

Execute the following query to check whether the table named CUSTOMERS in "data-dump.sql" file has been imported or not −

Show Tables;

正如我们可以在下面看到的输出中，CUSTOMERS 表已成功导入到新数据库“testdb”中。

As we can see the output below, the CUSTOMERS table has been succesfully imported into the new database 'testdb'.

让我们通过执行以下查询来验证是否已导入记录：

Let us also verify whether the records has been imported or not by executing the below query −

select * from customers;

记录也成功导入。

The records are also successfully imported.