正文内容
一、为什么你需要了解二进制类型？
在日常开发中，我们经常需要处理：
- 用户密码哈希值存储
- 文件二进制内容管理
- UUID等特殊标识符
- 加密数据存储
这时候，CHAR/VARCHAR就显得力不从心了！今天我们就来揭秘MySQL专门为二进制数据设计的两种类型——BINARY和VARBINARY。
二、核心区别速查表
| 特性 | BINARY | VARBINARY | CHAR/VARCHAR |
|---|---|---|---|
| 存储内容 | 二进制字节 | 二进制字节 | 文本字符 |
| 长度 | 固定(0-255) | 可变(0-65535) | 根据字符集变化 |
| 比较方式 | 逐字节比较 | 逐字节比较 | 按字符集规则比较 |
| 填充 | 用0x00填充 | 不填充 | 空格填充/不填充 |
三、5个必知实战技巧
1. 密码哈希存储最佳实践
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50),
-- 存储SHA-256哈希（固定64字节）
password_hash BINARY(64) NOT NULL
);
-- 插入用户（假设密码哈希为SHA256('123456')）
INSERT INTO users (username, password_hash)
VALUES (
'admin',
UNHEX('8D969EEF6ECAD3C29A3A629280E686CF0C3F5D5A86AFF3CA12020C923ADC6C92')
);
-- 插入第二个用户（假设密码哈希为SHA256('password')）
INSERT INTO users (username, password_hash)
VALUES (
'user01',
UNHEX('5E884898DA28047151D0E56F8DC6292773603D0D6AABBDD62A11EF721D1542D8')
);
-- 查询所有用户（哈希显示为十六进制）
SELECT
id,
username,
HEX(password_hash) AS password_hash
FROM users;
-- 查询所有用户（哈希显示为十六进制）
SELECT
id,
username,
HEX(password_hash) AS password_hash
FROM users;
2. 高效UUID存储方案
CREATE TABLE orders (
-- 16字节UUID比36字符的UUID字符串节省空间
order_id BINARY(16) PRIMARY KEY,
total DECIMAL(10,2)
);
-- 插入UUID示例
INSERT INTO orders VALUES
(UNHEX(REPLACE(UUID(), '-', '')), 99.99);
-- 查询
SELECT HEX(order_id) AS order_id_hex, total FROM orders;
3. 二进制数据查询技巧
-- 创建文件签名表
CREATE TABLE files (
id INT AUTO_INCREMENT PRIMARY KEY,
file_name VARCHAR(255) NOT NULL,
file_size BIGINT NOT NULL,
file_signature VARBINARY(16) NOT NULL, -- 用于存储文件魔数签名
file_content LONGBLOB,
upload_time DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
-- 创建索引优化查询
ALTER TABLE files ADD INDEX idx_signature (file_signature(8)); -- 只索引前8字节
-- 插入PNG文件（签名头：0x89504E470D0A1A0A）
INSERT INTO files (file_name, file_size, file_signature)
VALUES (
'example.png',
1024,
0x89504E470D0A1A0A
);
-- 插入PDF文件（签名头：0x25504446）
INSERT INTO files (file_name, file_size, file_signature)
VALUES (
'document.pdf',
2048,
0x25504446
);
-- 插入ZIP文件（签名头：0x504B0304）
INSERT INTO files (file_name, file_size, file_signature, file_content)
VALUES (
'archive.zip',
4096,
0x504B0304,
-- 模拟ZIP文件内容（实际使用时替换为真实二进制数据）
LOAD_FILE('/tmp/sample.zip')
);
-- 使用UNHEX函数插入（效果相同）
INSERT INTO files (file_name, file_size, file_signature)
VALUES (
'test.jpg',
512,
UNHEX('FFD8FFE0') -- JPEG文件签名
);
-- 查看所有文件的签名（16进制格式）
SELECT
id,
file_name,
HEX(file_signature) AS signature_hex,
file_size,
upload_time
FROM files;
-- 结果示例：
/*
+----+-------------+------------------+-----------+---------------------+
| id | file_name | signature_hex | file_size | upload_time |
+----+-------------+------------------+-----------+---------------------+
| 1 | example.png | 89504E470D0A1A0A | 1024 | 2023-08-21 09:15:33 |
| 2 | document.pdf| 25504446 | 2048 | 2023-08-21 09:15:45 |
| 3 | archive.zip | 504B0304 | 4096 | 2023-08-21 09:16:02 |
| 4 | test.jpg | FFD8FFE0 | 512 | 2023-08-21 09:17:15 |
+----+-------------+------------------+-----------+---------------------+
*/
-- 查找所有PNG文件（匹配完整签名）
SELECT *
FROM files
WHERE file_signature = 0x89504E470D0A1A0A;
-- 查找PDF或ZIP文件（使用IN子句）
SELECT file_name, file_size
FROM files
WHERE file_signature IN (0x25504446, 0x504B0304);
-- 查找JPEG文件（使用UNHEX函数）
SELECT *
FROM files
WHERE file_signature = UNHEX('FFD8FFE0');
-- 查找所有ZIP开头文件（匹配前4字节）
SELECT *
FROM files
WHERE LEFT(file_signature, 4) = 0x504B0304;
-- 使用SUBSTRING和HEX组合查询
SELECT file_name
FROM files
WHERE HEX(SUBSTRING(file_signature, 1, 4)) = 'FFD8FFE0';
4. 避免踩坑：严格模式设置
-- 防止二进制数据被静默截断
SET sql_mode = 'STRICT_TRANS_TABLES';
5. 性能优化：索引前缀
-- 对长二进制数据只索引前20字节
ALTER TABLE documents ADD INDEX (file_hash(20));
四、常见问题QA
Q：BINARY和BLOB有什么区别？ A：BINARY适合小数据（≤64KB），支持默认值；BLOB适合大文件，8.0+才支持默认值
Q：为什么我的二进制比较结果不符合预期？ A：二进制比较是区分大小写的！'A'（0x41）≠ 'a'（0x61）
Q：如何选择固定长度还是可变长度？ A：哈希值等固定长度数据用BINARY，其他可变数据用VARBINARY
五、性能对比测试
我们对100万条数据进行了基准测试：
- 插入速度：VARBINARY ≈ BINARY > VARCHAR
- 查询速度：BINARY > VARBINARY ≈ VARCHAR
- 存储空间：VARBINARY最省空间（无填充）
六、扩展阅读
- [MySQL 8.0官方文档-Binary Types]dev.mysql.com/doc/refman/…
- [UUID存储优化方案]www.percona.com/blog/store-…
- [MySQL严格模式详解]dev.mysql.com/doc/refman/…
