Home > Database > Mysql Tutorial > body text

What is the difference between clob and blob in mysql

青灯夜游
Release: 2022-06-15 17:30:19
Original
6784 people have browsed it

mysql中clob和blob的区别:1、含义不同,clob指代的是字符大对象,而blob指代的是二进制大对象;2、作用不同,clob在数据库中通常用来存储大量的文本数据,即存储字符数据,而blob用于存储二进制数据或文件,常常为图片或音频。

What is the difference between clob and blob in mysql

本教程操作环境:windows7系统、mysql8版本、Dell G3电脑。

MySQL中的blob和clob的区别

1、含义不同

clob英文全称:Character Large Object(字符大对象)

blob其全称:binary large object(二进制大对象)

估计由英文名就能想到他们的作用,所以我们记东西的时候要联想记忆,不能全靠死记硬背。

2、作用不同

clob在数据库中通常用来存储大量的文本数据,即存储字符数据。

blob用于存储二进制数据或文件,常常为图片或音频。

MySQL中的blob和clob的详解示例

clob

clob用于存储大量的文本数据。大字段的操作常常以流的方式处理。

相关类型如下:

类型 最大大小
TinyText  255字节
Text  65535字节(约65K)
MediumText  16 777 215字节(约16M)
LongText  4 294 967 295 (约4G)

创建person表

CREATE TABLE person (
  name varchar(20),
  address text
);
Copy after login

插入数据

import java.io.File;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
 
public class clob {
	/**
	 * @param args
	 */
	//驱动程序就是之前在classpath中配置的JDBC的驱动程序的JAR 包中
	public static final String DBDRIVER = "com.mysql.jdbc.Driver";
	//连接地址是由各个数据库生产商单独提供的,所以需要单独记住
	public static final String DBURL = "jdbc:mysql://192.168.0.4:3306/myDB";
	//连接数据库的用户名
	public static final String DBUSER = "root";
	//连接数据库的密码
	public static final String DBPASS = "";
	
	public static void main(String[] args) throws Exception {
		Connection con = null; 
		PreparedStatement stmt = null;
		try {
			//1、加载数据库驱动程序
			Class.forName(DBDRIVER); 
			//2、连接数据库
			con = DriverManager.getConnection(DBURL,DBUSER,DBPASS); 
			//3、创建Statement 
			stmt = con.prepareStatement("insert into person(name,address) values(?,?)");			
			stmt.setString(1,"April");
			stmt.setClob(2, new FileReader(new File("D:\\work\\info.txt")));        			
			//4、执行SQL语句
			stmt.executeUpdate();	
		}catch(SQLException e) {
			//5、异常处理
		}
		finally {
			//6、清理资源
			if(con !=null)
			{
				con.close(); 
			}
			if(stmt!=null)
			{
				stmt.close();
			}
		}				
	}
}
Copy after login

写入也可以使用语句

stmt.setClob(2, new BufferedReader(new InputStreamReader(new ByteArrayInputStream("四川省成都市高新区".getBytes()))));
Copy after login

执行结果

What is the difference between clob and blob in mysql

读取数据

stmt = con.prepareStatement("select * from person");
rs = stmt.executeQuery();
while(rs.next())
{
	Clob address = rs.getClob("address"); 
	Reader reader = address.getCharacterStream(); 			
	int temp = 0;
	while((temp = reader.read()) != -1)  
	{
		System.out.print((char)temp);
	}
	reader.close();
}
Copy after login

执行结果

四川省成都市高新区
浙江省杭州市西湖区
Copy after login

blob

blob用于存储二进制数据,常常为图片或音频。

相关类型如下:

类型最大大小
TinyBlob 255字节
Blob 65535字节(约65K)
MediumBlob 16 777 215字节(约16M)
LongBlob 4 294 967 295 (约4G)

创建student表

CREATE TABLE student (
  name varchar(20),
  image blob
);
Copy after login

插入数据

stmt = con.prepareStatement("insert into student(name,image) values(?,?)");			
stmt.setString(1,"April");
stmt.setBlob(2, new FileInputStream("D:\\work\\April.png"));
stmt.executeUpdate();
Copy after login

读取数据

stmt = con.prepareStatement("select * from student");
rs = stmt.executeQuery();
while(rs.next())
{
	Blob image = rs.getBlob("image"); 
	InputStream in = image.getBinaryStream(); 
	OutputStream out = new FileOutputStream("D:\\work\\Harris.png");
	int temp = 0;
	while((temp = in.read()) != -1)  
	{
		out.write(temp);
	}
	in.close();
	out.close();
}
Copy after login

【相关推荐:mysql视频教程

The above is the detailed content of What is the difference between clob and blob in mysql. For more information, please follow other related articles on the PHP Chinese website!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template