SQLServer 数组存储方式：SQLServer实现数组保存的方法与技巧

SQLServer | 数组存储方式：SQLServer实现数组保存的方法与技巧

场景引入：
小明最近在开发一个电商系统📦，需要存储用户的"最近浏览商品ID列表"，他犯愁了："SQLServer没有数组类型啊，这堆ID该怎么存？" 别急！今天我们就来揭秘SQLServer中五种实用的"数组存储"方案，总有一款适合你！✨

逗号分隔字符串（CSV）

适用场景：简单列表、查询频次低

CREATE TABLE UserActivities (
    UserID INT PRIMARY KEY,
    ViewedProducts VARCHAR(MAX) -- 存储如 "1,5,12,33"
);

技巧：

• 插入数据使用 STRING_AGG（SQLServer 2017+）

  UPDATE UserActivities 
  SET ViewedProducts = STRING_AGG(ProductID, ',') 
  WHERE UserID = 1001;

• 查询时用 STRING_SPLIT 拆分

  SELECT * FROM Products 
  WHERE ProductID IN (
      SELECT value FROM STRING_SPLIT(
          (SELECT ViewedProducts FROM UserActivities WHERE UserID = 1001), 
          ','
      )
  );

  ⚠️ 缺点：无法强制数据类型，性能较差

JSON格式存储

适用场景：需要结构化存储（SQLServer 2016+）🎯

CREATE TABLE UserProfiles (
    UserID INT PRIMARY KEY,
    FavoriteItems NVARCHAR(MAX) -- 存储如 '[1, {"id":2,"note":"限量款"}, 3]'
    CONSTRAINT [CheckJSON] CHECK (ISJSON(FavoriteItems) = 1)
);

操作示例：

-- 追加元素
UPDATE UserProfiles
SET FavoriteItems = JSON_MODIFY(
    FavoriteItems,
    'append $', 4  -- 追加数字4
)
WHERE UserID = 1001;
-- 查询特定元素
SELECT *
FROM Products
WHERE ProductID IN (
    SELECT [value] 
    FROM OPENJSON((SELECT FavoriteItems FROM UserProfiles WHERE UserID = 1001))
);

🌟 优势：支持复杂数据结构，内置JSON验证

关联表方案（正规化）

适用场景：需要严格关系型操作 🔍

CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    UserName NVARCHAR(50)
);
CREATE TABLE UserTags (
    RecordID INT IDENTITY(1,1),
    UserID INT FOREIGN KEY REFERENCES Users(UserID),
    TagValue NVARCHAR(50),
    SortOrder INT  -- 可选排序字段
);

查询示例：

-- 获取用户的所有标签（保持插入顺序）
SELECT TagValue 
FROM UserTags 
WHERE UserID = 1001 
ORDER BY SortOrder;

💡 专业建议：为(UserID, SortOrder)建立复合索引提升性能

XML数据类型

适用场景：历史系统兼容、需要Schema验证 📜

CREATE TABLE ProductBundles (
    BundleID INT PRIMARY KEY,
    Items XML,
    CONSTRAINT [ValidXML] CHECK (Items.exist('//ItemID') = 1)
);

操作技巧：

-- 插入数据
INSERT INTO ProductBundles VALUES (
    1,
    '<Items><ItemID>101</ItemID><ItemID>205</ItemID></Items>'
);
-- XQuery查询
SELECT 
    BundleID,
    i.value('.', 'INT') AS ItemID
FROM ProductBundles
CROSS APPLY Items.nodes('/Items/ItemID') AS T(i)
WHERE BundleID = 1;

CLR自定义类型

高级玩法：需要完全自定义数组行为 ⚙️

1. 在.NET中创建实现INullable接口的类
2. 编译后通过SQL语句注册程序集：

   CREATE ASSEMBLY ArrayLibrary FROM 'D:\Array.dll';
   CREATE TYPE IntArray EXTERNAL NAME ArrayLibrary.[MyNamespace.IntArray];

3. 建表示例：

   CREATE TABLE MatrixData (
    MatrixID INT PRIMARY KEY,
    ValuesArray IntArray
   );

   🔧 适用场景：数学计算、特殊业务逻辑

性能对比速查表 📊

2025年最新实践建议：
1️⃣ 新项目优先考虑JSON方案（平衡功能与性能）
2️⃣ 历史系统升级可逐步将CSV迁移到JSON
3️⃣ 需要事务处理的严格关系数据坚持用关联表

下次遇到"SQLServer存数组"的需求，不妨试试这些方法吧！🚀 哪个方案最符合你的业务场景呢？