SQL数组类型处理方法_SQL多值字段解析技巧

SQL无原生数组类型，主流数据库通过扩展支持：PostgreSQL用ARRAY类型与unnest()展开，MySQL用JSON类型与JSON_CONTAINS等函数解析，通用场景应优先关系建模而非多值字段。

SQL本身没有原生数组类型，但主流数据库提供了类似数组或集合的扩展支持。处理“多值字段”时，核心思路是避免用字符串拼接存储（如"1,2,3"），优先使用规范的关系建模；若必须处理数组类字段，则需根据数据库类型选择对应解析方式。

PostgreSQL：原生数组支持与展开操作

PostgreSQL 支持 INTEGER[]、TEXT[] 等数组类型，可直接定义、查询和展开。

• 建表示例：CREATE TABLE tags (id SERIAL, name TEXT, categories TEXT[]);
• 插入数组：INSERT INTO tags VALUES (1, 'book', ARRAY['tech', 'python', 'database']);
• 判断是否包含某值：WHERE 'python' = ANY(categories)
• 展开为行（类似“反规范化”）：SELECT id, name, unnest(categories) AS cat FROM tags;
• 去重并聚合回数组：SELECT id, array_agg(DISTINCT cat) FROM (...) GROUP BY id;

MySQL：用JSON类型模拟数组 + JSON函数解析

MySQL 5.7+ 支持 JSON 类型，适合存储结构化多值数据，比逗号分隔更安全可靠。

• 建表：CREATE TABLE items (id INT, props JSON);
• 插入：INSERT INTO items VALUES (1, '["red", "large", "in_stock"]');
• 提取元素：props->"$[0]" 获取第一个值，props->>"$[0]" 去引号
• 判断是否包含：JSON_CONTAINS(props, '"large"')
• 遍历所有元素（需配合 JSON_TABLE，MySQL 8.0+）：
  SELECT jt.val FROM items, JSON_TABLE(props, '$[*]' COLUMNS (val TEXT PATH '$')) AS jt WHERE id = 1;

通用技巧：字符串分割（慎用，仅限遗留系统）

当字段是逗号分隔字符串（如 "a,b,c"）且无法改造结构时，可用数据库内置函数临时拆解，但性能差、易出错，不推荐新项目使用。

• MySQL：用 SUBSTRING_INDEX 分段取值，或借助递归CTE（8.0+）生成数字序列再切分
• PostgreSQL：用 string_to_array() 转数组，再结合 unnest()
• SQL Server：用 STRING_SPLIT()（2016+），返回表结果集
• 注意：空值、空字符串、多余空格、转义字符都会导致解析失败，务必先清洗数据

设计建议：优先关系化，而非存储多值

真正健壮的方案不是“怎么解析数组”，而是“为什么需要数组”。绝大多数场景应拆分为关联表。

• 例如：用户-标签关系，建 user_tags(user_id, tag_id) 中间表，而非在 users 表里加 tags VARCHAR
• 好处：支持索引、外键约束、事务一致性、高效查询（如“查找同时有A和B标签的用户”）
• 只有极少数场景适合数组/JSON：配置项、日志属性、前端动态字段等读多写少、结构松散的数据

基本上就这些。选对类型、用对函数、想清模型，多值问题就不复杂但容易忽略设计本质。