SQL中32位十六进制字符串与BINARY(16)互转及存储优化问询

Great question—this is a smart optimization for cutting storage overhead and boosting query performance with hex string identifiers. Most modern SQL databases have built-in tools to handle this conversion seamlessly, so you won’t need to roll your own chunked logic (though we’ll cover that as a fallback too). Let’s walk through the solutions:

1. Use Built-in Database Functions (Recommended) #

Nearly all major databases provide native functions to convert hex strings to binary and vice versa. This is the fastest, most reliable approach, and you can make it completely transparent to your client scripts with computed columns.

MySQL/MariaDB #

• Convert hex string to binary: UNHEX()
• Convert binary back to hex string: HEX()

Example Usage:

-- Insert data (convert hex string to Binary(16) on the fly)
INSERT INTO your_table (UserKey_binary, data0, data1)
VALUES (UNHEX('000000003abc4f6e000000003abc4f6e'), 'sample_data0', 'sample_data1');

-- Query data (convert binary back to hex string for client)
SELECT HEX(UserKey_binary) AS UserKey, data0, data1
FROM your_table
WHERE UserKey_binary = UNHEX('000000003abc4f6e000000003abc4f6e');

Make it client-transparent with a stored generated column:

ALTER TABLE your_table
ADD COLUMN UserKey CHAR(32) GENERATED ALWAYS AS (HEX(UserKey_binary)) STORED;

Now your client can read/write to the UserKey string column as before—MySQL automatically handles converting to/from UserKey_binary behind the scenes.

PostgreSQL #

• Convert hex string to binary: decode(hex_str, 'hex')
• Convert binary back to hex string: encode(bin_val, 'hex')

Example Usage:

-- Insert data
INSERT INTO your_table (UserKey_binary, data0, data1)
VALUES (decode('000000003abc4f6e000000003abc4f6e', 'hex'), 'sample_data0', 'sample_data1');

-- Query data
SELECT encode(UserKey_binary, 'hex') AS UserKey, data0, data1
FROM your_table
WHERE UserKey_binary = decode('000000003abc4f6e000000003abc4f6e', 'hex');

Client-transparent generated column:

ALTER TABLE your_table
ADD COLUMN UserKey TEXT GENERATED ALWAYS AS (encode(UserKey_binary, 'hex')) STORED;

SQL Server #

• Convert hex string to binary: CONVERT(VARBINARY(16), hex_str, 2) (the 2 specifies the input is a hex string without a 0x prefix)
• Convert binary back to hex string: CONVERT(CHAR(32), bin_val, 2)

Example Usage:

-- Insert data
INSERT INTO your_table (UserKey_binary, data0, data1)
VALUES (CONVERT(VARBINARY(16), '000000003abc4f6e000000003abc4f6e', 2), 'sample_data0', 'sample_data1');

-- Query data
SELECT CONVERT(CHAR(32), UserKey_binary, 2) AS UserKey, data0, data1
FROM your_table
WHERE UserKey_binary = CONVERT(VARBINARY(16), '000000003abc4f6e000000003abc4f6e', 2);

Client-transparent persisted computed column:

ALTER TABLE your_table
ADD UserKey AS CONVERT(CHAR(32), UserKey_binary, 2) PERSISTED;

2. Fallback: Custom Chunked Conversion (For Databases Without Built-ins) #

If you’re working with a legacy or niche database that lacks native hex-to-binary functions, you can split the 32-character string into smaller 8-character chunks (each equal to 4 bytes), convert each chunk to binary, then concatenate the results.

Here’s an example custom function for MySQL (though this shouldn’t be necessary for most modern databases):

DELIMITER //
CREATE FUNCTION hex_to_binary16(hex_str CHAR(32)) RETURNS BINARY(16)
DETERMINISTIC
BEGIN
  RETURN CONCAT(
    UNHEX(SUBSTRING(hex_str, 1, 8)),
    UNHEX(SUBSTRING(hex_str, 9, 8)),
    UNHEX(SUBSTRING(hex_str, 17, 8)),
    UNHEX(SUBSTRING(hex_str, 25, 8))
  );
END //
DELIMITER ;

-- Reverse function
DELIMITER //
CREATE FUNCTION binary16_to_hex(bin_val BINARY(16)) RETURNS CHAR(32)
DETERMINISTIC
BEGIN
  RETURN CONCAT(
    HEX(SUBSTRING(bin_val, 1, 4)),
    HEX(SUBSTRING(bin_val, 5, 4)),
    HEX(SUBSTRING(bin_val, 9, 4)),
    HEX(SUBSTRING(bin_val, 13, 4))
  );
END //
DELIMITER ;

3. Performance & Indexing Best Practices #

• Set UserKey_binary as your primary key or unique index: Binary types are just as indexable as strings, and the smaller size (16 bytes vs. 32 bytes for CHAR(32)) means your index will take up half the space. This reduces disk I/O and allows more index entries to fit in memory, speeding up lookups dramatically.
• Stick with generated columns for client transparency: As shown earlier, this lets your client code keep using the original hex string format without any changes—all conversion happens server-side, so you don’t have to modify existing scripts.

内容的提问来源于stack exchange，提问作者mzoll