mysql数据的导入和导出

mysql> system more /tmp/emp.txt
"1","z1","aa"
"2","z1","aa"
"3","z1","aa"

例子2，发现例1中第一列是数值型，如果不希望字段两边用引号，则语句改为如下

mysql> select * from emp into outfile '/tmp/emp.txt' fields terminated by ","  optionally enclosed by '"' ;

下面测试一下转义字符，大概包括三类，转义字符本身，字段分隔符（导出的文本中用什么符号分隔），记录分隔符（每条记录之间用什么分隔，默认是回车）

例子3，更改上面例子中id=1的name为\"##!aa

update employee set name ='\\"##!aa' where id=1; #更新操作中转义字符本身也需要用转义字符来转义，所以这里有2个\\
# 然后做导出操作
select * from employee into outfile '/tmp/employee' fields terminated by "," optionally enclosed by ‘”’；导出结果如下
mysql> system more /tmp/emp.txt
1,"\\\"##!aa","aa"
2,"z1","aa"
3,"z1","aa"

说明：name 中含有转义字符本身“\”，域引用符“”“，因此，在输出的数据中我们发现这两种字符前面都加上了转义字符“\”

例子4，将id=1的name更新为含有字段分隔符”，“的字符串

update employee set name='\\"#,#,!aa' where id=1;然后导出
mysql> system rm /tmp/emp.txt #需要删掉重名文件mysql> select * from emp into outfile '/tmp/emp.txt' fields terminated by "," optionally enclosed by '"' ;输出结果如下
mysql> system more /tmp/emp.txt
1,"\\\"#,#,!aa","aa"
2,"z1","aa"
3,"z1","aa"

说明：发现数据中的字符","没有被转义，原因是它和后面真正的字段分隔符之间没有冲突，因为name字段包含在双引号之间。

例子5：继续做测试，将输出文件的字段引用符去掉，这个时候，我们的预期是数据中的“,”将成为转义字符而需要加上“\”

mysql> system rm /tmp/emp.txt
mysql> select * from emp into outfile '/tmp/emp.txt' fields terminated by "," ;输出结果如下mysql> system more /tmp/emp.txt
1,\\"#\,#\,!aa,aa
2,z1,aa
3,z1,aa

说明：果然，现在的“,”前面加上了转义字符“\”。而刚才的引用符“””却没有被转义，因为它已经没有什么歧义，不需要被转义

注意：SELECT…INTO OUTFILE...产生的输出文件如果在目标目录下有重名文件，将不会创建成功，源文件不能被自动覆盖

方法 2：用 mysqldump 导出数据为文本。

mysqldump –u username –T target_dir dbname tablename [option]

其中 option 参数可以是以下选项：

1) --fields-terminated-by=name（字段分隔符）；
2) --fields-enclosed-by=name  字段引用符，比如每个字段用双引号括起来；
3) --fields-optionally-enclosed-by=name（字段引用符，只用在 char、varchar 和 text 等字符型字段上
4) --fields-escaped-by=name（转义字符）；
5) --lines-terminated-by=name（记录结束符）。

例子，采用 mysqldump 生成指定分隔符分隔的文本：

mysqldump -uroot -T /tmp test emp --fields-terminated-by ',' --fields-optionally-enclosed-by '"'

输出结果如下

more /tmp/emp.txt

1,"\\\"#,#,!aa","aa"
2,"z1","aa"
3,"z1","aa"

注意：

1）ubuntu测试中会出错，解决方法如下。将输出目录改为/var/lib/mysql-files/. 例子中test是库名，emp是表名，如果不写则备份全部表

mysql> mysql> select * from person into outfile '/tmp/x.txt' fields terminated by ",";
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
mysql> show global variables like "%secure%";
+--------------------------+-----------------------+
| Variable_name | Value |
+--------------------------+-----------------------+
| require_secure_transport | OFF |
| secure_auth | ON |
| secure_file_priv | /var/lib/mysql-files/ |
+--------------------------+-----------------------+

2）如果非要用/tmp，则需要做如下操作 (测试了下又不行了...)

三. 导入纯文本数据

这里只讨论用 SELECT… INTO OUTFILE 或者 mysqldump 导出的纯数据文本的导入方法。和导出类似，导入也有两种不同的方法，分别是 LOAD DATA INFILE…和mysqlimport，它们的本质是一样的，区别只是在于一个在 MySQL 内部执行，另一个在 MySQL 外部执行。

需要注意的是在导入纯文本数据之前，需要事先创建好表结构

1. 一个简单的完整例子

首先准备表结构和文本数据，新建test库：create database test，然后在test下新建表person

create table person(
id int not null auto_increment,
name varchar(40) not null,
city varchar(20),
salary int,
primary key(id)
)
engine=innodb default charset=utf8;

每一项之间用Tab键进行分隔，如果该字段为NULL，则用/N表示。

进入mysql并且切换到对应的库后，执行下面命令导入数据：

注意

1. data.txt存放的位置，其他地方会报错

2. 在mysql配置文件中修改字符编码

mysql 5.6
sudo vim /etc/mysql/my.cnf，重启mysql来完成字符集的设置。添加以下两行 （如果是上传安装包，vim /etc/my.cnf)
[mysqld]
character_set_server=utf8
binlog_format=rowmysql 5.7 可能需要在etc/mysql/mysql.conf.d/mysqld.cnf中额外增加如下代码，有时候也不需要
[client]
default-character-set=utf8

2. 使用“LOAD DATA INFILE…”命令导入数据详解

mysql > LOAD DATA [LOCAL] INFILE ‘filename’ INTO TABLE tablename [option]

option 可以是以下选项：
1. FIELDS TERMINATED BY 'string'（字段分隔符，默认为制表符'\t'）；
2. FIELDS [OPTIONALLY] ENCLOSED BY 'char'   字段引用符，如果加 OPTIONALLY 选项则只会做用在char, varchar和text等字符型字段上，其他类型字段默认不使用引用符
3. FIELDS ESCAPED BY 'char'（转义字符，默认为'\'）；
4. LINES STARTING BY 'string'（每行前都加此字符串，默认''）；
5. LINES TERMINATED BY 'string'（行结束符，默认为'\n'）；
6. IGNORE number LINES（忽略输入文件中的前 n 行数据）；
7. (col_name_or_user_var,...) （按照列出的字段顺序和字段数量加载数据）；
8. SET col_name = expr,... 将列做一定的数值转换后再加载。

注意：
1. 其中 char 表示此符号只能是单个字符，string 表示可以是字符串。
2. FILELD 和 LINES 和前面 SELECT …INTO OUTFILE…的含义完全相同，不同的是多了几个不同的选项

例子1：将文件“/tmp/emp.txt”中的数据加载到表 emp 中

mysql> load data infile '/tmp/emp.txt' into table emp fields terminated by ',' enclosed by'"' ;

例子2：如果不希望加载文件中的前2行，加上ignore字段

mysql> load data infile '/tmp/emp.txt' into table emp fields terminated by ','  enclosed by '"'  ignore 2 lines;

例子3：如果发现文件中的列顺序和表中的列顺序不符，或者只想加载部分列，可以在命令行中加上列的顺序

mysql> load data infile '/tmp/emp.txt' into table emp fields terminated by ',' enclosed by '"' ignore 2 lines (id,content,name);

如果只想加载第一列，字段的列表里面可以只加第一列的名称：

mysql> load data infile '/tmp/emp.txt' into table emp fields terminated by ',' enclosed by '"'  ignore 2 lines (id);

例子4：如果希望将 id 列的内容+10 后再加载到表中，可以如下操作：

mysql> load data infile '/tmp/emp.txt' into table emp fields terminated by ','  enclosed by '"'  set id=id+10;

3. 用 mysqlimport 来实现导入数据详解，具体命令如下

shell>mysqlimport –u root –p*** [--LOCAL] dbname order_tab.txt [option]
其中 option 参数可以是以下选项：
1.  --fields-terminated-by=name（字段分隔符）；
2.  --fields-enclosed-by=name（字段引用符）；
3.  --fields-optionally-enclosed-by=name（字段引用符，只用在 char、varchar 和 text 等字符型字段上
4.  --fields-escaped-by=name（转义字符）；
5.  --lines-terminated-by=name（记录结束符）；
6.  -- ignore-lines=number（或略前几行）。

这与 mysqldump 的选项几乎完全相同，这里不再详细介绍，简单来看一个例子：

mysqlimport -uroot test /tmp/emp.txt --fields-terminated-by=',' --fields-enclosed-by='"'

转载出处：https://www.cnblogs.com/regit/p/8041762.html