How to store and read pictures in mysql

Mysql storage method for reading images: first convert the image into a buffer stream; then obtain the byte array of the image and perform related operations; finally pass "public void MapSearchQuery(out byte[] imageByteResulet){.. .}" just read the image.

Recommended: "mysql video tutorial"

First, let’s introduce mysql related Data type: There are four BLOB types in MySQL, TinyBlob (maximum 255Byte), Blob (maximum 65K), MediunBlob (16M), LongBlob (maximum 4G). Please note here that if the related Data too long... words appear in your database, it may be that the size of the type you selected is not enough.

Next, let me briefly talk about why I did not use the method of storing the image path, but adopted the method of storing the image directly in MySQL. There are two reasons:

1. It does not need a large number of pictures, a database only needs one picture

2. The software structure is to Through WebService, a main client accesses several affiliated clients below. If the affiliated client does not store images directly for the main client to access, then the main client will inevitably need a function to load images (similar to the FTP function).

Let’s go directly to the code:

public bool MapSearchWrite(string strImagePath)
         {
             //将图片转换成缓冲流
             FileStream fs = new FileStream(strImagePath, FileMode.Open, FileAccess.Read);
             
             //获得图片的字节数组
             byte[] byImage = new byte[fs.Length];
             fs.Read(byImage, 0, byImage.Length);
             fs.Close();
             //数据库连接
             MySqlConnection conn = new MySqlConnection();
             conn.ConnectionString = "Server=localhost;Uid=root;Password=123456;Database=firefighting;charset=gb2312";
             try
             {
                 conn.Open();
             }
             catch
             {
                 conn.Close();
                 conn.Dispose();
                 throw new ArgumentException("地图检索数据库连接失败");
             }
             //判断数据库内部有无记录
             string strQueryCmd = "select PicNum from images";
             MySqlCommand cmdQuery = new MySqlCommand(strQueryCmd, conn);
             MySqlDataReader dataReader = cmdQuery.ExecuteReader();
             //执行操作
             MySqlCommand cmd = new MySqlCommand();
             if (dataReader.Read())
             {
                 cmd.CommandText = "update images set Image=@byImage";
             }
             else
             {
                 cmd.CommandText = "insert into images(Image) values(@byImage)";
             }
           
             cmd.CommandType = CommandType.Text;
             cmd.Parameters.Add("@byImage", MySqlDbType.MediumBlob);
             cmd.Parameters[0].Value = byImage;
             cmd.Connection = conn;
          
             int affectedRows = 0;
             try
             {
                 affectedRows = cmd.ExecuteNonQuery();
             }
             catch
             {
                 affectedRows = -1;
             }
             //关闭连接等
             cmd.Dispose();
             conn.Close();
             conn.Dispose();
             if (affectedRows <= 0)
             {
                 return false;
             }
             else
             {
                 return true;
             }
         }

This is the operation code to insert the picture into the database. The path is the storage you need. The path where the picture is located (including the name and suffix of the picture). In addition, I use the ADO.NET connection method and the language is C#. I don’t need to explain other codes...

The following is the code to read the image in MySQL

 public void MapSearchQuery(out byte[] imageByteResulet)
         {
             imageByteResulet = null;
             MySqlConnection conn = new MySqlConnection();
             conn.ConnectionString = "Server=localhost;Uid=root;Password=123456;Database=firefighting;charset=gb2312";
             try
             {
                 conn.Open();
             }
             catch
             {
                 conn.Close();
                 conn.Dispose();
                 throw new ArgumentException("地图检索数据库连接失败");
             }
             string strQueryCmd = "select Image from images limit 1";
             MySqlCommand cmd = new MySqlCommand(strQueryCmd, conn);
             MySqlDataReader dataReader = null;
             try
             {
                 dataReader = cmd.ExecuteReader();
             }
             catch
             {
                 dataReader.Dispose();
                 cmd.Dispose();
                 conn.Close();
                 conn.Dispose();
                 throw new ArgumentException("地图检索查询地图失败");
             }
             if (dataReader.Read())
             {
                 imageByteResulet = new byte[dataReader.GetBytes(0, 0, null, 0, int.MaxValue)];
                 dataReader.GetBytes(0, 0, imageByteResulet, 0, imageByteResulet.Length);
                 //将图片字节数组加载入到缓冲流
                 // MemoryStream imageStream = new MemoryStream(imageByte);
                 //从缓冲流生成图片
                 //imageResulet = Image.FromStream(imageStream, true);
             }
             dataReader.Dispose();
             cmd.Dispose();
             conn.Close();
             conn.Dispose();
         }

Of course, I take care that the Image object cannot be Through WebService transmission, BLOB data is only converted into byte[] for transmission. If you do not need this function, you can directly kick out the relevant code and convert byte[] into a picture object. Two methods are provided

The first one: imageByte is the byte[] data obtained by calling the above function

//将图片字节数组加载入到缓冲流  
MemoryStream imageStream = new MemoryStream(imageByte);
                //从缓冲流生成图片                
                imageResulet = Image.FromStream(imageStream, true);
                //pictureBox是一个显示图片或者视频的C#控件
                pictureBox.Image = imageResulet;

The image is read and displayed

Second type: BitMap is

Bitmap bm = new Bitmap(new MemoryStream(
imageByte
));
            
    
 pictureBox1.Image = bm;

in the System.Drawingm namespace Well, I'm done here. Of course, you don't have to save the image in the database. You can make a URL mapping and return the file stream (I haven't tried this yet. I will share my experience with everyone after I have time).

The above is the detailed content of How to store and read pictures in mysql. For more information, please follow other related articles on the PHP Chinese website!