mysql中的数据导入与导出

浏览数：21 / 时间：2015年06月12日

为了普及mysql的基本知识，特意弄了这个章节，主要是发现第一次接触的人都不知道怎么弄，或者看不懂，所以这里就详细说下吧

============================================================

数据导入

1.mysqlimport命令行导入数据

在使用mysqlimport命令导入数据时，数据来源文件名要和目标表一致，不想改文件名的话，可以复制一份创建临时文件，示例如下。

建立一个文本users.txt，内容如下：

创建一个表users

使用mysqlimport将users.txt中数据导入users表

PS F:\> mysqlimport -u root -p123456 zz --default-character-set=gbk --fields-terminated-by=‘,‘ f:\users.txtzz.users: Records: 3  Deleted: 0  Skipped: 0  Warnings: 0-----------------------------验证----------------------------------mysql> select * from users\G*************************** 1. row ***************************
   id: 1003
 name: 王五
email: [email protected]*************************** 2. row ***************************
   id: 1001
 name: 张三
email: [email protected]*************************** 3. row ***************************
   id: 1002
 name: 李四
email: [email protected]

分列，使用--fields-terninated-by参数来指定每列的分隔符，例如：

如果列值中出现了分隔符，例如 1004"#李#白"#"[email protected]"

PS F:\> mysqlimport -u root -p7758520 zz  --fields-terminated-by=‘#‘ --fields-enclosed-by=\"  f:\users.txt

如果遇到一条记录有多行，则可以使用--lines-terminated-by=name来指定行的结束符

PS F:\> mysqlimport -u root -p7758520 zz  --fields-terminated-by=‘#‘ --fields-enclosed-by=\"  --lines-terminated-by=‘xxx\n‘ f:\users.txt

2.使用Load Data语句导入数据

Load Data 语句的使用语法如下：

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE ‘file_name‘
    [REPLACE | IGNORE]
    INTO TABLE tbl_name    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY ‘string‘]
        [[OPTIONALLY] ENCLOSED BY ‘char‘]        [ESCAPED BY ‘char‘]
    ]    [LINES
        [STARTING BY ‘string‘]
        [TERMINATED BY ‘string‘]
    ]    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]

刚开始看到这个语法吓了一跳，这么长，其实没这么复杂，一般只需记住LOAD DATA INFILE file_name INTO TABLE tb_name这个即可，示例：

首先创建一个表sql_users，利用上面的users表复制一下

mysql> create table sql_users as select * from users;
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0mysql> truncate table sql_users;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from sql_users;
Empty set (0.00 sec)

文本sql_users.txt

1004#李白[email protected]
1005#杜牧[email protected]
1006#杜甫[email protected]
1007#苏轼[email protected]

利用LOAD DATA INFILEE语句导入数据

mysql> load data infile ‘f:\sql_users.txt‘ into table sql_users fields terminated by ‘#‘;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from sql_users;
+------+------+--------------------+
| id   | name | email              |
+------+------+--------------------+
| 1004 | 李白 | [email protected]
| 1005 | 杜牧 | [email protected]
| 1006 | 杜甫 | [email protected]
| 1007 | 苏轼 | [email protected]  |
+------+------+--------------------+
4 rows in set (0.00 sec)

如果在导入数据时，遇到字符串无法识别时，一般都是字符集有问题，使用charset选项即可解决

mysql> load data infile ‘f:\sql_users.txt‘ into table sql_users  fields terminated by ‘#‘;
ERROR 1366 (HY000): Incorrect string value: ‘\xC0\xEE\xB0\xD7‘ for column ‘name‘ at row 1--------------------------------字符集不一样-----------------------mysql> load data infile ‘f:\sql_users.txt‘ into table sql_users  character set gbk fields terminated by ‘#‘;
Query OK, 4 rows affected (0.03 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

LOAD DATA INFILE命令默认要导入数据存放在服务上，如果要导入客户端的数据，可以指定LOCAL，那么mysql将从客户端读取数据，这样的方式会比服务器上操作要慢一点，因为客户端的数据需要通过网络传输到服务器。

mysql> load data local infile ‘f:\sql_users.txt‘ into table sql_users  fields terminated by ‘#‘;

如果需要忽略与主键值重复的记录值或者替换重复值，可以使用IGNORE或REPLACE选项，但是LOAD DATA INFILE命令语法中有两处IGNORE关键字，前面一个是用来此功能的，后面一个用来指定需要忽略的前N条记录。

如果不想导入数据文件的前N行，使用IGNORE N LINES来处理

如果在数据文件中记录行头有某些字符，又不想被导入，可以使用LINES STARTING BY来解决，但是如果某行记录不包含这些字符的话，那么这行记录也会被忽略。

数据文件为Excel文件的处理，首先将Excel文件保存为CSV格式，这样字段间都是用逗号隔开的，再进行处理。

数据文件列值中有特殊符号，使用enclosed by来处理。例如，列值中有分隔符

数据导入时换行符的问题，在上面的示例中，有几个数据导入到表中后，查询时结果显示有点别扭，不知大家注意到了没。

在Windows系统中，文本格式的换行符有"\r+\n"组成，而在linux系统中，换行符是"\n"。因此出出现上述问题，解决方法就是指定换行符LINES TERMINATED BY。

mysql> LOAD DATA INFILE ‘F:\stu.csv‘ INTO TABLE stu CHARACTER SET GBK FIELDS TERMINATED BY ‘,‘ ENCLOSED BY ‘"‘ LINES TERMINATED BY ‘\r\n‘ IGNORE 1 LINES;

表的列数多余数据文件中的列数，解决方法就是指定要导入到表的字段，如下所示