MySQL“二进制” vs“char character set binary” [英] MySQL "binary" vs "char character set binary"

问题描述

binary（10） vs char（10）character set binary ？

和 varbinary（10） vs varchar（10）字符集二进制 ？

在所有 MySQL引擎中是否同义？

有没有要注意？

解决方案

没有区别。

但是，如果您正在存储一个字符串，则会有一个警告。

如果您只想存储一个字节数组或其他二进制数据（如流或文件），那么使用二进制类型，因为这是它们的意图。 p>

从 MySQL手册：

使用CHARACTER SET二进制定义一个CHAR，VARCHAR，
或TEXT列使列被视为二进制数据类型。
例如，以下对定义是等价的：

  CHAR（10）CHARACTER SET binary 
 BINARY（10）
 
 VARCHAR（10）CHARACTER SET二进制
 VARBINARY（10）
 
 TEXT CHARACTER SET二进制
 BLOB 
  




所以在技术上没有区别。

但是，当存储字符串时，必须使用字符集从字符串转换为字节值。决定要么在MySQL服务器之前自己做，要么离开MySQL，要为你做。 MySQL将通过使用BIN字符集将字符串转换为BINARY来执行。

如果要以其他格式存储编码，可以说您有业务需求说你必须每个字符使用4个字节（MySQL默认情况下不会这样做），然后你可以使用CHARACTER SET BINARY到文本列，然后执行自己编码的字符集。

同样值得阅读二进制类型和变数类型从MySQL手册中了解详细信息，如填充。

摘要：
没有技术差异因为一个是另一个的同义词。在我看来，将二进制字符串存储在通常使用CHARACTER SET BINARY存储字符串的数据类型中，并将字节数组/流等存储在BINARY字段中是无逻辑意义的，这些字段不能通过字符集转换数据来表示。

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

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

Are they synonymous in all MySQL engines?

Is there any gotcha to watch out for?

解决方案

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:

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(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.

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.

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

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.

这篇关于MySQL“二进制” vs“char character set binary”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！