mysql导出数据拼接_mysql + 存储过程+Select into file 拼接执行导出语句 数据快速导出到文件 （分表）+ （单条语句）LOAD DATA 导入...

数据库数据快速导出到文件

1.遍历数据库同时 拼接出一条语句  select into file

SELECT CONCAT('select * from ',table_name,' INTO OUTFILE ''D:/backup/',table_name,'.txt'' FIELDS TERMINATED BY ''\\,'' OPTIONALLY ENCLOSED BY '''' LINES STARTING BY '''' TERMINATED BY ','''\\r\\n''','; ') as output FROM information_schema.`TABLES` WHERE table_schema='数据库名' AND TABLE_ROWS >0;

2.执行这些语句搜索结果

暂时没有找到直接执行的方法 了解一下 execute sp_sqlexec @lssql  应该可以直接在结果中继续执行刚才拼接的语句

3.没有找到的话那么就foreach 一下 循序执行以后就会导出到文件，缺点是不能覆盖重名文件，所以在之前要先把原来的文件删除掉。

数据导入到数据库

1.清空原来的表

TRUNCATE 表名;

LOAD DATA LOCAL INFILE 'D:/backup/表名.txt' INTO TABLE `表名` FIELDS TERMINATED BY ',' ENCLOSED BY ''

从文件夹中遍历一下拼接上面乃一条语句，缺点会覆盖表

据说  select into file  比 拼接一条大 insert into 语句要快   缺点应该是没办法像拼接一样放到一个文件里。

小数据量没问题，最近遇见的是  读一个100M的text文件运行的时候  string 提示 内存不足，如果数据量太大 拼接一条insert into语句还是有弊端的，那么如果直接利用mysql执行还好，在程序中读取再插入可能要考虑数据量的问题，可以尝试没到一定数据量分割一个文件。不过这有违背了 想用一个文件解决备份的方法，所以程序始终还是程序，不可能那么完美。

-------------------------------------------------------------------------------------混个县------------------------------------------------------------------

续更 ：

经过努力还是解决了一句话导出数据库的方法

先这么写等有空了 我再加上注释；不管怎么说还是有用的，下次准备批量导入；

知识点：

1.mysql 游标

2.mysql 变量

3.mysql 存储过程

4.mysql loop循环

5.mysql 动态sql

6.mysql 预编译prepared

7.mysql  select into outfile

8. mysql fetch into

drop procedure if exists ExportDB;

CREATE PROCEDURE ExportDB()

BEGIN

declare backsql VARCHAR(200);

declare done int default false;

declare total int default 0;

declare mycursor cursor for

SELECT CONCAT('select * from ',table_name,

' INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/',table_name,'.txt'' ',

' FIELDS TERMINATED BY ''\\,'' OPTIONALLY ENCLOSED BY '''' LINES STARTING BY '''' TERMINATED BY ','''\\r\\n''','; ') as backsql

FROM information_schema.`TABLES` WHERE table_schema='数据库名' AND TABLE_ROWS >0;

#指定游标循环结束时的返回值

declare continue HANDLER for not found set done = true;

#设置初始值

set total = 0;

open mycursor;

posLoop:LOOP

fetch mycursor INTO backsql;

if done then

leave posLoop;#跳出游标循环

end if;

SET @sqlstr =backsql;

PREPARE outsql FROM @sqlstr;

EXECUTE outsql ;

deallocate prepare outsql;

END LOOP posLoop;

close mycursor;

#deallocate mycursor;

END;

call ExportDB();

引用连接：游标的使用方式

----------------------------------------------------------------------分割线线------------------------------------------------------------------------------

同志们如果你们用的是 mysql workbench  这些语句可能是会报错的；

但是我已经稍作修改 做了一些改动，但是还需要注意 可能会有安全目录这么一说；

#删除存储方法

drop procedure if exists ExportDB;

DELIMITER $$

CREATE PROCEDURE `ExportDB`()

#存储过程开始标志

BEGIN

#设置变量

declare backsql VARCHAR(300); #用来保存拼接成的SQL语句

declare done int default false;#循环是否结束

#设置游标变量 从SQL的结果中

declare DBBackupsCursor cursor for

SELECT CONCAT('select * from ',table_name,

' INTO OUTFILE ''C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/',table_name,'.txt'' ',

' FIELDS TERMINATED BY ''\\,'' OPTIONALLY ENCLOSED BY '''' LINES STARTING BY '''' TERMINATED BY ','''\\r\\n''','; ') as backsql

FROM information_schema.`TABLES` WHERE table_schema='数据库名' AND TABLE_ROWS >0;

#指定游标循环结束时的返回值

declare continue HANDLER for not found set done = true;

#开始使用游标

open DBBackupsCursor;

#开始循环

posLoop:LOOP

#将循环中的当前行存到backsql变量中

fetch DBBackupsCursor INTO backsql;

if done then #退出循环的判断

leave posLoop;#跳出游标循环

end if;

SET @sqlstr =backsql;#将语句设置到临时变量

PREPARE outsql FROM @sqlstr; #预编译导出语句(因为不能直接运行语句所以设置变量中转)

EXECUTE outsql ; #执行拼接的Sql语句

deallocate prepare outsql; #销毁outsql预编译变量

END LOOP posLoop;#循环节为标志

#关闭游标

close DBBackupsCursor;

#销毁游标

#deallocate DBBackupsCursor;

#程序结束

END;

$$ #对应结束标志

call ExportDB();#调用存储过程方法

如果提示The MySQL server is running with the --secure-file-priv option so it cannot execute this statement 错误

是因为导出的目录不是安全目录

SHOW VARIABLES LIKE "secure_file_priv"

会得到安全目录

一个是放到安全目录里

要不就是修改安全目录

-------------------------------------------------------------------分割线---------------------------------------------------------------------------

第二部制作导入程序

先写个草稿 但是有异常

(发现了一个搞笑的事情，我发现这个异常是 LOAD DATA is not allowed in stored  存储过程中不允许使用LOAD DATA)

所以下面的也就没啥必要看了 但是我这里还是留在这里，怎么说也是算打破了砂锅，不求甚解可不好。

一旦前面的表出错 后面的就都不执行了，所以先放着

需要研究的是

1.mysql存储过程中的异常处理

2.执行循环实现continue的效果

#删除存储方法

drop procedure if exists ImportDB;

DELIMITER $$

CREATE PROCEDURE `ImportDB`()

#存储过程开始标志

BEGIN

#设置变量

declare backsql VARCHAR(300); #用来保存拼接成的SQL语句

declare done int default false;#循环是否结束

declare errorcode int default 0;

#设置游标变量 从SQL的结果中

declare DBBackupsCursor cursor for

SELECT

CONCAT('TRUNCATE ',table_name,';',

'LOAD DATA LOCAL INFILE ',

'''C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/',table_name,'.txt''',

'INTO TABLE `',table_name,'`',

'FIELDS TERMINATED BY '','' ENCLOSED BY '''' ;') as backsql

FROM information_schema.`TABLES` WHERE table_schema='phi' ;

#指定游标循环结束时的返回值

declare continue HANDLER for not found set done = true;

#开始使用游标

open DBBackupsCursor;

#开始循环

posLoop:LOOP

begin

DECLARE CONTINUE HANDLER FOR SQLWARNING,NOT FOUND,SQLEXCEPTION

#将循环中的当前行存到backsql变量中

fetch DBBackupsCursor INTO backsql;

if done then #退出循环的判断

leave posLoop;#跳出游标循环

end if;

SET @sqlstr =backsql;#将语句设置到临时变量

PREPARE outsql FROM @sqlstr; #预编译导出语句(因为不能直接运行语句所以设置变量中转)

EXECUTE outsql ; #执行拼接的Sql语句

deallocate prepare outsql; #销毁outsql预编译变量

end;

END LOOP posLoop;#循环节为标志

#关闭游标

close DBBackupsCursor;

#销毁游标

#deallocate DBBackupsCursor;

#程序结束

END;

$$ #对应结束标志

call ImportDB();#调用存储过程方法