在 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 中導出和導入數據的方法。