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

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

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部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2694951/，如需轉載，請註明出處，否則將追究法律責任。