| MySQL插入图片或pdf文件到数据库中（BLOB） | 您所在的位置：网站首页 › 奶糖派内衣知乎 › MySQL插入图片或pdf文件到数据库中（BLOB） |
MySQL插入图片或pdf文件到数据库中（BLOB）
|
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
该参数将下面数据类型的栏位的数据以十六进制的形式导出 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宝典今日头条号地址： ........................................................................................................................ ● 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 ● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解 ● 版权所有，欢迎分享本文，转载请保留出处 ........................................................................................................................ ● 小麦苗的微店： ● 小麦苗出版的数据库类丛书： 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）， 学习最实用的数据库技术。
........................................................................................................................ 
|
【本文地址】