如何在MySQL中向单个列添加多个值？附result表创建语句

嘿，我来帮你理清这个问题！你提到的“向一个列添加多个值”，通常对应两种常见的使用场景，我结合你的result表结构分别说明：

场景1：给同一行的单个列存储多个值 #

如果想在某一行的某个奖项列（比如first_prize）里存放多个获奖编号/值，有几种实现方式，推荐度从低到高：

• 使用分隔符拼接（不推荐）
  可以用逗号、竖线等符号把多个值拼接成字符串存入列中，比如：

  INSERT INTO result (id, l_id, lname, first_prize) 
  VALUES (1, 'L001', '高中数学竞赛', '1001,1002');
  

  查询时可以用FIND_IN_SET或SUBSTRING_INDEX提取单个值，但这种方式会导致查询效率低、难以维护，也不符合数据库规范化设计，只适合临时场景。

• 使用JSON类型（推荐中小规模场景）
  如果你使用的是MySQL 5.7及以上版本，可以把目标列修改为JSON类型，这样就能直接存储数组格式的多个值：
  首先修改列类型：

  ALTER TABLE result MODIFY COLUMN first_prize JSON;
  

  然后插入多个值：

  INSERT INTO result (id, l_id, lname, first_prize) 
  VALUES (1, 'L001', '高中数学竞赛', '[1001,1002]');
  

  查询时可以用JSON函数（比如JSON_EXTRACT）获取单个值，比分隔符方式更规范灵活。

• 新建关联表（推荐规范设计）
  最符合数据库设计范式的方式是拆分出一个关联表，专门存储奖项和获奖值的对应关系。比如新建prize_records表：

  CREATE TABLE prize_records (
      id INT(11) AUTO_INCREMENT PRIMARY KEY,
      result_id INT(11) NOT NULL,
      prize_type VARCHAR(20) NOT NULL, -- 比如'first_prize'、'second_prize'
      prize_value VARCHAR(9) NOT NULL,
      FOREIGN KEY (result_id) REFERENCES result(id)
  );
  

  然后插入多个获奖值时，每个值存一行：

  INSERT INTO prize_records (result_id, prize_type, prize_value)
  VALUES (1, 'first_prize', '1001'), (1, 'first_prize', '1002');
  

  这种方式便于后续的查询、更新和统计，完全避免了单列存多值的弊端。

场景2：批量给多行的同一列插入/更新值 #

如果是想一次性给多行的某个列设置值（比如给多个竞赛记录的first_prize赋值）：

• 批量插入多行数据
  使用INSERT的多值语法，一次插入多条记录：

  INSERT INTO result (id, l_id, lname, first_prize) 
  VALUES 
      (1, 'L001', '高中数学竞赛', '1001'),
      (2, 'L002', '高中物理竞赛', '1003'),
      (3, 'L003', '高中化学竞赛', '1005');
  

• 批量更新已有行的列值
  如果是给已存在的行批量更新，可以结合CASE语句或WHERE条件：

  • 针对不同行设置不同值：

    UPDATE result 
    SET first_prize = CASE id 
        WHEN 1 THEN '1001,1002' 
        WHEN 2 THEN '1003' 
        ELSE first_prize 
    END 
    WHERE id IN (1,2);
    

  • 给符合条件的所有行设置统一值：

    UPDATE result 
    SET first_prize = '1000' 
    WHERE l_id LIKE 'L00%';
    

内容的提问来源于stack exchange，提问作者Jacob V T