TheBINARYandVARBINARYdatatypes_MySQL

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了TheBINARYandVARBINARYdatatypes_MySQL，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4434字，纯文字阅读大概需要7分钟。

内容图文

Who supports VARBINARY

There's an SQL:2008 standard optional feature T021 "BINARY and VARBINARY data types". Our book has a bigger description, but here is one that is more up to date:

Standard Conformance

Provided that sql_mode=strict_all_tables, MySQL does the right (standard) thing most of the time, with two slight exceptions and one exception that isn't there.

The first exception:

CREATE TABLE t1 (s1 VARBINARY(2));INSERT INTO t1 VALUES (X'010200');

... This causes an error. MySQL is non-conformant. If one tries to store three bytes into a two-byte target, but the third byte is X'00', there should be no error.

The second exception:

SET sql_mode='traditional,pipes_as_concat';CREATE TABLE t2 (s1 VARBINARY(50000));CREATE TABLE t3 AS SELECT s1 || s1 FROM t2;

... This does not cause an error. MySQL is non-conformant. If a concatenation results in a value that's longer than the maximum length of VARBINARY, which is less than 65535, then there should be an error.

The exception that isn't there:

The MySQL manual makesan odd claimthat, for certain cases when there's a UNIQUE index, "For example, if a table contains 'a', an attempt to store 'a/0' causes a duplicate-key error." Ignore the manual. Attempting to insert 'a/0' will only cause a duplicate-key error if the table's unique-key column contains 'a/0'.

The poemAntigonishdesccribed a similar case:

Yesterday, upon the stair,

I met a man who wasn't there.

He wasn't there again today,

I wish, I wish he'd go away..."

The BINARY trap

Since BINARY columns are fixed-length, there has to be a padding rule. For example, suppose somebody enters zero bytes into a BINARY(2) target:

CREATE TABLE t4 (s1 BINARY(2));INSERT INTO t4 VALUES (X'');SELECT HEX(s1) FROM t4;

... The result is '0000' -- the padding byte for BINARY is X'00' (0x00), not X'20' (space).

There also has to be a rule about what to do for comparisons if comparands end with padding bytes.

CREATE TABLE t5 (s1 VARBINARY(2));INSERT INTO t5 VALUES (X'0102');SELECT * FROM t5 WHERE s1 = X'010200';

... This returns zero rows. It's implementation-defined whether MySQL should

ignore trailing X'00' during comparisons, so there was no chance of getting

it wrong.

The behaviour difference between BINARY and VARBINARY can cause fun:

CREATE TABLE t7 (s1 VARBINARY(2) PRIMARY KEY);CREATE TABLE t8 (s1 BINARY(2), FOREIGN KEY (s1) REFERENCES t7 (s1));INSERT INTO t7 VALUES (0x01);INSERT INTO t8 SELECT s1 FROM t7;

... which fails on a foreign-key constraint error! It looks bizarre

that a value which is coming from the primary-key row can't be put

in the foreign-key row, doesn't it? But the zero-padding rule, combined

with the no-ignore-zero rule, means this is inevitable.

BINARY(x) is a fine data type whenever it's certain that all the valueswill be exactly x bytes long, and otherwise it's a troublemaker.

When to use VARBINARY

VARBINARY is better than TINYBLOB or MEDIUMBLOB because it has a definite

maximum size, and that makes life easier for client programs that want to

know: how wide can the display be? In most DBMSs it's more important that BLOBs can be stored separately from the rest of the row.

VARBINARY is better than VARCHAR if there should be no validity checking.For example, if the default character set is UTF8 then this is illegal:

CREATE TABLE t9 (s1 VARCHAR(5));INSERT INTO t9 VALUES (0xF4808283);

... but this is legal because character set doesn't matter:

CREATE TABLE t10 (s1 VARBINARY(5));INSERT INTO t10 VALUES (0xF4808283);

(I ran into this example on aSQL Server forumwhere the participants display woeful ignorance of Unicode).

And finally converting everything to VARBINARY is one way to avoid the annoying message "Invalid mix of collations". In fact the wikimedia folks appear to havechanged all VARCHARs to VARBINARYsback in 2011 just to avoid that error. I opine that the less drastic solution is to use collations consistently, but I wasn't there.

内容总结

以上是互联网集市为您收集整理的TheBINARYandVARBINARYdatatypes_MySQL全部内容，希望文章能够帮你解决TheBINARYandVARBINARYdatatypes_MySQL所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端