mysql批量导出blob为文件

1-文件列表

2-执行结果

21-执行完成image_dump-1.bat后

文件列表

文件解释

bmp:导出文件所在文件夹

bmp_2:由于导出文件的文件名称或者所在路径有中文字符，需要中转的文件夹

copy_bat.bat:用于把导出的文件复制到含有中文路径的位置或者重命名含有中文的文件名称

image_load.sql:从数据库中导出图片的sql命令

iris_list.txt:数据库中人员的虹膜列表

person_list:数据库中人员的列表

bmp内的文件

22-执行完成sql_dump-2.bat后

3-参数

31-image_dump-1.bat

         mysql_path:mysql的安装目录

         bin_path：mysql的bin目录，即mysql_path下的bin目录

         db_name：导出图片的数据库名称

         root_password：root用户的密码

         table_person:人员基础信息表名称

         table_iris:人员的虹膜信息表名称

        image_path:导出图片所在文件夹

        person_list:人员基础信息

        iris_list:虹膜信息

        sql_file：执行导出文件的sql命令的执行脚本

        copy_path:由于目的目录或名称含有中文字符，导出文件用于中转的文件夹

        copy_bat: 由于目的目录或名称含有中文字符，用于从中转文件夹拷贝到目的文件夹的命令所在脚本

         folder_name_1:单人文件夹下的第一个文件夹名称

         folder_name_2:单人文件夹下的第二个文件夹名称

         folder_name_3:单人文件夹下的第三个文件夹名称

         suffix_name_1:第一类导出文件的文件后缀名

        suffix_name_2:第二类导出文件的文件后缀名

        suffix_name_3:第三类导出文件的文件后缀名

32-sql_dump-2.bat

         mysql_path:mysql的安装目录

         bin_path：mysql的bin目录，即mysql_path下的bin目录

         db_name：导出图片的数据库名称

         root_password：root用户的密码

        sql_file：执行导出文件的sql命令的执行脚本

        copy_bat: 由于目的目录或名称含有中文字符，用于从中转文件夹拷贝到目的文件夹的命令所在脚本

4-执行步骤

1-image_dump-1.bat，sql_dump-2.bat在同一个目录下

2-先执行image_dump-1.bat

3-再执行sql_dump-2.bat

4-查看bat文件所在文件夹下的bmp文件夹内文件

5-脚本

51-image_dump-1.bat

@ECHO OFF
setlocal enabledelayedexpansion
:begin
	:: Command line parameters.
	set file_path=%~dp0

	::::::::::::::::::::mysql parameters::::::::::::::::::::::::::
	set mysql_path=C:\Program Files (x86)\MySQL\MySQL Server 5.6

	set bin_path=%mysql_path%\bin

	set db_name=demo

	:: the password of root 
	set root_password=ABCD@1234@QWER

	set table_person=t_person

	set table_iris=t_image

	::::::::::::::::::::file parameters::::::::::::::::::::::::::
	set image_path=%file_path%bmp\

	set person_list=%file_path%\person_list.txt

	set iris_list=%file_path%\iris_list.txt

	set sql_file=%file_path%\image_load.sql

	set copy_path=%file_path%bmp_2\

	set copy_bat=%file_path%\copy_bat.bat

	::::::::::::::::::::info parameters::::::::::::::::::::::::::
	set folder_name_1=code

	set folder_name_2=image

	set folder_name_3=info

	set suffix_name_1=

	set suffix_name_2=.bmp

	set suffix_name_3=.txt
	::::::::::::::::::::::::::::::::::::::::::::::
	if not exist "%image_path%" md "%image_path%"

	if not exist "%copy_path%" md "%copy_path%"

	::将文件夹的反斜杠改为斜杠
	set rename_bmp_path_change=%copy_path:\=/%

	::清空中间文件的数据

	::人员信息：名称,身份证号,性别,人员id
	if exist %person_list% del %person_list%

	::虹膜信息：虹膜id,人员id,边边
	if exist %iris_list% del %iris_list%

	::批量导出图片的sql
	if exist %sql_file% del %sql_file%

	::批量拷贝的命令
	if exist %copy_bat% del %copy_bat%

	::获取人员信息
	echo select concat(name,',',id_card_number,',',gender,',',id) from %table_person%;|mysql -uroot -p%root_password% %db_name%>"%person_list%"

	::循环人员信息
	for /f "skip=1 delims=, tokens=1,2,3,4 usebackq" %%i in ("%person_list%") do (
			set folder_name=%image_path%%%i
			set folder_1=%image_path%%%i\%folder_name_1%
			set folder_2=%image_path%%%i\%folder_name_2%
			set folder_3=%image_path%%%i\%folder_name_3%

			set folder_chg_1=%rename_bmp_path_change%%%i/%folder_name_1%
			set folder_chg_2=%rename_bmp_path_change%%%i/%folder_name_2%
			set folder_chg_3=%rename_bmp_path_change%%%i/%folder_name_3%

			::创建文件夹的命令
			if not exist "!folder_name!" md "!folder_name!"
			if not exist "!folder_1!" md "!folder_1!"
			if not exist "!folder_2!" md "!folder_2!"
			if not exist "!folder_3!" md "!folder_3!"

			::导出人员基本信息到文本
			echo name : %%i >> "!folder_3!\%%i%%j%%k%suffix_name_3%"
			echo idcard : %%j >> "!folder_3!\%%i%%j%%k%suffix_name_3%"
			echo gender : %%k >> "!folder_3!\%%i%%j%%k%suffix_name_3%"

			::获取虹膜信息
			echo select concat(id,',',person_id,',',side^) from %table_iris% where person_id='%%l';|mysql -uroot -p%root_password% %db_name%>"%iris_list%" 2>&1

			echo. >> "%sql_file%"

			::循环虹膜记录
			set /a left=0
			set /a right=0
			for /f "skip=2 delims=, tokens=1,2,3 usebackq" %%x in ("%iris_list%") do (
				::左眼
				if %%z==1 (
					set /a left+=1
					echo select code from %table_iris% where id='%%x' into dumpfile '%rename_bmp_path_change%%%jL!left!%suffix_name_1%'; >>"%sql_file%"
					echo select image from %table_iris% where id='%%x' into dumpfile '%rename_bmp_path_change%%%jL!left!%suffix_name_2%'; >>"%sql_file%"
					echo copy "%copy_path%%%jL!left!%suffix_name_1%" "!folder_1!\%%i%%j%%kL!left!%suffix_name_1%" >>"%copy_bat%"
					echo copy "%copy_path%%%jL!left!%suffix_name_2%" "!folder_2!\%%i%%j%%kL!left!%suffix_name_2%" >>"%copy_bat%"
				)
				::右眼
				if %%z==2 (
					set /a right+=1
					echo select code from %table_iris% where id='%%x' into dumpfile '%rename_bmp_path_change%%%jR!right!%suffix_name_1%'; >>"%sql_file%"
					echo select image from %table_iris% where id='%%x' into dumpfile '%rename_bmp_path_change%%%jR!right!%suffix_name_2%'; >>"%sql_file%"
					echo copy "%copy_path%%%jR!right!%suffix_name_1%" "!folder_1!\%%i%%j%%kR!right!%suffix_name_1%" >>"%copy_bat%"
					echo copy "%copy_path%%%jR!right!%suffix_name_2%" "!folder_2!\%%i%%j%%kR!right!%suffix_name_2%" >>"%copy_bat%"
				)
			)
	)
	if not %errorlevel%==0 goto fail
	if %errorlevel%==0 goto finish

::noexistfail
:noexistfail
	@echo There is no bmp yet!
	goto end

::fail
:fail
	@echo Fail!

:finish
	@echo Successfully!
	goto end
	
:end
	pause

52-sql_dump-2.bat

@ECHO OFF
setlocal enabledelayedexpansion
:begin
	:: Command line parameters.
	set file_path=%~dp0

	::::::::::::::::::::mysql parameters::::::::::::::::::::::::::
	set mysql_path=C:\Program Files (x86)\MySQL\MySQL Server 5.6

	set bin_path=%mysql_path%\bin

	set db_name=demo

	:: the password of root 
	set root_password=ASDF@1234@QWER

	::::::::::::::::::::file parameters::::::::::::::::::::::::::
	set sql_file=%file_path%\image_load.sql

	set copy_bat=%file_path%\copy_bat.bat
	::::::::::::::::::::::::::::::::::::::::::::::

	"%bin_path%\mysql.exe" -uroot -p%root_password% %db_name% < "%sql_file%"

	start %copy_bat%
	if not %errorlevel%==0 goto fail
	if %errorlevel%==0 goto finish

::fail
:fail
	@echo Fail!

:finish
	@echo Successfully!
	goto end
	
:end
	pause