解密MySQL二进制类型：BINARY与VARBINARY的终极指南

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;

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;

-- 创建文件签名表
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';

-- 防止二进制数据被静默截断
SET sql_mode = 'STRICT_TRANS_TABLES';

-- 对长二进制数据只索引前20字节
ALTER TABLE documents ADD INDEX (file_hash(20));