mysql 导入导出

1.文本文件里的特殊字符处理
LOAD DATA和SELECT…INTO OUTFILE、mysqlimport和mysqldump有一组专门的用来处理文本文件中特殊字符的选项，具
体如下所示。
·FIELDS TERMINATED BY'fieldtermstring'：各列（字段）之间用什么字符分隔，默认是tab，一般设置为逗号“,”。
·[OPTIONALLY]ENCLOSED BY'char'：值被什么字符引起来，一般设置为引号'"'，如果指定了OPTIONALLY，则
ENCLOSED BY'char'只对字符串数据类型（比如CHAR、BINARY、TEXT或ENUM）生效。
·ESCAPED BY'escchar'：定义转义字符，默认是“\”。
·LINES TERMINATED BY'linetermstring'：定义行结束符，用于分隔行。

在Windows下需要使用“\r\n”提供一次换行，而在Linux下只需要“\n”就可以了。

2.文本文件的数据格式
所有命令都要求有关的文本文件必须严格遵守一种数据格式，具体如下所示。
·数值：可以用科学计数法。
·字符串：字符串里的特殊字符必须加上反斜线字符作为识别标志，以区别于各种分隔符。日期按照2005-12-21格式的字符
串来对待，时间值按照23:59:59格式的字符串来对待，时间戳按照20051231235959格式的整数来对待。
NULL值：假设“\”作为转义前导字符，“'”作为字符串的前后缀标记，那么在导出操作中，NULL值将被表示为\N；在没有指
定转义前导字符的导出操作中，NULL值将被表示为由4个字符构成的字符串。在指定了转移前导字符的操作中，MySQL将把
NULL、\N、'\N'都解释为NULL值，但'NULL'将被解释为一个字符串'NULL'。

4.6.2　使用mysqldump导出，使用mysql导入
虽然mysqldump速度较慢，但这种方式有最好的兼容性，这也是目前使用最为广泛的备份数据的方式。使用mysqldump导出
的一般是SQL文件，也称为转储文件或dump文件，我们可以使用客户端工具mysql执行这个文件，导入数据，示例如下。
1）导出指定的表。
mysqldump test --tables test1 test4 > test1_test4.sql
2）分别导出sql文件和数据文件（数据值以tab分隔）。
mysqldump --tab=/home/garychen/tmp test
3）分离导出sql文件和数据文件（定制数据格式，数据值以逗号分隔）
mysqldump --tab=/home/garychen/tmp --fields-terminated-by=',' --fields-enclosed-by=''' test
4）导出某个库。
mysqldump --complete-insert --force --add-drop-database --insert-ignore --hex-blob --databases test > test_db.sql
代码说明如下。
--complete-insert：导出的dump文件里，每条INSERT语句都包括了列名。
--force：即使出现错误（如VIEW引用的表已经不存在了），也要继续执行导出操作（mysqldump会打印出错误，注释完
VIEW定义后继续后续的数据导出）。
--insert-ignore：生成的INSERT语句是INSERT IGNORE的形式，如果导入此文件，即使出错了也仍然可以继续导入数据（当
作警告）。
例如，使用mysql执行SQL文件，插入与主键冲突的值，如果是INSERT，那么mysql会异常退出，并提示如下错误。
ERROR 1062 (23000) at line 28: Duplicate entry '1' for key 1
如果是INSERT IGNORE，那么mysql会忽略错误，继续插入后面的值。
例如下面这些语句。
INSERT IGNORE INTO 't1' VALUES ('1'),('10'),('11'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9');
INSERT IGNORE INTO 't1' VALUES ('111'),('20'),('21'),('22'),('23'),('4'),('5'),('6'),('7'),('88'),('99');
两条INSERT语句，即使有重复键值，也仍然会插入后面的值，因此88、99仍然可以正常插入。
--databases：类似--tables，后面可以跟多个值。
--compatible=name：导出的文件和其他数据库更兼容（但不确保），name的值可以是ANSI、MYSQL323、MYSQL40、
POSTGRESQL、ORACLE、MSSQL、DB2、MAXDB、NO_KEY_OPTIONS、NO_TABLE_OPTIONS或NO_FIELD_OPTIONS。
5）导出所有的数据库。
mysqldump --all-databases --add-drop-database > db.sql
6）导出xml格式的数据。
mysqldump -u root -p --xml mylibrary > /tmp/mylibrary.xml
如果有二进制数据，则要使用选项--hex-blob。
InnoDB若想获得一致性的数据库副本，则要启用选项--single-transaction。
mysqldump不能利用通配符导出多个表，表比较多的时候，可以先SELECT出要导出的表，如下语句即可查询到所有的表。
select group_concat(table_name SEPARATOR ' ') from information_schema.tables where table_schema ='db_name' and table_name
like 'prefix%';
或者，可以采用如下方式将表名导出到一个文件。
mysql -N information_schema -e "select table_name from tables where table_name like 'prefix_%' " > tbs.txt
然后运行如下命令导出数据。
mysqldump db 'cat tbs.txt' > dump.sql
也可以忽略部分表，加上参数--ignore-table=db_name.tbl_name1、--ignore-table=db_name.tbl_name2。
mysqldump可以把警告和错误追加记录在文件中，加上参数--log-error=file_name即可。
如果使用mysqldump导出数据，可以考虑的优化的方式有如下5种。
·选择I/O活动低的时候。
·I/O分离（数据盘和备份盘I/O分离）。
·输出到管道压缩（gzip）。
·--quick跳过内存缓冲（--opt默认启用）。
·从数据保留策略上想办法，把不需要修改的大量数据放到历史表中，而不是每次都备份。
mysqldump导出的SQL转储文件，可以用如下的形式将数据导入到数据库中。
mysql db_name < db_name.sql
转储文件（dump文件）里面一般指定了set names utf8，所以我们在导入的时候不再需要指定特殊的字符集。例外的情况
是，有一些特殊的场合，SQL文件是以其他的字符集导出的，这个时候导入要注意保持文件的字符集、客户端字符集和连接的
字符集的一致性，例如：
mysql --default-character-set=charset_name database_name < import_table.sql
--default-character-set的意思是，客户端和连接都默认使用charset_name字符集。例如：
mysql --default-character-set=gbk < import_table.sql
这个文件的字符集是gbk。
如果mysql客户端输出的数据是乱码，那么请检查下客户端、连接的字符集配置。例如，我们使用SSH工具securecrt登录主
机，然后使用mysql命令行工具连接MySQL服务器，mysql连接的默认配置可能是latin1，那么此时显示utf8的数据将会是乱码。这
种情况下，可以在客户端运行set names utf8，并确认securecrt的字符编码是UTF-8，这样就可以正常显示utf8字符集的数据了。
4.6.3　使用SELECT INTO OUTFILE命令导出数据
如果想要进行SQL级别的表备份，可以使用SELECT INTO OUTFILE命令语句。对于SELECT INTO OUTFILE，输出的文件
不能先于输出存在。
示例语句如下所示。
SELECT * INTO OUTFILE '/tmp/testfile.txt' FROM exporttable;
SELECT * INTO OUTFILE '/tmp/testfile.txt' FIELDS TERMINATED BY ':' OPTIONALLY ENCLOSED BY '+' ESCAPED BY '!' FROM
exporttable;
SELECT a,b,a+b INTO OUTFILE '/tmp/result.text' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'
FROM test_table;
一般来说，只要导出导入操作中使用的选项完全一致，用SELECT…INTO OUTFILE命令导出的文本文件就可以用LOAD
DATA命令导入到数据表里去，不会发生任何变化。
4.6.4　使用LOAD DATA导入数据
SELECT...INTO OUTFILE可以筛选记录，导出表数据到一个文件中，而LOAD DATA INFILE则是相反的操作，是读取这个
文件导入表中。
如果MySQL服务器和LOAD DATA命令不在同一台计算机上执行，当想导入本地文件系统的文件时，则需要使用语法变体
LOAD DATA...LOCAL INFILE...，也就是说，如果指定LOCAL关键词，则表明从客户主机读文件。如果没指定LOCAL，那么文
件必须位于服务器上。
可能是因为字符集设置而导致乱码的问题。LOAD DATA INFILE在某些MySQL的版本上不支持指定导入时的字符集。这
时，MySQL将假设导入文件的字符集是character_set_database，这个变量会根据当前数据库指定的字符集而变化；如果没有指定
当前数据库，那么它的值将由character_set_server决定。因此如果LOAD DATA INFILE不支持指定字符集，那么在导入前需要确
认当前数据库的字符集，如果与当前数据库的字符集不符，则使用SET character_set_database命令进行更改。SET names命令也是
可行的，或者直接在LOAD DATA INFILE命令里指定字符集，例如如下语句。
mysql> load data infile '/tmp/t0.txt' into table t0 character set gbk fields terminated by ',' enclosed by '"' lines
terminated by '\n' (`name`,`age`,`description`) set update_time=current_timestamp;
其他示例如下。
示例1：
LOAD DATA INFILE '/path/to/file'
INTO TABLE table_name
FIELDS TERMINATED BY '\t'
ENCLOSED BY '\''
LINES TERMINATED BY '\n'
示例2：
LOAD DATA INFILE '/path/to/file' REPLACE
INTO TABLE table_name
FIELDS TERMINATED BY '\t'
ENCLOSED BY '\''
LINES TERMINATED BY '\n'
示例3： 导入csv格式的文本文件。csv格式的文件，即逗号分隔的数据文件。首先，生成如下csv文件。
mysql> select field_list from table_name into outfile '/home/garychen/tmp/table_name_2.csv' fields terminated by ','
optionally enclosed by '"' lines terminated by '\n';
然后，截断表，清空数据，命令如下。
mysql> truncate table table_name;
最后，进行验证，可以看到，原来导出的文件，现在可以正常导入到数据表中，语句如下。
mysql> load data local infile '/home/garychen/tmp/table_name_2.csv' into table table_name fields terminated by ',' lines
terminated by '\n'(field1,field2,field3);
LOAD DATA的优化 　相较于普通的mysql命令，LOAD DATA执行SQL文件导入的方式要快得多，一般可以达到每秒几万
条记录的插入速度。有时对于大表，我们仍然期望获得更高的导入速度，以下将针对InnoDB和MyISAM表分别叙述如何进行优
化。
对于InnoDB的优化，建议的方式如下。
·将innodb_buffer_pool_size设置得更大些。
·将innodb_log_file_size设置得更大些，如256MB。
·设置忽略二级索引的唯一性约束，SET UNIQUE_CHECKS=0。
·设置忽略外键约束，SET FOREIGN_KEY_CHECKS=0。
·设置不记录二进制日志，SET sql_log_bin=0。
·按主键顺序导入数据。由于InnoDB使用了聚集索引，如果是顺序自增ID的导入，那么导入将会更快，我们可以把要导入
的文件按照主键顺序先排好序再导入。
·对于InnoDB引擎的表，可以在导入前，先设置autocommit=0，例如如下语句。
truncate table_name;
set autocommit = 0;
load data infile /path/to/file into table table_name...
commit;
·可以将大的数据文件切割为更小的多个文件，例如使用操作系统命令split切割文件，然后再并行导入数据。
对于MyISAM的优化，建议的方式如下。
·将bulk_insert_tree_size、myisam_sort_buffer_size、key_buffer_size设置得更大些。
·先禁用key（ALTER TABLE…DISABLE KEYS），然后再导入数据，然后启用key（ALTER TABLE…ENABLE KEYS）。重
新启用key后，可以批量重新创建索引，批量创建索引的效率比在逐笔插入记录时创建索引要高效得多。注意ALTER TABLE…
DISABLE KEYS禁用的只是非唯一索引，唯一索引或主键是不能禁用的，除非你先手动移除它。
·使用LOAD DATA INFILE，tab分隔的文件更容易解析，比其他方式更快。
由于唯一索引（约束）对于我们导入数据的影响比较大，尤其对于大表导入，我们需要留意这一点。不要在大表上创建太
多的唯一索引，主键、唯一索引不要包含太多列，否则导入数据将会很慢。
关于优化导入数据的方式，见仁见智，其实一次INSERT插入多条记录，控制每个表的大小（<15GB，确保B-tree索引在内
存中），并发导入，批量事务等方式都有好处，但更多的时候也要考虑维护的简单方便。
如果有很多表，那么使用mysqldump会更简单。如果是导入个别大表，而且对于时间有很高的要求，那么LOAD DATA未尝
不可。
mysqldump默认的导出文件，其实已经包含了一些优化了，会有禁用key、启用key的操作，而且是一条INSERT语句包括多
行记录的。
4.6.5　用mysqlimport工具导入
mysqlimport命令的语法格式如下。
mysqlimport databasename tablename.txt
示例如下。
mysqlimport --local test imptest.txt
4.6.6　用mysql程序的批处理模式导出
有时可以考虑使用mysql工具导出数据，特别是远程操作的时候，下面来看几个示例。
示例1： 导出authors表。
mysql -u root --password=123456 --batch --default-character-set=utf8 -e "SELECT * FROM authors;" mylibrary > output.txt
示例2： 查询结果的纵向显示。
mysql -u root --password=123456 --vertical '--execute=SELECT * FROM titles;' mylibrary > test.txt
示例3： 生成html表格形式的输出。
mysql -u root -p=xxx --html '--execute=SELECT * FROM titles;' \
--default-character-set=latin1 mylibrary > test.html
示例4： 用mysql程序生成xml格式的输出。
mysql -u root -p -xml -default-character-set=utf8
'-execute=SELECT * FROM titles;' mylibrary > C:\test.xml
4.6.7　用split切割文件，加速导入数据
split命令的作用是切割文件，语法格式如下所示。
split [OPTION] [INPUT [PREFIX]]
如果不加入任何参数，默认情况下是以1000行的大小来分割的。
下面来看个案例，使用split切割导出的数据文件，这些数据文件需要通过PHP脚本解析二次处理后，再插入MySQL数据
库，示例如下。
split -l 5052000 subs.txt test_split_sub_
其中，-l参数指定按多少条记录切割文件。这里将按照每5052000条记录进行切割，生成的文件名以test_split_sub_为前缀，
生成的文件名类似如下。
test_split_sub_aa test_split_sub_ab test_split_sub_ac