BINARY与VARBINARY

MySQL中的二进制字符串类型主要存储一些二进制数据，比如可以存储图片、音频和视频等二进制数据。

BINARY(M)为固定长度的二进制字符串，M表示最多能存储的字节数，取值范围是0~255个字符。如果未指定(M)，表示只能存储1个字节。例如BINARY(8)，表示最多能存储8个字节，如果字段值不足(M)个字节，将在右边填充’\0’以补齐指定长度。

VARBINARY (M)为可变长度的二进制字符串，M表示最多能存储的字节数，总字节数不能超过行的字节长度限制65535，另外还要考虑额外字节开销，VARBINARY类型的数据除了存储数据本身外，还需要1或2个字节来存储数据的字节数。VARBINARY类型必须指定(M)，否则报错。
image.png

  1. CREATE TABLE test_binary1(
  2. f1 BINARY,
  3. f2 BINARY(3),
  4. #f3 VARBINARY, # varbinary必须指明长度
  5. f4 VARBINARY(10)
  6. );
  8. DESC test_binary1;
  10. INSERT INTO test_binary1(f1,f2)
  11. VALUES('a','abc');
  13. SELECT * FROM test_binary1;
  15. # 溢出，报错！！！
  16. #Data too long for column 'f1' at row 1
  17. INSERT INTO test_binary1(f1)
  18. VALUES('ab');
  20. INSERT INTO test_binary1(f2,f4)
  21. VALUES('ab','ab');
  23. SELECT LENGTH(f2),LENGTH(f4)
  24. FROM test_binary1;

BLOB类型

BLOB是一个二进制大对象，可以容纳可变数量的数据。
MySQL中的BLOB类型包括TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB4种类型，它们可容纳值的最大长度不同。可以存储一个二进制的大对象，比如图片、音频和视频等。
需要注意的是，在实际工作中，往往不会在MySQL数据库中使用BLOB类型存储大对象数据，通常会将图片、音频和视频文件存储到服务器的磁盘上，并将图片、音频和视频的访问路径存储到MySQL中。

image.png

  1. CREATE TABLE test_blob1(
  2. id INT,
  3. img MEDIUMBLOB
  4. );
  6. INSERT INTO test_blob1(id)
  7. VALUES (1001);
  9. SELECT *
  10. FROM test_blob1;

TEXT和BLOB的使用注意事项

  • BLOB和TEXT值也会引起自己的一些问题，特别是执行了大量的删除或更新操作的时候。删除这种值会在数据表中留下很大的”空洞”，以后填入这些”空洞“的记录可能长度不同。为了提高性能，建议定期使用OPTIMIZE TABLE功能对这类表进行碎片整理。
  • 如果需要对大文本字段进行模糊查询，MySQL提供了前缀索引。但是仍然要在不必要的时候避免检索大型的BLOB或TEXT值。例如，SELECT*查询就不是很好的想法，除非你能够确定作为约束条件的WHERE子句只会找到所需要的数据行。否则，你可能毫无目的地在网络上传输大量的值。
  • 把BLOB或TEXT列分离到单独的表中。在某些环境中，如果把这些数据列移动到第二张数据表中，可以让你把原数据表中的数据列转换为固定长度的数据行格式，那么它就是有意义的。这会减少主表中的碎片，使你得到固定长度数据行的性能优势。它还使你在主数据表上运行SELECT*查询的时候不会通过网络传输大量的BLOB或TEXT值。