jave导出mysql_mysql数据导出到文件及java解析方法

laojiang:~ # mysql -uroot -proot

mysql> grant file on laojiang.* to 'laojiang'@'%';

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES --file权限是全局级别的，不是数据库级别的，

mysql> grant file on *.* to 'laojiang'@'%';

Query OK, 0 rows affected (0.00 sec)

mysql>

laojiang:~ # mysql -ulaojiang -ppassword -h10.10.10.212 -P3307  -Dlaojiang

mysql> select * from user_info

-> into outfile '/var/lib/mysql/user_info_01.txt'

-> character set utf8

-> fields terminated by x'1E' optionally enclosed by '"'

-> lines terminated by '\n'

-> ;

--注意如果不授予用户file权限，或者授予了不重新登录，则执行导出文件是报错(另外注意授予权限的用户和导出文件的用户是否同一个)：

ERROR 1045 (28000): Access denied for user 'laojiang'@'%' (using password: YES)

mysql> exit

Bye

laojiang:~ # mysql -ulaojiang -ppassword -h10.10.10.212 -P3307  -Dlaojiang  --重新登录后执行导出文件成功

mysql> select * from user_info

-> into outfile '/var/lib/mysql/user_info_01.txt'

-> character set utf8

-> fields terminated by x'1E' optionally enclosed by '"'

-> lines terminated by '\n'

-> ;

Query OK, 175578 rows affected (2.92 sec)

mysql>

使用java的split用1E分割字符串：

public static void main(String[] args)

{

File file = new File("d:/user_info_01.txt");

try

{

BufferedReader br = new BufferedReader(new FileReader(file));

String s = null;

while ((s = br.readLine()) != null)

{

String ss[] = s.split((char)30+"");

for(int i=0;i

if(ss[i].equals("\\N")){

ss[i]="";

}

System.out.print(ss[i]);

}

System.out.println();

}

br.close();

}

catch (Exception e)

{

e.printStackTrace();

}

}

另外测试一下目录权限的问题：

laojiang:~ # whoami

root

laojiang:~ # mkdir /mysql

laojiang:~ # ll /

drwxr-xr-x   2 root root  4096 Sep 27 08:09 mysql

laojiang:~ #

laojiang:~ # mysql -ulaojiang -ppassword -h10.10.10.212 -P3307  -Dlaojiang

mysql> select * from user_info

-> into outfile '/mysql/user_info_01.txt'

-> character set utf8

-> fields terminated by x'1E' optionally enclosed by '"'

-> lines terminated by '\n'

-> ;

ERROR 1 (HY000): Can't create/write to file '/mysql/user_info_01.txt' (Errcode: 13 - Permission denied)

mysql>

不能写入文件，检查/var/lib/mysql和/mysql两目录权限等的差异，修改mysql的属主之后，文件可以写入了。

laojiang:/var/lib # ll mysql

drwx--x--x 2 mysql mysql      4096 Sep 26 07:18 mysql

laojiang:/mysql # chown mysql.mysql /mysql

laojiang:/mysql # ll /

drwxr-xr-x   2 mysql mysql  4096 Sep 27 08:09 mysql

laojiang:~ # mysql -ulaojiang -ppassword -h10.10.10.212 -P3307  -Dlaojiang

mysql> select * from user_info

-> into outfile '/mysql/user_info_01.txt'

-> character set utf8

-> fields terminated by x'1E' optionally enclosed by '"'

-> lines terminated by '\n'

-> ;

Query OK, 175578 rows affected (2.92 sec)

mysql>

存储过程导出数据

drop procedure if exists sp_user_info_exp;

delimiter $

create  procedure sp_user_info_exp(

out out_retcode integer,

out out_message varchar(200)

)

begin

declare s_file varchar(200);

declare s_sqlstr varchar(5000);

declare error int;

declare v_error numeric(10);

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION set error=-3;

DECLARE CONTINUE HANDLER FOR SQLWARNING set error=-1;

DECLARE CONTINUE HANDLER FOR NOT FOUND set error=-2;

set error = 0;

set out_retcode = 0;

set out_message = 'success!';

set @s_file = concat('/mysql/user_info',date_format(sysdate(),'%Y%m%d'),'.dat');

set @s_sqlstr =

concat("select * from user_info into outfile '", @s_file,"'

character set utf8

fields terminated by x'1e' optionally enclosed by '""'

lines terminated by '\n' ");

prepare sqlExecute from @s_sqlstr;

execute sqlExecute;

deallocate prepare sqlExecute;

set v_error=error;

if v_error<>0 then

set out_retcode = 9;

set out_message = 'failure!';

end if;

end$

delimiter ;

call sp_user_info_exp(@out1,@out2);

select @out1,@out2;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29911917/viewspace-1281921/，如需转载，请注明出处，否则将追究法律责任。