sql-server - 一对多存储表 SQL Server

Question

我不是在问这个问题中的数据类型，因为我知道 IP 地址对于 IPv4 应该存储为 BINARY(4)——这只是一个合乎逻辑的例子。也就是说，我没有将 IP 地址直接存储在日志表中，而是将所有唯一的 IP 地址(在添加时)存储到 StoreIPAddress 表中。然后我通过加入 ID 字段在需要的地方进行关联。

例子:

Table StoreIPAddress(ID INT Identity(1,1), IPAddress VARCHAR(15))
Table LogSessions(SessionID VARCHAR(255), IPAddressID)

所以现在，我不必将 ip 地址存储 500 亿次。我只存储一次，然后通过 ID 字段加入。我对数据库中许多重复出现的值执行此操作。

然后我使用存储过程获取 ID(如果存在)，如果不存在，则添加 ip 地址，并返回新 ID。这样……

CREATE PROCEDURE [dbo].[usp_StoreIPAddress] 
    @IPADDRESS VARCHAR(15), @ID INT OUTPUT
AS
    SET @ID = (SELECT TOP 1 ID FROM dbo.StoreIPAddress WHERE IPAddress = @IPADDRESS);

    IF (ISNULL(@ID, 0) = 0)
    BEGIN
        INSERT INTO dbo.StoreIPAddress(IPAddress) 
        VALUES(@IPADDRESS);

        SELECT @ID = SCOPE_IDENTITY();
    END
END
RETURN

如果我在单个实例中使用它，将 session 添加到日志等，这会起作用。但是如果我运行脚本来添加 100 个 ip 地址怎么办？如果我可以像存储过程一样编写函数，那么函数将完美运行。但是函数不能向表中添加数据。

这将是理想的:

UPDATE LogSessions
SET IPAddressID = dbo.udf_StoreIPAddress(IPAddress)

问题是，由于函数不允许插入语句，所以这不会起作用。还有另一种解决方法吗？我可以编写光标和创可贴，但我想找出一种安全有效的方法，并牢记最佳实践。

Answer 1

您可以在 View 上使用带有 INSTEAD OF INSERT TRIGGER 的 VIEW 和 INSERT INTO the View:

CREATE Table StoreIPAddress(ID INT Identity(1,1), IPAddress VARCHAR(15));

CREATE Table LogSessions(SessionID VARCHAR(255), IPAddressID INT);
GO
CREATE VIEW vwStoreIpAddress
AS
SELECT s.IpAddress, LS.SessionId
FROM StoreIpAddress S
INNER JOIN LogSessions LS
  ON S.ID = LS.IPAddressId

GO
CREATE TRIGGER trgvwStoreIpAddress ON vwStoreIpAddress
INSTEAD OF INSERT
AS

    INSERT INTO StoreIPAddress (IPAddress)
    SELECT I.IPAddress
    FROM INSERTED I
    WHERE NOT EXISTS (SELECT * FROM StoreIPAddress S WHERE S.IPAddress = I.IPAddress)

    INSERT INTO LogSessions (SessionId, IPAddressID)
    SELECT i.SessionID, S.ID
    FROM inserted I
    INNER JOIN StoreIPAddress S
        ON I.IPAddress = S.IPAddress

然后是一个简单的插入语句:

 INSERT INTO vwStoreIpAddress
 SELECT '1.1.1.1', 'session1'
 UNION ALL
 SELECT '1.1.1.1', 'session2'
 UNION ALL
 SELECT '1.1.1.2', 'session1'