MySQL插入图片或pdf文件到数据库中（BLOB）--load_file函数
如何查看和下载Oracle BLOB类型的数据：https://www.xmmup.com/ruhechakanhexiazaioracle-blobleixingdeshuju.html
使用load_file函数
在MySQL中可以使用load_file函数处理：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | 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查看二进制图片：
MySQL之max_allowed_packet
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_allowed_packet
5.7
| 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
| 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.)
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.
1 | mysql> UPDATE t SET blob_col=LOAD_FILE('/tmp/picture') WHERE id=1; |
