多列含不同长度重复组的表转换为1NF的SQL实现方法

1NF的核心要求是所有列的值必须是不可再分的原子值，不能存在逗号分隔的多值组合。针对你的表中courses、hobbies、fav_colors三个多值列，最合理的做法是拆分出主表和多个关联表，既符合1NF规范，又避免数据冗余。以下是主流数据库的具体实现代码：

前提假设 #

假设你的原始表名为user_profiles，结构与示例一致。

一、MySQL 8.0+ 实现 #

1. 创建用户主表（存储唯一用户）

-- 创建用户主表，用自增ID作为主键（避免name重复问题）
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

-- 从原表提取唯一用户插入主表
INSERT INTO users (name)
SELECT DISTINCT name FROM user_profiles;

2. 拆分courses列到关联表

-- 创建用户-课程关联表
CREATE TABLE user_courses (
    user_id INT,
    course VARCHAR(50) NOT NULL,
    PRIMARY KEY (user_id, course), -- 避免同一用户重复记录同一课程
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- 递归拆分逗号分隔的课程数据并插入
WITH RECURSIVE split_courses AS (
    SELECT 
        name,
        SUBSTRING_INDEX(courses, ',', 1) AS course,
        SUBSTRING(courses, LENGTH(SUBSTRING_INDEX(courses, ',', 1)) + 2) AS remaining_courses
    FROM user_profiles
    WHERE courses IS NOT NULL AND courses != ''
    UNION ALL
    SELECT 
        name,
        SUBSTRING_INDEX(remaining_courses, ',', 1) AS course,
        SUBSTRING(remaining_courses, LENGTH(SUBSTRING_INDEX(remaining_courses, ',', 1)) + 2) AS remaining_courses
    FROM split_courses
    WHERE remaining_courses IS NOT NULL AND remaining_courses != ''
)
INSERT INTO user_courses (user_id, course)
SELECT u.user_id, TRIM(sc.course) -- 去除字符串前后空格
FROM split_courses sc
JOIN users u ON sc.name = u.name;

3. 拆分hobbies列到关联表

-- 创建用户-爱好关联表
CREATE TABLE user_hobbies (
    user_id INT,
    hobby VARCHAR(50) NOT NULL,
    PRIMARY KEY (user_id, hobby),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- 递归拆分爱好数据并插入
WITH RECURSIVE split_hobbies AS (
    SELECT 
        name,
        SUBSTRING_INDEX(hobbies, ',', 1) AS hobby,
        SUBSTRING(hobbies, LENGTH(SUBSTRING_INDEX(hobbies, ',', 1)) + 2) AS remaining_hobbies
    FROM user_profiles
    WHERE hobbies IS NOT NULL AND hobbies != ''
    UNION ALL
    SELECT 
        name,
        SUBSTRING_INDEX(remaining_hobbies, ',', 1) AS hobby,
        SUBSTRING(remaining_hobbies, LENGTH(SUBSTRING_INDEX(remaining_hobbies, ',', 1)) + 2) AS remaining_hobbies
    FROM split_hobbies
    WHERE remaining_hobbies IS NOT NULL AND remaining_hobbies != ''
)
INSERT INTO user_hobbies (user_id, hobby)
SELECT u.user_id, TRIM(sh.hobby)
FROM split_hobbies sh
JOIN users u ON sh.name = u.name;

4. 拆分fav_colors列到关联表

-- 创建用户-偏好颜色关联表
CREATE TABLE user_fav_colors (
    user_id INT,
    fav_color VARCHAR(50) NOT NULL,
    PRIMARY KEY (user_id, fav_color),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- 递归拆分颜色数据并插入
WITH RECURSIVE split_colors AS (
    SELECT 
        name,
        SUBSTRING_INDEX(fav_colors, ',', 1) AS fav_color,
        SUBSTRING(fav_colors, LENGTH(SUBSTRING_INDEX(fav_colors, ',', 1)) + 2) AS remaining_colors
    FROM user_profiles
    WHERE fav_colors IS NOT NULL AND fav_colors != ''
    UNION ALL
    SELECT 
        name,
        SUBSTRING_INDEX(remaining_colors, ',', 1) AS fav_color,
        SUBSTRING(remaining_colors, LENGTH(SUBSTRING_INDEX(remaining_colors, ',', 1)) + 2) AS remaining_colors
    FROM split_colors
    WHERE remaining_colors IS NOT NULL AND remaining_colors != ''
)
INSERT INTO user_fav_colors (user_id, fav_color)
SELECT u.user_id, TRIM(sc.fav_color)
FROM split_colors sc
JOIN users u ON sc.name = u.name;

二、PostgreSQL 实现 #

PostgreSQL提供了string_to_array和unnest函数，拆分多值列更简洁：

1. 创建用户主表

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

INSERT INTO users (name)
SELECT DISTINCT name FROM user_profiles;

2. 拆分courses列

CREATE TABLE user_courses (
    user_id INT,
    course VARCHAR(50) NOT NULL,
    PRIMARY KEY (user_id, course),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

INSERT INTO user_courses (user_id, course)
SELECT u.user_id, TRIM(unnest(string_to_array(up.courses, ','))) AS course
FROM user_profiles up
JOIN users u ON up.name = u.name
WHERE up.courses IS NOT NULL AND up.courses != '';

3. 拆分hobbies列

CREATE TABLE user_hobbies (
    user_id INT,
    hobby VARCHAR(50) NOT NULL,
    PRIMARY KEY (user_id, hobby),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

INSERT INTO user_hobbies (user_id, hobby)
SELECT u.user_id, TRIM(unnest(string_to_array(up.hobbies, ','))) AS hobby
FROM user_profiles up
JOIN users u ON up.name = u.name
WHERE up.hobbies IS NOT NULL AND up.hobbies != '';

4. 拆分fav_colors列

CREATE TABLE user_fav_colors (
    user_id INT,
    fav_color VARCHAR(50) NOT NULL,
    PRIMARY KEY (user_id, fav_color),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

INSERT INTO user_fav_colors (user_id, fav_color)
SELECT u.user_id, TRIM(unnest(string_to_array(up.fav_colors, ','))) AS fav_color
FROM user_profiles up
JOIN users u ON up.name = u.name
WHERE up.fav_colors IS NOT NULL AND up.fav_colors != '';

三、SQL Server 实现 #

SQL Server使用STRING_SPLIT函数拆分多值列：

1. 创建用户主表

CREATE TABLE users (
    user_id INT IDENTITY(1,1) PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

INSERT INTO users (name)
SELECT DISTINCT name FROM user_profiles;

2. 拆分courses列

CREATE TABLE user_courses (
    user_id INT,
    course VARCHAR(50) NOT NULL,
    PRIMARY KEY (user_id, course),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

INSERT INTO user_courses (user_id, course)
SELECT u.user_id, TRIM(ss.value) AS course
FROM user_profiles up
JOIN users u ON up.name = u.name
CROSS APPLY STRING_SPLIT(up.courses, ',') ss
WHERE up.courses IS NOT NULL AND up.courses != '';

3. 拆分hobbies列

CREATE TABLE user_hobbies (
    user_id INT,
    hobby VARCHAR(50) NOT NULL,
    PRIMARY KEY (user_id, hobby),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

INSERT INTO user_hobbies (user_id, hobby)
SELECT u.user_id, TRIM(ss.value) AS hobby
FROM user_profiles up
JOIN users u ON up.name = u.name
CROSS APPLY STRING_SPLIT(up.hobbies, ',') ss
WHERE up.hobbies IS NOT NULL AND up.hobbies != '';

4. 拆分fav_colors列

CREATE TABLE user_fav_colors (
    user_id INT,
    fav_color VARCHAR(50) NOT NULL,
    PRIMARY KEY (user_id, fav_color),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

INSERT INTO user_fav_colors (user_id, fav_color)
SELECT u.user_id, TRIM(ss.value) AS fav_color
FROM user_profiles up
JOIN users u ON up.name = u.name
CROSS APPLY STRING_SPLIT(up.fav_colors, ',') ss
WHERE up.fav_colors IS NOT NULL AND up.fav_colors != '';

可选：扁平化1NF表（不推荐） #

如果需要一个完全扁平化的1NF表（会产生大量数据冗余，仅适合临时需求），可以通过关联三个拆分后的表生成：

-- MySQL示例，其他数据库语法类似
CREATE TABLE flattened_1nf AS
SELECT 
    u.name,
    uc.course,
    uh.hobby,
    ufc.fav_color
FROM users u
JOIN user_courses uc ON u.user_id = uc.user_id
JOIN user_hobbies uh ON u.user_id = uh.user_id
JOIN user_fav_colors ufc ON u.user_id = ufc.user_id;

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