在 MySQL 中导出和导入数据 · ZetCode 中文系列教程

# 在 MySQL 中导出和导入数据 > 原文： [http://zetcode.com/databases/mysqltutorial/exportimport/](http://zetcode.com/databases/mysqltutorial/exportimport/) 在 MySQL 教程的这一部分中，我们将从 MySQL 数据库导出数据并将数据导入回来。 ## 简单的数据导出在第一个示例中，我们将数据保存在文本文件中。 ```sql mysql> SELECT * FROM Cars INTO OUTFILE '/tmp/cars'; Query OK, 8 rows affected (0.00 sec) ``` 我们从`Cars`表中选择所有行（8）到`/tmp`目录中的`cars`文件中。我们需要具有写入该目录的权限。 ```sql $ cat /tmp/cars 1 Audi 52642 2 Mercedes 57127 3 Skoda 9000 4 Volvo 29000 5 Bentley 350000 6 Citroen 21000 7 Hummer 41400 8 Volkswagen 21600 ``` 我们显示文件的内容。 ```sql mysql> DELETE FROM Cars; mysql> LOAD DATA INFILE '/tmp/cars' INTO TABLE Cars; ``` 在第一条语句中，我们删除表中的所有行。在第二条语句中，我们将所有数据从文本文件加载到`Cars`表中。 ```sql mysql> SELECT * FROM Cars INTO OUTFILE '/tmp/cars.csv' -> FIELDS TERMINATED BY ','; ``` 在上面的 SQL 语句中，我们将`Cars`表中的所有数据转储到`cars.csv`文件中。 `FIELDS TERMINATED BY`子句控制在文本文件中如何终止数据。我们选择了一个逗号字符。 CSV 代表逗号分隔值，它是一种非常常见且可移植的文件格式。它可以由许多其他应用（如 OpenOffice，其他数据库等）导入。 ```sql $ cat /tmp/cars.csv 1,Audi,52642 2,Mercedes,57127 3,Skoda,9000 4,Volvo,29000 5,Bentley,350000 6,Citroen,21000 7,Hummer,41400 8,Volkswagen,21600 ``` 这是`cars.csv`文件的内容。 ```sql mysql> DELETE FROM Cars; mysql> LOAD DATA INFILE '/tmp/cars.csv' INTO TABLE Cars -> FIELDS TERMINATED BY ','; mysql> SELECT * FROM Cars; +----+------------+--------+ | Id | Name | Cost | +----+------------+--------+ | 1 | Audi | 52642 | | 2 | Mercedes | 57127 | | 3 | Skoda | 9000 | | 4 | Volvo | 29000 | | 5 | Bentley | 350000 | | 6 | Citroen | 21000 | | 7 | Hummer | 41400 | | 8 | Volkswagen | 21600 | +----+------------+--------+ ``` 我们删除所有数据，然后从`cars.csv`文件中将其还原。 ## 导出到 XML 文件可以使用`mysql`监视器导出和导入 XML 数据。 ```sql $ mysql -uroot -p --xml -e 'SELECT * FROM mydb.Cars' > /tmp/cars.xml ``` `mysql`监视器具有`--xml`选项，使我们能够以 XML 格式转储数据。 `-e`选项执行一条语句并退出监视器。 ```sql $ cat /tmp/cars.xml <?xml version="1.0"?> <resultset statement="SELECT * FROM mydb.Cars " xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <row> <field name="Id">1</field> <field name="Name">Audi</field> <field name="Cost">52642</field> </row> <row> <field name="Id">2</field> <field name="Name">Mercedes</field> <field name="Cost">57127</field> </row> <row> <field name="Id">3</field> <field name="Name">Skoda</field> <field name="Cost">9000</field> </row> <row> <field name="Id">4</field> <field name="Name">Volvo</field> <field name="Cost">29000</field> </row> <row> <field name="Id">5</field> <field name="Name">Bentley</field> <field name="Cost">350000</field> </row> <row> <field name="Id">6</field> <field name="Name">Citroen</field> <field name="Cost">21000</field> </row> <row> <field name="Id">7</field> <field name="Name">Hummer</field> <field name="Cost">41400</field> </row> <row> <field name="Id">8</field> <field name="Name">Volkswagen</field> <field name="Cost">21600</field> </row> </resultset> ``` 这是`mysql`监视器生成的 XML 文件。 ```sql mysql> TRUNCATE Cars; mysql> LOAD XML /tmp/cars.xml INTO TABLE Cars; ``` 我们截断`Cars`表。我们从 XML 文件加载数据。请注意，`LOAD XML`语句可用于 MySQL 5.5 及更高版本。 ## 使用`mysqldump`工具 `mysqldump`是用于为 MySQL 创建备份的命令工具。当我们将数据从一个地方传输到另一个地方时，会使用转储一词。从数据库文件到文本文件。从内存到文件。和类似。 ### 转储表结构 ```sql mysqldump -u root -p --no-data mydb > bkp1.sql ``` 上面的命令将`mydb`数据库中所有表的表结构转储到`bkq1.sql`文件中。 `--no-data`选项导致不保存数据，仅保存表结构。 ```sql -- -- Table structure for table `Cars` -- DROP TABLE IF EXISTS `Cars`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `Cars` ( `Id` int(11) NOT NULL, `Name` varchar(50) DEFAULT NULL, `Cost` int(11) DEFAULT NULL, PRIMARY KEY (`Id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; ``` 在这里，我们看到了`bkp1.sql`文件的一部分。这是用于创建`Cars`表的 SQL。 ### 仅转储数据 ```sql $ mysqldump -uroot -p --no-create-info mydb > bkp2.sql ``` 此命令转储`mydb`数据库的所有表中的所有数据。它省略了表结构。表结构的遗漏是由`--no-create-info`选项引起的。 ```sql -- -- Dumping data for table `Cars` -- LOCK TABLES `Cars` WRITE; /*!40000 ALTER TABLE `Cars` DISABLE KEYS */; INSERT INTO `Cars` VALUES (1,'Audi',52642),(2,'Mercedes',57127),(3,'Skoda',9000), (4,'Volvo',29000),(5,'Bentley',350000),(6,'Citroen',21000), (7,'Hummer',41400),(8,'Volkswagen',21600); /*!40000 ALTER TABLE `Cars` ENABLE KEYS */; UNLOCK TABLES; ``` 在这里，我们可以看到`Cars`表的数据。 ### 转储整个数据库 ```sql $ mysqldump -uroot -p mydb > bkp3.sql ``` 此命令将所有表从`mydb`数据库转储到`bkp3.sql`文件。 ### 恢复数据我们展示了如何从备份 SQL 文件还原数据库。 ```sql mysql> DROP DATABASE mydb; ERROR 1010 (HY000): Error dropping database (can't rmdir './mydb/', errno: 17) mysql> SHOW TABLES; Empty set (0.00 sec) ``` 我们删除`mydb`数据库。显示错误。表已删除，但数据库未删除。 ```sql $ sudo ls /var/lib/mysql/mydb cars cars.txt $ sudo rm /var/lib/mysql/mydb/cars $ sudo rm /var/lib/mysql/mydb/cars.txt ``` 原因是（在我的情况下）在进行备份时，一些数据写入了`mydb`目录，MySQL 在其中存储了`mydb`数据库。这两个外来文件无法删除，因此出现上述错误。通过删除文件，错误已得到解决。 ```sql mysql> DROP DATABASE mydb; Query OK, 0 rows affected (0.04 sec) mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | testdb | | world | +--------------------+ 4 rows in set (0.00 sec) ``` `mydb`数据库已完全删除。 ```sql mysql> CREATE DATABASE mydb; mysql> USE mydb; mysql> source bkp3.sql ``` 我们创建`mydb`数据库。更改为数据库。并使用`source`命令执行`bkp3.sql`脚本。重新创建数据库。 ```sql mysql> SHOW TABLES; +----------------+ | Tables_in_mydb | +----------------+ | AA | | Ages | | Animals | | Authors | | BB | | Books | | Books2 | | Brands | | Cars | ... mysql> SELECT * FROM Cars; +----+------------+--------+ | Id | Name | Cost | +----+------------+--------+ | 1 | Audi | 52642 | | 2 | Mercedes | 57127 | | 3 | Skoda | 9000 | | 4 | Volvo | 29000 | | 5 | Bentley | 350000 | | 6 | Citroen | 21000 | | 7 | Hummer | 41400 | | 8 | Volkswagen | 21600 | +----+------------+--------+ ``` 数据已验证。在 MySQL 教程的这一部分中，我们展示了几种在 MySQL 中导出和导入数据的方法。