数据库的备份和还原

数据的备份(mysqldump)

使用 mysqldump 命令备份

mysqldump -h host -u user -p[password] dbname [tbname [tbname...]] > filename.sql

先来看看 test 数据库下有哪些表，

mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| books          |
| fruits         |
+----------------+
2 rows in set (0.00 sec)

表中记录，

mysql> select * from books;
+---------+---------------------------+-----------+
| book_id | bk_title                  | copyright |
+---------+---------------------------+-----------+
|   11026 | Guide to MySQL 5.5        |      2008 |
|   11028 | C++ Primer                |      2009 |
|   11033 | Study Html                |      2011 |
|   11035 | How to use php            |      2003 |
|   11041 | Inside vc++               |      2011 |
|   11072 | Teach yourself javascript |      2005 |
|   11078 | Learning MySQL            |      2010 |
+---------+---------------------------+-----------+
7 rows in set (0.00 sec)

mysql> select * from fruits;
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
| a1   |  101 | apple      |    5.20 |
| a2   |  103 | apricot    |    2.20 |
| b1   |  101 | blackberry |   10.20 |
| b2   |  104 | berry      |    7.60 |
| b5   |  107 | xxxx       |    3.60 |
| bs1  |  102 | orange     |   11.20 |
| bs2  |  105 | melon      |    8.20 |
| c0   |  101 | cherry     |    3.20 |
| l2   |  104 | lemon      |    6.40 |
| m1   |  106 | mango      |   15.60 |
| m2   |  105 | xbabay     |    2.60 |
| m3   |  105 | xxtt       |   11.60 |
| o2   |  103 | coconut    |    9.20 |
| t1   |  102 | banana     |   10.30 |
| t2   |  102 | grape      |    5.30 |
| t4   |  107 | xbababa    |    3.60 |
+------+------+------------+---------+
16 rows in set (0.00 sec)

test 数据库中所有表，

F:\workspace\MySQL>mysqldump -h localhost -u root -p test > test.sql

文件 test.sql 内容如下，

-- MySQL dump 10.13  Distrib 5.5.40, for Win32 (x86)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version	5.5.40

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `books`
--

DROP TABLE IF EXISTS `books`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `books` (`book_id` int(11) NOT NULL,
`bk_title` varchar(60) NOT NULL,
`copyright` year(4) NOT NULL,
PRIMARY KEY (`book_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `books`
--

LOCK TABLES `books` WRITE;
/*!40000 ALTER TABLE `books` DISABLE KEYS */;
INSERT INTO `books` VALUES (11026,'Guide to MySQL 5.5',2008),(11028,'C++ Primer',2009),(11033,'Study Html',2011),(11035,'How to use php',2003),(11041,'Inside vc++',2011),(11072,'Teach yourself javascript',2005),(11078,'Learning MySQL',2010);
/*!40000 ALTER TABLE `books` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `fruits`
--

DROP TABLE IF EXISTS `fruits`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `fruits` (`f_id` char(10) NOT NULL,
`s_id` int(11) DEFAULT NULL,
`f_name` char(255) NOT NULL,
`f_price` decimal(8,2) NOT NULL,
PRIMARY KEY (`f_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `fruits`
--

LOCK TABLES `fruits` WRITE;
/*!40000 ALTER TABLE `fruits` DISABLE KEYS */;
INSERT INTO `fruits` VALUES ('a1',101,'apple',5.20),('a2',103,'apricot',2.20),('b1',101,'blackberry',10.20),('b2',104,'berry',7.60),('b5',107,'xxxx',3.60),('bs1',102,'orange',11.20),('bs2',105,'melon',8.20),('c0',101,'cherry',3.20),('l2',104,'lemon',6.40),('m1',106,'mango',15.60),('m2',105,'xbabay',2.60),('m3',105,'xxtt',11.60),('o2',103,'coconut',9.20),('t1',102,'banana',10.30),('t2',102,'grape',5.30),('t4',107,'xbababa',3.60);
/*!40000 ALTER TABLE `fruits` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2015-03-21 15:12:07

备份 test 数据库中某个表，

F:\workspace\MySQL>mysqldump -h localhost -u root -p test fruits > fruits.sql

文件 fruits.sql 内容如下：

-- MySQL dump 10.13  Distrib 5.5.40, for Win32 (x86)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version	5.5.40

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `fruits`
--

DROP TABLE IF EXISTS `fruits`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `fruits` (`f_id` char(10) NOT NULL,
`s_id` int(11) DEFAULT NULL,
`f_name` char(255) NOT NULL,
`f_price` decimal(8,2) NOT NULL,
PRIMARY KEY (`f_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `fruits`
--

LOCK TABLES `fruits` WRITE;
/*!40000 ALTER TABLE `fruits` DISABLE KEYS */;
INSERT INTO `fruits` VALUES ('a1',101,'apple',5.20),('a2',103,'apricot',2.20),('b1',101,'blackberry',10.20),('b2',104,'berry',7.60),('b5',107,'xxxx',3.60),('bs1',102,'orange',11.20),('bs2',105,'melon',8.20),('c0',101,'cherry',3.20),('l2',104,'lemon',6.40),('m1',106,'mango',15.60),('m2',105,'xbabay',2.60),('m3',105,'xxtt',11.60),('o2',103,'coconut',9.20),('t1',102,'banana',10.30),('t2',102,'grape',5.30),('t4',107,'xbababa',3.60);
/*!40000 ALTER TABLE `fruits` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2015-03-21 15:14:43

备份多个数据库

mysqldump -h host -u user -p[password] --databases [dbname [dbname...]]> filename.sql

--all-databases可备份所有数据库。

备份数据库 test 和test_db，

F:\workspace\MySQL>mysqldump -h localhost -u root -p --databases test test_db > test.sql

test.sql 内容就不贴了，篇幅过大。

数据还原

使用 mysql 还原

mysql -h host -u user -p[password] < filename.sql

或者登陆数据库使用, 需要先 use 选定数据库

source filename.sql;

数据迁移

将 www.abc.com 主机上的 MySQL 数据库全部迁移到 www.bcd.com 主机上。

mysqldump -h www.abc.com -u root -ppassword dbname | mysql -h www.bcd.com -u root -ppassword;

表的导出

用SELECT ... INTO OUTFILE 导出文本文件

SELECT columnlist FROM table WHERE condition INTO OUTFILE 'filename' [OPTION]

[OPTION] 选项
    FIELDS TERMINATED BY 'value’
    FIELDS [OPTIONALLY] ENCLOSED BY 'value'
    FIELDS ESCAPED BY 'value'
    LINES STARTING BY 'value'
    LINES TERMINATED BY 'value'

例如，将 fruits 表数据导出到 fruits.txt,

mysql> select * from fruits into outfile 'C:/fruits.txt';
Query OK, 16 rows affected (0.00 sec)
fruits.txt 内容如下，

a1	101	apple	5.20
a2	103	apricot	2.20
b1	101	blackberry	10.20
b2	104	berry	7.60
b5	107	xxxx	3.60
bs1	102	orange	11.20
bs2	105	melon	8.20
c0	101	cherry	3.20
l2	104	lemon	6.40
m1	106	mango	15.60
m2	105	xbabay	2.60
m3	105	xxtt	11.60
o2	103	coconut	9.20
t1	102	banana	10.30
t2	102	grape	5.30
t4	107	xbababa	3.60

也可以设置导出的格式，比如字段之间用逗号隔开，字符串用引号括起来，转义符用? 表示，每行以 < 开头，以 > 结尾。

mysql> select * from fruits into outfile 'C:/fruits.txt'
	-> fields
	->  terminated by ','
	->  optionally enclosed by '\"'
	->  escaped by '\?'
	-> lines
	->  starting by '<'
	->  terminated by '>\n';
Query OK, 16 rows affected (0.02 sec)

导出结果如下，

<"a1",101,"apple",5.20>
<"a2",103,"apricot",2.20>
<"b1",101,"blackberry",10.20>
<"b2",104,"berry",7.60>
<"b5",107,"xxxx",3.60>
<"bs1",102,"orange",11.20>
<"bs2",105,"melon",8.20>
<"c0",101,"cherry",3.20>
<"l2",104,"lemon",6.40>
<"m1",106,"mango",15.60>
<"m2",105,"xbabay",2.60>
<"m3",105,"xxtt",11.60>
<"o2",103,"coconut",9.20>
<"t1",102,"banana",10.30>
<"t2",102,"grape",5.30>
<"t4",107,"xbababa",3.60>

使用 mysqldump 导出文本文件

mysqldump -T out_dir -h host -u root -p dbname [tables][OPTIONS]

OPTIONS 选项：
    --fields-terminated-by=value
    --fields-enclosed-by=value
    --fields-optionally-enclosed-by=value
    --fields-escaped-by=value
    --lines-terminated-by=value

导出 fruits 表字段之间用逗号隔开，字符串用引号括起来，转义符用? 表示。

F:\workspace\MySQL>mysqldump -h localhost -u root -p test fruits -T . --fields-terminated-by=, --fields-optionally-enclosed-by=\" --fields-escaped-by=\? --lines-terminated-by=\n

将在当前目录生成 fruits.sql 和 fruits.txt，内容分别为

-- MySQL dump 10.13  Distrib 5.5.40, for Win32 (x86)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version	5.5.40

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `fruits`
--

DROP TABLE IF EXISTS `fruits`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `fruits` (`f_id` char(10) NOT NULL,
`s_id` int(11) DEFAULT NULL,
`f_name` char(255) NOT NULL,
`f_price` decimal(8,2) NOT NULL,
PRIMARY KEY (`f_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2015-03-21 16:17:50
"a1",101,"apple",5.20
"a2",103,"apricot",2.20
"b1",101,"blackberry",10.20
"b2",104,"berry",7.60
"b5",107,"xxxx",3.60
"bs1",102,"orange",11.20
"bs2",105,"melon",8.20
"c0",101,"cherry",3.20
"l2",104,"lemon",6.40
"m1",106,"mango",15.60
"m2",105,"xbabay",2.60
"m3",105,"xxtt",11.60
"o2",103,"coconut",9.20
"t1",102,"banana",10.30
"t2",102,"grape",5.30
"t4",107,"xbababa",3.60

使用 mysql 指令导出文本文件

mysql -h host -u root -p --execute="SELECT 语句 " dbname > filename.txt
--vertical 参数可把记录分为多行显示
--html 保存成 html
--xml 保存成 xml

导出 fruits 表数据。

F:\workspace\MySQL>mysql -h localhost -u root -p test --execute="select * from fruits;" > fruits.txt

fruits.txt 文件内容如下，

f_id	s_id	f_name	f_price
a1	101	apple	5.20
a2	103	apricot	2.20
b1	101	blackberry	10.20
b2	104	berry	7.60
b5	107	xxxx	3.60
bs1	102	orange	11.20
bs2	105	melon	8.20
c0	101	cherry	3.20
l2	104	lemon	6.40
m1	106	mango	15.60
m2	105	xbabay	2.60
m3	105	xxtt	11.60
o2	103	coconut	9.20
t1	102	banana	10.30
t2	102	grape	5.30
t4	107	xbababa	3.60

记录分行显示，

F:\workspace\MySQL>mysql -h localhost -u root -p test --vertical --execute="select * from fruits;" > fruits.txt

fruits.txt 文件内容如下，

*************************** 1. row ***************************
f_id: a1
s_id: 101
f_name: apple
f_price: 5.20
*************************** 2. row ***************************
f_id: a2
s_id: 103
f_name: apricot
f_price: 2.20
*************************** 3. row ***************************
f_id: b1
s_id: 101
f_name: blackberry
f_price: 10.20
*************************** 4. row ***************************
f_id: b2
s_id: 104
f_name: berry
f_price: 7.60
*************************** 5. row ***************************
f_id: b5
s_id: 107
f_name: xxxx
f_price: 3.60
*************************** 6. row ***************************
f_id: bs1
s_id: 102
f_name: orange
f_price: 11.20
*************************** 7. row ***************************
f_id: bs2
s_id: 105
f_name: melon
f_price: 8.20
*************************** 8. row ***************************
f_id: c0
s_id: 101
f_name: cherry
f_price: 3.20
*************************** 9. row ***************************
f_id: l2
s_id: 104
f_name: lemon
f_price: 6.40
*************************** 10. row ***************************
f_id: m1
s_id: 106
f_name: mango
f_price: 15.60
*************************** 11. row ***************************
f_id: m2
s_id: 105
f_name: xbabay
f_price: 2.60
*************************** 12. row ***************************
f_id: m3
s_id: 105
f_name: xxtt
f_price: 11.60
*************************** 13. row ***************************
f_id: o2
s_id: 103
f_name: coconut
f_price: 9.20
*************************** 14. row ***************************
f_id: t1
s_id: 102
f_name: banana
f_price: 10.30
*************************** 15. row ***************************
f_id: t2
s_id: 102
f_name: grape
f_price: 5.30
*************************** 16. row ***************************
f_id: t4
s_id: 107
f_name: xbababa
f_price: 3.60

保存为 html，

F:\workspace\MySQL>mysql -h localhost -u root -p test --html --execute="select * from fruits;" > fruits.html

fruits.html 内容如下，

最后试试保存成 xml 的，

F:\workspace\MySQL>mysql -h localhost -u root -p test --xml --execute="select * from fruits;" > fruits.xml

fruits.xml 文件内容如下，

<?xml version="1.0"?>

<resultset statement="select * from fruits" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
	<field name="f_id">a1</field>
	<field name="s_id">101</field>
	<field name="f_name">apple</field>
	<field name="f_price">5.20</field>
</row>

<row>
	<field name="f_id">a2</field>
	<field name="s_id">103</field>
	<field name="f_name">apricot</field>
	<field name="f_price">2.20</field>
</row>

<row>
	<field name="f_id">b1</field>
	<field name="s_id">101</field>
	<field name="f_name">blackberry</field>
	<field name="f_price">10.20</field>
</row>

<row>
	<field name="f_id">b2</field>
	<field name="s_id">104</field>
	<field name="f_name">berry</field>
	<field name="f_price">7.60</field>
</row>

<row>
	<field name="f_id">b5</field>
	<field name="s_id">107</field>
	<field name="f_name">xxxx</field>
	<field name="f_price">3.60</field>
</row>

<row>
	<field name="f_id">bs1</field>
	<field name="s_id">102</field>
	<field name="f_name">orange</field>
	<field name="f_price">11.20</field>
</row>

<row>
	<field name="f_id">bs2</field>
	<field name="s_id">105</field>
	<field name="f_name">melon</field>
	<field name="f_price">8.20</field>
</row>

<row>
	<field name="f_id">c0</field>
	<field name="s_id">101</field>
	<field name="f_name">cherry</field>
	<field name="f_price">3.20</field>
</row>

<row>
	<field name="f_id">l2</field>
	<field name="s_id">104</field>
	<field name="f_name">lemon</field>
	<field name="f_price">6.40</field>
</row>

<row>
	<field name="f_id">m1</field>
	<field name="s_id">106</field>
	<field name="f_name">mango</field>
	<field name="f_price">15.60</field>
</row>

<row>
	<field name="f_id">m2</field>
	<field name="s_id">105</field>
	<field name="f_name">xbabay</field>
	<field name="f_price">2.60</field>
</row>

<row>
	<field name="f_id">m3</field>
	<field name="s_id">105</field>
	<field name="f_name">xxtt</field>
	<field name="f_price">11.60</field>
</row>

<row>
	<field name="f_id">o2</field>
	<field name="s_id">103</field>
	<field name="f_name">coconut</field>
	<field name="f_price">9.20</field>
</row>

<row>
	<field name="f_id">t1</field>
	<field name="s_id">102</field>
	<field name="f_name">banana</field>
	<field name="f_price">10.30</field>
</row>

<row>
	<field name="f_id">t2</field>
	<field name="s_id">102</field>
	<field name="f_name">grape</field>
	<field name="f_price">5.30</field>
</row>

<row>
	<field name="f_id">t4</field>
	<field name="s_id">107</field>
	<field name="f_name">xbababa</field>
	<field name="f_price">3.60</field>
</row>
</resultset>

表的导入

使用 LOAD DATA INFILE 方式导入文件

LOAD DATA INFILE 'filename.txt' INTO TABLE tablename [OPTIONS] [IGNORE number LINES]

[OPTION] 选项
        FIELDS TERMINATED BY 'value’
        FIELDS [OPTIONALLY] ENCLOSED BY 'value'
        FIELDS ESCAPED BY 'value'
        LINES STARTING BY 'value'
        LINES TERMINATED BY 'value'
[IGNORE number LINES] 忽略前面 number 行

例如，将下面文件 fruits.txt 导入 fruits 表，

a1	101	apple	5.20
a2	103	apricot	2.20
b1	101	blackberry	10.20
b2	104	berry	7.60
b5	107	xxxx	3.60
bs1	102	orange	11.20
bs2	105	melon	8.20
c0	101	cherry	3.20
l2	104	lemon	6.40
m1	106	mango	15.60
m2	105	xbabay	2.60
m3	105	xxtt	11.60
o2	103	coconut	9.20
t1	102	banana	10.30
t2	102	grape	5.30
t4	107	xbababa	3.60

先删除 fruits 表中数据，

mysql> delete from test.fruits;
Query OK, 16 rows affected (0.05 sec)

导入数据并查看，

mysql> load data infile 'C:/fruits.txt' into table test.fruits;
Query OK, 16 rows affected (0.06 sec)
Records: 16  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from test.fruits;
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
| a1   |  101 | apple      |    5.20 |
| a2   |  103 | apricot    |    2.20 |
| b1   |  101 | blackberry |   10.20 |
| b2   |  104 | berry      |    7.60 |
| b5   |  107 | xxxx       |    3.60 |
| bs1  |  102 | orange     |   11.20 |
| bs2  |  105 | melon      |    8.20 |
| c0   |  101 | cherry     |    3.20 |
| l2   |  104 | lemon      |    6.40 |
| m1   |  106 | mango      |   15.60 |
| m2   |  105 | xbabay     |    2.60 |
| m3   |  105 | xxtt       |   11.60 |
| o2   |  103 | coconut    |    9.20 |
| t1   |  102 | banana     |   10.30 |
| t2   |  102 | grape      |    5.30 |
| t4   |  107 | xbababa    |    3.60 |
+------+------+------------+---------+
16 rows in set (0.00 sec)

忽略前 6 行导入，

mysql> load data infile 'C:/fruits.txt' into table test.fruits ignore 6 lines;
Query OK, 10 rows affected (0.13 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from test.fruits;
+------+------+---------+---------+
| f_id | s_id | f_name  | f_price |
+------+------+---------+---------+
| bs2  |  105 | melon   |    8.20 |
| c0   |  101 | cherry  |    3.20 |
| l2   |  104 | lemon   |    6.40 |
| m1   |  106 | mango   |   15.60 |
| m2   |  105 | xbabay  |    2.60 |
| m3   |  105 | xxtt    |   11.60 |
| o2   |  103 | coconut |    9.20 |
| t1   |  102 | banana  |   10.30 |
| t2   |  102 | grape   |    5.30 |
| t4   |  107 | xbababa |    3.60 |
+------+------+---------+---------+
10 rows in set (0.00 sec)

用 mysqlimport 命令导入文本文件

mysqlimport -h host -u root -p dbname filename.txt [OPTIONS]

[OPTIONS]参数
    --fields-terminated-by=value
    --fields-enclosed-by=value
    --fields-optionally-enclosed-by=value
    --fields-escaped-by=value
    --lines-terminated-by=value
    --ignore-lines=n

例如导入 fruits.txt 到表 fruits 中。

F:\workspace\MySQL>mysqlimport -h localhost -u root -p test fruits.txt
Enter password: ********
test.fruits: Records: 16  Deleted: 0  Skipped: 0  Warnings: 0