MySQL“二进制”vs“char字符集二进制”

时间:2021-12-08 16:51:34

What's the difference between binary(10) vs char(10)character set binary?

二进制(10)与char(10)字符集二进制有什么区别?

And varbinary(10) vs varchar(10)character set binary?

和varbinary(10)vs varchar(10)字符集二进制?

Are they synonymous in all MySQL engines?

它们是所有MySQL引擎中的同义词吗?

Is there any gotcha to watch out for?

有什么需要注意的吗?

1 个解决方案

#1


9  

There isn't a difference.

没有区别。

However, there is a caveat if you're storing a string.

但是,如果您要存储字符串,则需要注意。

If you only want to store a byte array or other binary data such as a stream or file then use the binary type as that is what they are meant for.

如果您只想存储字节数组或其他二进制数据(如流或文件),请使用二进制类型,因为它就是它们的用途。

Quote from the MySQL manual:

从MySQL手册引用:

The use of CHARACTER SET binary in the definition of a CHAR, VARCHAR, or TEXT column causes the column to be treated as a binary data type. For example, the following pairs of definitions are equivalent:

在CHAR,VARCHAR或TEXT列的定义中使用CHARACTER SET二进制导致该列被视为二进制数据类型。例如,以下两对定义是等效的:

CHAR(10) CHARACTER SET binary
BINARY(10)

VARCHAR(10) CHARACTER SET binary
VARBINARY(10)

TEXT CHARACTER SET binary
BLOB

So, technically there is no difference.

所以,技术上没有区别。

However, when storing a string it must be converted from a string to byte values using a character set. The decision is to either do this yourself before the MySQL server or you leave it up to MySQL do to do for you. MySQL will perform with by casting a string to BINARY using the BIN character sets.

但是,在存储字符串时,必须使用字符集将字符串转换为字节值。决定是在MySQL服务器之前自己做这件事,还是让你把它留给MySQL来为你做。 MySQL将通过使用BIN字符集将字符串转换为BINARY来执行。

If you want to store the encoding in another format, lets say you have a business requirement that says you must use 4 bytes per character (MySQL doesn't do this by default) you could then use the "CHARACTER SET BINARY" to a textual column and perform the character set encoding yourself.

如果您想以其他格式存储编码,假设您有一个业务要求,即每个字符必须使用4个字节(默认情况下MySQL不会这样做)您可以使用“CHARACTER SET BINARY”来处理文本列并自己执行字符集编码。

It is also worth reading The BINARY and VARBINARY Types from the MySQL manual as this details important information such as padding.

同样值得阅读MySQL手册中的BINARY和VARBINARY类型,因为它详细说明了填充等重要信息。

Summary: There is no technical difference as one is a synonym to the other. In my opinion it makes logical sense to store binary strings in data types that would normally hold a string using the "CHARACTER SET BINARY" and to store byte arrays / streams etc in BINARY fields that cannot be represented by transforming the data though a character set.

简介:没有技术差异,因为一个是另一个的同义词。在我看来,将二进制字符串存储在通常使用“CHARACTER SET BINARY”保存字符串的数据类型中,并在BINARY字段中存储字节数组/流等,通过字符集转换数据无法表示。

#1


9  

There isn't a difference.

没有区别。

However, there is a caveat if you're storing a string.

但是,如果您要存储字符串,则需要注意。

If you only want to store a byte array or other binary data such as a stream or file then use the binary type as that is what they are meant for.

如果您只想存储字节数组或其他二进制数据(如流或文件),请使用二进制类型,因为它就是它们的用途。

Quote from the MySQL manual:

从MySQL手册引用:

The use of CHARACTER SET binary in the definition of a CHAR, VARCHAR, or TEXT column causes the column to be treated as a binary data type. For example, the following pairs of definitions are equivalent:

在CHAR,VARCHAR或TEXT列的定义中使用CHARACTER SET二进制导致该列被视为二进制数据类型。例如,以下两对定义是等效的:

CHAR(10) CHARACTER SET binary
BINARY(10)

VARCHAR(10) CHARACTER SET binary
VARBINARY(10)

TEXT CHARACTER SET binary
BLOB

So, technically there is no difference.

所以,技术上没有区别。

However, when storing a string it must be converted from a string to byte values using a character set. The decision is to either do this yourself before the MySQL server or you leave it up to MySQL do to do for you. MySQL will perform with by casting a string to BINARY using the BIN character sets.

但是,在存储字符串时,必须使用字符集将字符串转换为字节值。决定是在MySQL服务器之前自己做这件事,还是让你把它留给MySQL来为你做。 MySQL将通过使用BIN字符集将字符串转换为BINARY来执行。

If you want to store the encoding in another format, lets say you have a business requirement that says you must use 4 bytes per character (MySQL doesn't do this by default) you could then use the "CHARACTER SET BINARY" to a textual column and perform the character set encoding yourself.

如果您想以其他格式存储编码,假设您有一个业务要求,即每个字符必须使用4个字节(默认情况下MySQL不会这样做)您可以使用“CHARACTER SET BINARY”来处理文本列并自己执行字符集编码。

It is also worth reading The BINARY and VARBINARY Types from the MySQL manual as this details important information such as padding.

同样值得阅读MySQL手册中的BINARY和VARBINARY类型,因为它详细说明了填充等重要信息。

Summary: There is no technical difference as one is a synonym to the other. In my opinion it makes logical sense to store binary strings in data types that would normally hold a string using the "CHARACTER SET BINARY" and to store byte arrays / streams etc in BINARY fields that cannot be represented by transforming the data though a character set.

简介:没有技术差异,因为一个是另一个的同义词。在我看来,将二进制字符串存储在通常使用“CHARACTER SET BINARY”保存字符串的数据类型中,并在BINARY字段中存储字节数组/流等,通过字符集转换数据无法表示。