Mysql 中的blob相关问题

一、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编码的问题，于是将原来的连接字符串设置为：

url=url++"?useUnicode=true&characterEncoding=utf-8";

 问题解决了，不仅能支持各种格式的文件，图片也显示正常了。如果设置为其他的字符集就会出现前面的错误。

 

四、max_allowed_packet参数设置

往数据库中存储较大的文件是出现如下错误：

 

<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文件中的最后一行添加

max_allowed_packet = 10M(也可以设置自己需要的大小)。

 五、效率问题

利用数据库存储大量文件时，查询效率就会变得很低。

在表的设计上，我们可以选择吧文件的相关信息存在一个表中fileInfo，而吧文件内容存在另一个表中fileContent，fileContent中有一个指向fileInfo的外键。这样，查询的时候只需要访问fileInfo，只有当要访问某个文件具体内容的时候才访问fileContent表。分表存储，能够显著提高查询速度。

 

-------------------------------------------------------------------------------------------------

 

  CLOB类型默认为1m 如果大于的话可能会出现

   Packet for query is too large (37748784 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable

 异常 这是可以在

[mysqld]下面添加 max_allowed_packet=10M来扩大限制

在 jdk 6。0 以前 向数据库插入clob 和blob数据的操作方法：

 void setBinaryStream(int parameterIndex, java.io.InputStream x, int length)方法

而在jdk 6.0以后就可以用

void setClob(int parameterIndex, Reader reader)

void setBlob(int parameterIndex, InputStream inputStream)方法来插入clob/blob

读取时候可以用读取流来处理或者用：

clob的时候可以

用 Clob.getSubString(pos, length)不过length是个int型的

或者用

Clob c = rs.getClob("clumn");
StringBuffer a = new StringBuffer(1024);
Reader r = c.getCharacterStream();
char[] cc = new char[1];
int i = -1;
while((i =r.read(cc))!=-1){
    a.append(cc);
}

 读取blob可以用：

Blob b = rs.getBlob("clumn");
 java.io.InputStream getBinaryStream ()或者getBytes(pos, length)