能否将字符串转为数值并保留可读性，以实现不同类型数据块的Union？

Great question—this is a super common headache when you need to combine mixed-type datasets with UNION but want to keep numeric integrity and readability. Here are two practical, database-friendly approaches:

1. Semantic Numeric Mapping with a Lookup Table #

This is the most universal method (works with all SQL databases) and keeps your values both numeric and human-readable via a reference mapping.

The idea is to assign unique, meaningful numeric codes to each string value, then use those codes in your UNION. You can always join back to the mapping table later if you need to display the original string.

Example SQL implementation:

-- Define your string-to-numeric mappings in a CTE or permanent table
WITH name_mappings AS (
    SELECT 'John' AS original_name, 1 AS numeric_code
    UNION ALL
    SELECT 'Jane' AS original_name, 2 AS numeric_code
)
-- Combine your numeric dataset with the mapped string data
SELECT your_numeric_column AS combined_values FROM numeric_dataset
UNION ALL
SELECT numeric_code AS combined_values FROM name_mappings;

If you need to restore readability after the UNION, just join the result back to the mapping table:

SELECT 
    cv.combined_values,
    nm.original_name
FROM combined_result cv
LEFT JOIN name_mappings nm ON cv.combined_values = nm.numeric_code;

2. Use Database Enums (If Supported) #

Many databases (PostgreSQL, MySQL, SQL Server) support enum types, which store string values as underlying integers. This lets you cast the enum to a numeric type for the UNION, while retaining the ability to display the original string.

PostgreSQL example:

-- Create an enum type for your names
CREATE TYPE name_enum AS ENUM ('John', 'Jane');

-- Convert your string column to the enum, then cast to integer for UNION
SELECT CAST(your_string_column::name_enum AS INTEGER) AS numeric_value FROM string_dataset;

-- Perform the UNION
SELECT your_numeric_column AS combined_values FROM numeric_dataset
UNION ALL
SELECT CAST(your_string_column::name_enum AS INTEGER) AS combined_values FROM string_dataset;

To get back the readable string, just cast the integer back to the enum type:

SELECT CAST(combined_values AS name_enum) AS readable_name FROM combined_result;

Key Notes #

• The lookup table method is more portable across databases, while enums are more concise but database-specific.
• Avoid using arbitrary hashes (like MD5) because they produce unreadable numeric values that don’t map logically to the original strings.

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