ITPub博客

首页 > 数据库 > MySQL > MySQL插入图片或pdf文件到数据库中(BLOB)--load_file函数

MySQL插入图片或pdf文件到数据库中(BLOB)--load_file函数

原创 MySQL 作者:lhrbest 时间:2020-05-29 09:39:23 0 删除 编辑

MySQL插入图片或pdf文件到数据库中(BLOB)--load_file函数



     Oracle BLOB类型的数据如何查看和下载?: http://blog.itpub.net/26736162/viewspace-2639269/


在MySQL中可以使用load_file函数处理:

drop table lhrdb.t_image;
CREATE table lhrdb.t_image(id int PRIMARY key auto_increment, `img` longblob NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  
select * from lhrdb.t_image;
insert into lhrdb.t_image(img) values(load_file('/tmp/1.jpg')),(load_file('/tmp/PerconaXtraBackup-2.4.20.pdf'));
select load_file('/tmp/1.jpg'),load_file('/tmp/PerconaXtraBackup-2.4.20.pdf') ;
select LENGTH(load_file('/tmp/1.jpg')),length(load_file('/tmp/PerconaXtraBackup-2.4.20.pdf'));
show  variables like '%max_allowed_packet%';
select @@secure_file_priv,@@max_allowed_packet;
set GLOBAL max_allowed_packet=1*1024*1024*1024; -- 最新1024,最大1g,5.7默认4M,8.0默认64M
在使用load_file函数时需要注意以下几点:
1、若没有加载成功,则会返回空。
2、必须设置参数secure_file_priv,文件需要放在该参数路径下
3、Windows环境下的文件夹识别符是 “/”,例如:“select load_file('C:/tmp/1.jpg') ;”
4、被加载的文件的大小需要小于参数max_allowed_packet的值
5、只能加载MySQL服务器上的文件secure_file_priv变量路径下的文件


mysqldump之 --hex-blob

Image.png

该参数将下面数据类型的栏位的数据以十六进制的形式导出

  • BINARY
  • VARBINARY
  • BLOB 
  • BIT
  • 以及binary字符集的数据

其中MySQL的BLOB类型可以有如下类型

  • tinyblob:仅255个字符
  • blob:最大限制到65K字节
  • mediumblob:限制到16M字节
  • longblob:可达4GB


使用Navicat Preminum可以将二进制数据导出来


也可以直接使用DBeaver查看二进制图片:






https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_allowed_packet


---- 5.7

max_allowed_packet

Property Value
Command-Line Format --max-allowed-packet=#
System Variable max_allowed_packet
Scope Global, Session
Dynamic Yes
Type Integer
Default Value 4194304
Minimum Value 1024
Maximum Value 1073741824

The maximum size of one packet or any generated/intermediate string, or any parameter sent by the  mysql_stmt_send_long_data() C API function. The default is 4MB.

The packet message buffer is initialized to  net_buffer_length bytes, but can grow up to  max_allowed_packet bytes when needed. This value by default is small, to catch large (possibly incorrect) packets.

You must increase this value if you are using large  BLOB columns or long strings. It should be as big as the largest  BLOB you want to use. The protocol limit for  max_allowed_packet is 1GB. The value should be a multiple of 1024; nonmultiples are rounded down to the nearest multiple.

When you change the message buffer size by changing the value of the  max_allowed_packet variable, you should also change the buffer size on the client side if your client program permits it. The default  max_allowed_packet value built in to the client library is 1GB, but individual client programs might override this. For example,  mysql and  mysqldump have defaults of 16MB and 24MB, respectively. They also enable you to change the client-side value by setting  max_allowed_packet on the command line or in an option file.

The session value of this variable is read only. The client can receive up to as many bytes as the session value. However, the server will not send to the client more bytes than the current global  max_allowed_packet value. (The global value could be less than the session value if the global value is changed after the client connects.)


--- 8.0 

max_allowed_packet

Property Value
Command-Line Format --max-allowed-packet=#
System Variable max_allowed_packet
Scope Global, Session
Dynamic Yes
SET_VAR Hint Applies No
Type Integer
Default Value 67108864
Minimum Value 1024
Maximum Value 1073741824

The maximum size of one packet or any generated/intermediate string, or any parameter sent by the  mysql_stmt_send_long_data() C API function. The default is 64MB.

The packet message buffer is initialized to  net_buffer_length bytes, but can grow up to  max_allowed_packet bytes when needed. This value by default is small, to catch large (possibly incorrect) packets.

You must increase this value if you are using large  BLOB columns or long strings. It should be as big as the largest  BLOB you want to use. The protocol limit for  max_allowed_packet is 1GB. The value should be a multiple of 1024; nonmultiples are rounded down to the nearest multiple.

When you change the message buffer size by changing the value of the  max_allowed_packet variable, you should also change the buffer size on the client side if your client program permits it. The default  max_allowed_packet value built in to the client library is 1GB, but individual client programs might override this. For example,  mysql and  mysqldump have defaults of 16MB and 24MB, respectively. They also enable you to change the client-side value by setting  max_allowed_packet on the command line or in an option file.

The session value of this variable is read only. The client can receive up to as many bytes as the session value. However, the server will not send to the client more bytes than the current global  max_allowed_packet value. (The global value could be less than the session value if the global value is changed after the client connects.)


LOAD_FILE( file_name)

Reads the file and returns the file contents as a string. To use this function, the file must be located on the server host, you must specify the full path name to the file, and you must have the  FILE privilege. The file must be readable by all and its size less than  max_allowed_packet bytes. If the  secure_file_priv system variable is set to a nonempty directory name, the file to be loaded must be located in that directory.

If the file does not exist or cannot be read because one of the preceding conditions is not satisfied, the function returns  NULL.

The  character_set_filesystem system variable controls interpretation of file names that are given as literal strings.

mysql> UPDATE t            SET blob_col=LOAD_FILE('/tmp/picture')
            WHERE id=1;






About Me

........................................................................................................................

● 本文作者:小麦苗,部分内容整理自网络,若有侵权请联系小麦苗删除

● 本文在itpub、博客园、CSDN和个人微 信公众号( DB宝)上有同步更新

● 本文itpub地址: http://blog.itpub.net/26736162

● 本文博客园地址: http://www.cnblogs.com/lhrbest

● 本文CSDN地址: https://blog.csdn.net/lihuarongaini

● 本文pdf版、个人简介及小麦苗云盘地址: http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答: http://blog.itpub.net/26736162/viewspace-2134706/

● DBA宝典今日头条号地址: http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

........................................................................................................................

● QQ群号: 230161599 、618766405

● 微 信群:可加我微 信(lhrbestxh),我拉大家进群,非诚勿扰

● 联系我请加QQ好友 646634621 ,注明添加缘由

● 于 2020-05-01 06:00 ~ 2020-05-30 24:00 在西安完成

● 最新修改时间:2020-05-01 06:00 ~ 2020-05-30 24:00

● 文章内容来源于小麦苗的学习笔记,部分整理自网络,若有侵权或不当之处还请谅解

● 版权所有,欢迎分享本文,转载请保留出处

........................................................................................................................

小麦苗的微店https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

小麦苗出版的数据库类丛书http://blog.itpub.net/26736162/viewspace-2142121/

小麦苗OCP、OCM、高可用网络班http://blog.itpub.net/26736162/viewspace-2148098/

小麦苗腾讯课堂主页https://lhr.ke.qq.com/

........................................................................................................................

请扫描下面的二维码来关注小麦苗的微 信公众号( DB宝)及QQ群(230161599、618766405)、添加小麦苗微 信(lhrbestxh), 学习最实用的数据库技术。

........................................................................................................................

欢迎与我联系

 

 



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

全部评论
【QQ:646634621】【微信:db_bao】【微信公众号:DB宝】【11g、12c OCM】【QQ群:230161599、618766405】【《数据库笔试面试宝典》作者】【OCP、OCM、高可用(RAC+DG+OGG)、MySQL培训班已开讲,只讲实用内容】

注册时间:2012-09-23

  • 博文量
    1516
  • 访问量
    8778025