mysql 导入_mysql数据的导入导出

mysql> system more /tmp/emp.txt1,"z1","aa"

2,"z1","aa"

3,"z1","aa"

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

例子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.txt1,"\\\"##!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.txt1,"\\\"#,#,!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.txt1,\\"#\,#\,!aa,aa

2,z1,aa3,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(记录结束符)。

常用参数

--compact ：使得输出结果简洁，不包括默认选项中的各种注释，例如例中对 test 数据库中的表 emp 进行简洁导出 mysqldump -uroot -p  --compact test emp >a

-F --flush-logs(备份前刷新日志)：加上此选项后，备份前将关闭旧日志，生成新日志。使得进行恢复的时候直接从新日志开始进行重做，大大方便了恢复过程

-l --lock-tables(给所有表加读锁)：可以在备份期间使用，使得数据无法被更新，从而使备份的数据保持一致性，可以配合-F选项一起使用。

注意：

MyISAM 存储引擎在备份的时候需要加上-l 参数，表示将所有表加上读锁，在备份期间，所有表将只能读而不能进行数据更新。

但是对于事务存储引擎(InnoDB 和 BDB)来说，可以采用更好的选项--single-transaction，此选项将使得 InnoDB 存储引擎得到一个快照(Snapshot)，使得备份的数据能够保证一致性

例子，采用 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 ",";

ERROR1290 (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，则需要做如下操作 (测试了下又不行了...)

在/etc/apparmor.d/usr.sbin.mysqld文件中加入一行代码如下/data/export/** rw, 保存退出后

/etc/init.d/apparmor reload #重新载入配置

再次导出即可。

三. 导入纯文本数据

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

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

1. 一个简单的完整例子

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

create tableperson(

idint not nullauto_increment,

namevarchar(40) not null,

cityvarchar(20),

salaryint,primary key(id)

)

engine=innodb default charset=utf8;

张三　　北京　　3000李四杭州　　4000王五/N　　4500小明天津　　/N

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

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

load data local infile "/var/lib/mysql/test/data.txt" into table person(name,city,salary);

注意

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

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

mysql 5.6sudo vim/etc/mysql/my.cnf，重启mysql来完成字符集的设置。添加以下两行 (如果是上传安装包，vim /etc/my.cnf)[mysqld]character_set_server=utf8

binlog_format=row

mysql5.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='"'