数据的备份(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可备份所有数据库。

备份数据库testtest_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内容如下,
http://7xibui.com1.z0.glb.clouddn.com/2015/03/blob9.png

最后试试保存成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