图片存储

怎样在mysql中存储比较大的图片？
如果你想把二进制的数据,比如说图片文件和HTML文件,直接保存在你的MySQL数据库，那么这篇文章就是为你而写的！我将告诉你怎样通过HTML表单来储存这些文件，怎样访问和使用这些文件。      一、本文概述      本文的主要内容如下：      ＊  在MySQL中建立一个新的数据库
  ＊  一个怎样储存文件的例子程序
  ＊  一个怎样访问文件的例子程序      二、在MySQL中建立一个新的database      首先，你必须在你的MySQL中建立一个新的数据库，我们将会把那些二进制文件储存在这个数据库里。在例子中我会使用下列结构，为了建立数据库，你必须做下列步骤：      1.  进入MySQL控制器
  2.  输入命令"create  database  binary_data;"
  3.  输入命令"use  binary_data;"      输入如下命令：      "CREATE  TABLE  binary_data  (  id  INT(4)  NOT  NULL  AUTO_INCREMENT  PRIMARY  KEY,description  CHAR(50),  bin_data  LONGBLOB,  filename  CHAR(50),  filesize  CHAR(50),  filetype  CHAR(50));"  （不能断行）      如果没有意外，数据库  和  表  应该建立好了。    三、一个怎样储存文件的例子程序      用这个例子你可以通过Html表单将文件传输到数据库中。      store.php3      //  store.php3  -  by  Florian  Dittmer      ?>      //  如果提交了表单，代码将被执行:      if  ($submit)  {      //  连接到数据库
  //  (你可能需要调整主机名，用户名和密码)      MYSQL_CONNECT(  "localhost",  "root",  "password");
  MySQL_select_db(  "binary_data");
  $data  =  addslashes(fread(fopen($form_data,  "r"),  filesize($form_data)));
  $result=MYSQL_QUERY(  "INSERT  INTO  binary_data  (description,bin_data,filename,filesize,filetype)VALUES  ('$form_description','$data','$form_data_name','$form_data_size','$form_data_type')");
  $id=  MySQL_insert_id();
  print  "This  file  has  the  following  Database  ID:  $id";
  MYSQL_CLOSE();
  }  else  {      //  否则显示储存新数据的表单      ?>     @MySQL_select_db(  "binary_data");
  $query  =  "select  bin_data,filetype  from  binary_data  where  id=$id";
  $result  =  @MYSQL_QUERY($query);
  $data  =  @MYSQL_RESULT($result,0,  "bin_data");
  $type  =  @MYSQL_RESULT($result,0,  "filetype");
  Header(  "Content-type:  $type");
  echo  $data;
  };
  ?>
程序必须知道要访问那个文件，  你必须将ID作为一个参数。      例如:  一个文件在数据库中的ID为2.  你可以这样调用它:  getdata.php3?id=2      如果你将图片储存在数据库里,  你可以向调用图片一样调用它。      Example:  一个图片文件在数据库中的ID为3.  你可以这样调用它:      五、怎样储存大于1MB的文件      如果你想储存大于1MB的文件，你必须对你的程序、PHP设置、SQL设置进行许多修改。      下面几条也许可以帮助你储存小于24MB的文件：      1)  修改  store.php3，将  MAX_FILE_SIZE  的值改成  24000000。      2)  修改你的PHP设置，在一般情况下，PHP只允许小于2MB的文件，你必须将max_filesize（在php.ini中）的值改成24000000      3)  去掉MYSQL的数据包大小限制,在一般情况下  MYSQL  小于1  MB的数据包。      4)  你必须用以下参数重启你的MYSQL  ：/usr/local/bin/safe_MySQLd  -O  key_buffer=16M  -O  table_cache=128  -O  sort_buffer=4M  -O  record_buffer=1M  -O  max_allowed_packet=24M      5)  如果仍然出错：可能是超时错误，如果你通过一个很慢的连接来储存一个很大的文件，PHP缺省的时间限制为30秒。你可以将max_execution_time(在php.ini中)的值改为-1
下面是一个老外写的，可以读Saving  Images  in  MySQL    Sometimes,  it's  more  convenient  to  save  images  in  a  database  than  as  files.
MySQL  and  PHP  make  it  very  easy  to  do  this.  In  this  article,  I  will  describe
how  to  save  images  in  a  MySQL  database  and  display  them  later  on.       Setting  up  the  database
The  difference  between  any  regular  text  or  integer  fields  and  a  field  that
needs  to  save  an  image  is  the  amount  of  data  that  is  needed  to  be  held  in  the
field.  MySQL  uses  special  fields  to  hold  large  amounts  of  data.  These  fields
are  known  as  blobs  (blob).    Here  is  the  BLOB  definition  from  the  MySQL  site  :    A  BLOB  is  a  binary  large  object  that  can  hold  a  variable  amount  of  data.  The
four  BLOB  types  TINYBLOB,  BLOB,  MEDIUMBLOB  and  LONGBLOB  differ  only  in  the
maximum  length  of  the  values  they  can  hold    For  more  information  about  MySQL  BLOBs  check  out  http://www.mysql.net/Manual_c
hapter/manual_Reference.html#BLOB  Use  the  next  syntax  to  create  a  basic  table  that  will  hold  the  images:

           CREATE  TABLE  Images  (
                       PicNum  int  NOT  NULL  AUTO_INCREMENT  PRIMARY  KEY,
                       Image  BLOB
           );

   Setting  the  upload  script
An  example  of  a  file  upload  front  end  can  be  seen  at  File  Uploading  by  berber
(29/06/99).  What  we  need  now  is  the  PHP  script  that  will  get  the  file  and
insert  it  into  MySQL.  The  next  script  does  just  that.  In  the  script,  I'm
assuming  that  the  name  of  the  file  field  is  "icture".
<?
If($Picture  !=  "none")  {
           $PSize  =  filesize($Picture);
           $mysqlPicture  =  addslashes(fread(fopen($Picture,  "r"),  $PSize));
           unlink($Picture);
           mysql_connect($host,$username,$password)
                       or  die("Unable  to  connect  to  SQL  server");
           @mysql_select_db($db)
                       or  die("Unable  to  select  database");
           mysql_query("INSERT  INTO  Images  (Image)  VALUES  '($mysqlPicture')")
                       or  die("Can't  Perform  Query");
}
else  {
           echo"You  did  not  upload  any  picture";
}
?>  This  is  all  that  is  needed  to  enter  the  image  into  the  database.  Note  that  in
some  cases  you  might  get  an  error  when  you  try  to  insert  the  image  into
MySQL.  In  such  a  case  you  should  check  the  maximum  packet  size  allowed  by
your  MySQL  ver.  It  might  be  too  small  and  you  will  see  an  error  about  this  in
the  MySQL  error  log.    What  we  did  in  the  above  file  is  :  1.  Check  if  a  file  was  uploaded  with  If($Picture  !=  "none").
2.  addslashes()  to  the  picture  stream  to  avoide  errors  in  MySQL.
3.  Delete  the  temporary  file.
3.  Connect  to  MySQL,  choose  the  database  and  insert  the  image.
   Displaying  the  Images
     Now  that  we  know  how  to  get  the  images  into  the  database  we  need  to  figure
out  how  to  get  them  out  and  display  them.  This  is  more  complicated  than
getting  them  in  but  if  you  follow  these  steps  you  will  have  this  up  and
running  in  no  time.    Since  showing  a  picture  requires  a  header  to  be  sent,  we  seem  to  be  in  an
impossible  situation  in  which  we  can  only  show  one  picture  and  than  we  can't
show  anymore  Since  once  the  headers  are  sent  we  can't  send  any  more  headers.
This  is  the  tricky  part.  To  outsmart  the  system  we  use  two  files.  The  first
file  is  the  HTML  template  that  knows  where  we  want  to  display  the  image(s).
It's  a  regular  PHP  file,  which  builds  the  HTML  that  contains  the  <IMG>  tags,
as  we  want  to  display  them.  The  second  file  is  called  to  provide  the  actual
file  stream  from  the  database  directly  into  the  SRC  property  of  the  <IMG>
tag.    This  is  how  a  simple  script  of  the  first  type  should  look  like:
<HTML>
<BODY>
<?
           mysql_connect($host,$username,$password)
                       or  die("Unable  to  connect  to  SQL  server");
           @mysql_select_db($db)
                       or  die("Unable  to  select  database");
           mysql_query("SELECT  *  FROM  Images")
                       or  die("Can't  Perform  Query");
           While($row=mysql_fetch_object($result))  {
                       echo  "<IMG  SRC=\"SecondType.php3?PicNum=$row->icNum\">";
           }
?>
</BODY>
</HTML>  While  the  HTML  is  being  displayed,  the  SecondType.php3  file  is  called  for
each  image  we  want  to  display.  The  script  is  called  with  the  Picture  ID
(PicNum)  which  allows  us  to  fetch  the  image  and  display  it.    The  SecondType.php3  file  looks  like  this  :
<?
$result=mysql_query("SELECT  *  FROM  Images  WHERE  PicNum=$PicNum")
           or  die("Can't  perform  Query");
$row=mysql_fetch_object($result);
Header(  "Content-type:  image/gif");
echo  $row->Image;
?>
This  is  the  whole  theory  behind  images  and  MySQL.  The  scripts  in  this  example
are  the  basics.  You  can  now  enhance  these  scripts  to  include  thumbnails,  set
the  images  in  various  positions,  enhance  the  database  table  to  hold  an  ALT
field,  Check  the  width  and  height  of  the  images  before  you  insert  them  into
the  database  and  keep  that  data  in  the  table  too  etc...

解决方案 »