精华内容
下载资源
问答
  • mysql blob

    2014-06-13 17:51:00
    ...MySQL中,BLOB是个类型系列,包括:TinyBlob、Blob、MediumBlob、LongBlob,这几个类型之间的唯一区别是在存储文件的最大大小上不同。 MySQL的四种BLOB类型 类型 大小(单位:字节) TinyBlo...

    mysql blob

    1 blob类型

    BLOB类型的字段用于存储二进制数据

    MySQL中,BLOB是个类型系列,包括:TinyBlob、Blob、MediumBlob、LongBlob,这几个类型之间的唯一区别是在存储文件的最大大小上不同。

    MySQL的四种BLOB类型
    类型 大小(单位:字节)
    TinyBlob 最大 255
    Blob 最大 65K
    MediumBlob 最大 16M
    LongBlob 最大 4G


    2 导入含有blob数据的sql库时产生相关的2个错误

    [Err] 2013 - Lost connection to MySQL server during query

    MySQL server has gone away

    解决方法:

    my.ini最后加入  

    max_allowed_packet = 16M  默认是1M,需要单独设置这个

    wait_timeout=2880000 设置延时时间
    interactive_timeout = 2880000

    然后重启mysql服务

    在cmd中 net stop mysql,     net start mysql


    3 查询max_allowed_packet的方法

    step1:cmd

    step2:mysql -u root -p

    step3:黑屏输入密码

    step4:show VARIABLES like '%max_allowed_packet %';



    posted @ 2014-06-13 17:51 00000000O 阅读( ...) 评论( ...) 编辑 收藏
    展开全文
  • MySQL BLOB

    2014-08-01 18:36:59
    一、MySQL BLOB 类型介绍 MySQL中,BLOB是一个二进制大型对象,是一个可以存储大量数据的容器,它能容纳不同大小的数据。BLOB类型实际是个类型系列(TinyBlob、Blob、MediumBlob、LongBlob),除了在存储的最大信息...

    {转帖:楚河汉界一小孩儿http://blog.sina.com.cn/itbrein }

    一、MySQL BLOB 类型介绍
    MySQL中,BLOB是一个二进制大型对象,是一个可以存储大量数据的容器,它能容纳不同大小的数据。BLOB类型实际是个类型系列(TinyBlob、Blob、MediumBlob、LongBlob),除了在存储的最大信息量上不同外,他们是等同的。

    MySQL的四种BLOB类型 
     类型  大小(单位:字节) 
     TinyBlob  最大 255B
     Blob  最大 65K 
     MediumBlob  最大 16M 
     LongBlob  最大 4G

    实际使用中根据需要存入的数据大小定义不同的BLOB类型。 
    需要注意的是:如果你存储的文件过大,数据库的性能会下降很多。  
     
    二、mysql中的blob存取
    create table Dish {
     int id;
    blob photo;
    };
    下面是从数据库里写的方法:
             String filepath = (String)session.getAttribute("file");//这里获得的是用jspsmartupload上传的文件的路径
              File file = new File(filepath);
              FileInputStream fin = new FileInputStream(file);
          
              dataBS = new blobConn();   
              con = dataBS.getConn();
              String erpsql = "insert into Dish values(?,?)";
              PreparedStatement stmt = con.prepareStatement(erpsql);
              stmt.setString(2,String.valueOf(id));
              stmt.setBinaryStream(3,fin,(int)file.length());//想数据库里插入是很简单的,就一行,但这种方法只有mysql可以用
              stmt.executeUpdate();
                                
              fin.close();
              stmt.close();
              con.close();
    下面是从数据库里读的方法:
    1.BufferedInputStream inputimg = null;
    try {
     Connection con = sqlDS.getConnection();//简写,获得数据库连接
    Statement stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery("select from Dish where id = 11");
    if(rs.next()){
    java.sql.Blob blob = (java.sql.Blob)rs.getBlob("photo");
    input = new BufferedInputStream(blob.getBinaryStream);
    }
    BufferedImage image = null;
    image = javax.imageio.ImageIo.read(input);
    ServlerOutputStream sos = response.getOutputStream();
    JPEGImageEncoder encoder = JPEGCodec.createJPEGEncoder(sos);
    encoder.encode(image);
    input.close();
    }catch(Exception e) {
    e.printStackTrace();
    }
     
    2.
    if(rs.next()){
        res.setContentType ("image/jpeg;charset=GB2312");//HttpServletResponse res
        ServletOutputStream out = res.getOutputStream ();
       
        BufferedInputStream jpgData = new BufferedInputStream (rs.getBinaryStream ("photo"));
        byte [] buf = new byte [4*1024];
        int len;
        if(jpgData.available () <= 0x0)//判断数据库里存放图片的字段是否有值,可以进行其他处理
         res.sendRedirect ("/images/nophoto.gif"); 
        
        while((len = jpgData.read (buf, 0, buf.length)) != -1)
         out.write (buf, 0, len);
    }
    3.
    if(rs.next()){
            res.setContentType("image/jpeg");  
            ServletOutputStream   out=res.getOutputStream();  
            InputStream   in=rs.getBinaryStream("photo");  
            byte   buff[]=new   byte[1024];  
            int   i;  
            while((i=in.read(buff))!=-1){   
                  out.write(buff);  
            }  
            in.close();   
            out.close();
    }  
     
    三、charset设置对blog操作的影响
    存储txt文件的时候没有问题;存储图片也没问题,但是再把图片图片从数据库中取出来,不能正常显示了;存储word格式的文件报错,如下:
    Caused by: java.sql.BatchUpdateException: Syntax error or access violation message from server: "You have an error in your SQL syntax near ''D0CF11E0A1B11AE1000000000000000000000000000000003E000300FEFF0900060000000000000' at line 1"
        at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1540)
    查了一下可能是charset编码的问题,于是将原来的连接字符串设置为:
    Java代码
    url=url++"?useUnicode=true&characterEncoding=utf-8";  
     问题解决了,不仅能支持各种格式的文件,图片也显示正常了。如果设置为其他的字符集就会出现前面的错误。
     
    四、max_allowed_packet参数设置
    往数据库中存储较大的文件是出现如下错误:
     
    Java代码
    <strong>java.lang.IllegalArgumentException: Packet is larger than max_allowed_packet from server configuration of 1048576 bytes</strong>  
    这是因为存入的文件大于mysql默认的 max_allowed_packet值。
    解决办法:在mysql安装目录下的my.ini文件中的最后一行添加
    Java代码
    max_allowed_packet = 10M(也可以设置自己需要的大小)。  
     五、效率问题
    利用数据库存储大量文件时,查询效率就会变得很低。
    在表的设计上,我们可以选择吧文件的相关信息存在一个表中fileInfo,而吧文件内容存在另一个表中 fileContent,fileContent中有一个指向fileInfo的外键。这样,查询的时候只需要访问fileInfo,只有当要访问某个文 件具体内容的时候才访问fileContent表。分表存储,能够显著提高查询速度。

    展开全文
  • I am trying to display an image from a MySQL blob field. I have tried a few different things and none of them seem to work.I have tried:header("Content-type: $type"); img src = $blobData;header("Conte...

    I am trying to display an image from a MySQL blob field. I have tried a few different things and none of them seem to work.

    I have tried:

    header("Content-type: $type"); img src = $blobData;

    header("Content-type: $type"); echo($blobData);

    解决方案

    Another option you might consider (assuming you are on Apache):

    Create an .htaccess file with a mod_rewrite for all image extensions (png, jpg, gif).

    Have it redirect to a php script that looks up the image requested in the DB. If it is there, it echos out the header and BLOG. If it isn't there, it returns a standard 404.

    This way you can have:

    adorablepuppy.jpg

    Which then gets redirected ala:

    RewriteEngine on

    RewriteRule \.(gif|jpg|png)$ imagelookup.php

    This script does a query for the image, which (obviously) assumes that the requested image has a unique key that matches the filename in the URL:

    $url = $_SERVER['REQUEST_URI'];

    $url_parts = explode("/", $url);

    $image_name = array_pop($url_parts);

    Now you have just the image filename. Do the query (which I shall leave up to you, along with any validation methods and checks for real files at the address, etc.).

    If it comes up with results:

    header('Content-type: image/jpeg');

    header('Content-Disposition: inline; filename="adorablepuppy.jpg"');

    print($image_blog);

    otherwise:

    header("HTTP/1.0 404 Not Found");

    FYI: I have no idea if this would be bad in terms of performance. But it would allow you to do what I think you want, which is output the image as though it were a flat image file on the server using a simple image element. I'm inclined to agree that BLOBs are not the best way to go, but this does avoid any cross-browser issues.

    展开全文
  • Anybody have some VBA code that will store a byte array into a MySQL blob column?解决方案Here is some code. Requires a reference to Microsoft Active Data Objects 2.x Library. It uses the OLE DB ...

    Anybody have some VBA code that will store a byte array into a MySQL blob column?

    解决方案

    Here is some code. Requires a reference to Microsoft Active Data Objects 2.x Library. It uses the OLE DB provider for MySQL (Might need to install that on the client machine).

    Sub StoreBLOB(data() As Byte, key As Double)

    'stores the BLOB byte array into the row identified by the key

    'requires reference to Microsoft Active Data Objects 2.x Library

    On Error GoTo handler:

    Dim con As New ADODB.Connection

    Dim rs As New ADODB.Recordset

    Dim conStr As String

    Dim strSQL As String

    'have it return only the record you want to store your blob

    strSQL = strSQL & "SELECT * FROM YOURTABLE WHERE KEY = " & key

    'setup connection

    conStr = conStr & "Provider=MySQLProv;"

    conStr = conStr & "Data Source=mydb;"

    conStr = conStr & "User Id=myUsername;"

    conStr = conStr & "Password=myPassword;"

    con.ConnectionString = conStr

    con.Open

    rs.Open strSQL, con, adOpenDynamic, adLockOptimistic

    If rs.RecordCount > 1 Then

    Err.Raise 1001, "StoreBLOB", "Too many records returned from dataset. Check to make sure you have the right key value"

    Else

    Err.Raise 1002, "StoreBLOB", "No Records found that match the key"

    End If

    rs.Fields("BLOBFIELDNAME").Value = data

    rs.Update 'store the contents to the database

    rs.Close

    con.Close

    Set rs = Nothing

    Set con = Nothing

    Exit Sub

    handler:

    Err.Raise 1003, "StoreBLOB", "Unexpected Error in StoreBLOB. Check that server is running"

    End Sub

    展开全文
  • Mysql Blob

    2011-04-22 14:23:00
    BLOB介绍BLOB (binary large object),二进制大对象,是一个可以存储二进制文件的容器。在计算机中,BLOB常常是数据库中用来存储二进制文件的字段类型。BLOB是一个大文件,典型的BLOB是一张图片或一个声音文件,由于...
  • Mysql Blob类型

    2019-10-28 19:41:24
    Mysql Blob类型Blob 4种类型mysqldump 带有Blob类型的字段,恢复时候blob类型字段会乱码,怎么避免? Blob 4种类型 tinyblob:仅255个字符 blob:最大限制到65K字节 mediumblob:最大限制到16M字节 longblob:最大可达4GB ...
  • BLOB是一个大文件,典型的BLOB是一张图片或一个声音文件,由于它们的尺寸,必须使用特殊的方式来处理(例如:上传、下载或者... mysql BLOB类型 MySQL中,BLOB是个类型系列,包括:TinyBlob、Blob、MediumBlob、LongBl
  • Am using the following code to get the blob data from the image file stored in the local disk.. I want to save that blob data into the blob field of database.CLongBinary m_BLOBImage;CFileStatus fileSt...
  • I'm coding an application that will be uploading and deleting many files, i usually just move the files to a folder in the server naming them with the row unique ... But as i understand MySQL also let...
  • mysql BLOB数据类型分类

    千次阅读 2018-11-03 13:46:35
    mysql BLOB是是一个二进制大对象,可以容纳可变数量的数据,下面分别说说mysql BLOB数据类型分类,可以分为 有 4 种 BLOB 类型:TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。来自www.oceanoemchina.com,分别讲述其...
  • I would like to use MYSQL as a storage system for a huge number of files.I would like to read/write just a portion of the data stored in a column (data is stored as bytes) so I don't have to load the ...
  • Jdbc操作Mysql BLOB类型数据 目录 Jdbc操作Mysql BLOB类型数据 一、MysqlBLOB类型数据 二、向数据表中插入数据 三、修改数据表中的BLOB类型字段 四、从数据表中读取BLOB类型数据 一、MysqlBLOB类型...
  • Mysql BLob类型 Mysql中,BLoB是一个二进制大型对象,可以储存大量数据的容器,它可以容纳不同大小的数据 插入BLob类型的数据必须使用PreparedStatement,因为BLOB类型的数据无法使用字符串拼接 mysql的四种BLOB类型...
  • MySQL blob字段报错 总结来说, Data truncation: Data too long for column ‘CONTENT’ at row 1问题的出现主要是以下几个原因: 1、可能 数据库中的字符集的编码与写代码的页面的编码不相符合;(一般为utf-8) 2...
  • mysql blob 查看

    2020-06-23 17:30:41
    CONVERT (FIELD USING utf8) SELECT *, CONVERT (CONTENT USING utf8) as CONTENT FROM T_CONTENT
  • 查看mysql blob字段的内容

    万次阅读 2019-07-17 10:01:12
    使用Navicat 12 for mysql查看blob字段的内容。注意要根据blob字段的类型选择对应的查看方式,常见的有文本,图像两种方式,否则会出现乱码。
  • MySQL BLOB 类型介绍

    2009-02-09 10:24:00
    MySQL BLOB 类型介绍MySQL中,BLOB是一个二进制大型对象,是一个可以存储大量数据的容器,它能容纳不同大小的数据。BLOB类型实际是个类型系列(TinyBlob、Blob、MediumBlob、LongBlob),除了在存储的最大信息量上...
  • MySql Blob Hibernate 存取

    千次阅读 2008-11-12 11:27:00
    1、数据库字段MySql Blob字段,默认非空表:attendance字段:ATTEND_IMAGE longblob2、图片利用HIbernate从文件存储到MySqlFileInputStream fileInputStream = new FileInputStream("e://1.gif");Blob blob = ...
  • mysql BLOB类型

    千次阅读 2015-06-30 15:55:13
    MySQL中,BLOB是个类型系列,包括:TinyBlob、Blob、MediumBlob、LongBlob,这几个类型之间的唯一区别是在存储文件的最大大小上不同。   MySQL的四种BLOB类型   类型 大小(单位:字节)   TinyBlob 最大 255...
  • 哪位大神写过 用mybatis 上传文件 保存到mysql数据库BLOB中的
  • On the other hand, you have BLOB and CLOB which are more suitable for data storage (images, etc) due to their larger capacity limits (4GB for example). As for the difference between BLOB and CLOB, I ...
  • mysql blob读写文件

    2020-03-06 18:07:40
    #写入 $host='127.0.0.1'; $user='root'; $password='root';...$path='filepath';...$file='0x'.bin2hex(file_get_contents($path));...$filewrite=new mysqli($host,$user,$password);...blob读写数据库方法,超精简。
  • Mysql blob Data truncation: Data too long for column   Use following data types as per your need   TINYBLOB : maximum length of 255 bytes BLOB : maximum length of 65,535 bytes ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 74,009
精华内容 29,603
关键字:

mysqlblob

mysql 订阅