mysql 表数据导入导出

• mysql表数据到出

select * into oufile '/path/file_name' fields terminated by '|' enclose by '"' from tbl1;

fields terminated by ‘|’：字段分割符
fields enclose by ‘”’：字段值由”符包起来
fields OPTIONALLY enclose by :字段为数字则不包起来
lines terminated by ‘\r\n’：行换符

下面写发效果和上面一样

select *  from tbl1 into oufile '/path/file_name' fields terminated by '|' enclose by '"' 

• 将以指定分割符的表文件数据导入到数据库中有如下两种方法

load data infile '/path/file_name' into table tbl_name fields terminated by '|' enclosed by '"';

• 如果表结构需要变更（新增字段，一般都有默认值）往往我们需要对原表进行数据备份，表结构变更后出现异常，则需要将原表数据导入到新表（表结构变更后的表），则在fields后面加上指定字段就可以了；注意表名和文件名一样

load data infile '/var/lib/mysql-files/tbl_trans_id.txt' into table tbl_ fields terminated by '|' enclosed by '"' (trans_id, trans_name, trans_code, trans_level, forbid, reserve);

• 通过mysqlimport也可以实现将备份文件中数据导入到新表中

mysqlimport -h127.0.0.1 -uroot -p123456 mysql '/var/lib/mysql-files/tbl_trans_id.txt' --columns='trans_id, trans_name, trans_code, trans_level, forbid, reserve' --fields-terminated-by '|' --fields-enclosed-by '"'

• 备份表数据到文件也可以使用mysqldump命令，需要加上set-gtid-purged=off，否则sql文件到入到表时会报错；

mysqldump -h${DB_HOST} -u${DB_USER} -p${DB_PWD} -P${DB_PORT} --set-gtid-purged=off ${DB_NAME} ${TBL_NAME} > ${BACK_DIR}/${TBL_NAME}.sql 

• 将sql表数据文件导入到表

 mysql -h${DB_HOST} -u${DB_USER} -p${DB_PWD} -P${DB_PORT} ${DB_NAME} -e "source ${BACK_DIR}/${TBL_NAME}.sql"