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

0    514    3

Tags:

👉 本文共约3726个字,系统预计阅读时间或需15分钟。

如何查看和下载Oracle BLOB类型的数据:https://www.xmmup.com/ruhechakanhexiazaioracle-blobleixingdeshuju.html

使用load_file函数

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

mysqldump之 --hex-blob

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

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

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

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

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

工具导出

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

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

img

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

img

MySQL之max_allowed_packet

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

5.7

max_allowed_packet

PropertyValue
Command-Line Format--max-allowed-packet=#
System Variablemax_allowed_packet
ScopeGlobal, Session
DynamicYes
TypeInteger
Default Value4194304
Minimum Value1024
Maximum Value1073741824

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

PropertyValue
Command-Line Format--max-allowed-packet=#
System Variablemax_allowed_packet
ScopeGlobal, Session
DynamicYes
SET_VAR Hint AppliesNo
TypeInteger
Default Value67108864
Minimum Value1024
Maximum Value1073741824

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.

标签:

头像

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

4 × 3 =

 

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部
返回顶部