主页 / dba / 问题 / 248977
Accepted
JackLock
Asked: 2019-09-18 08:19:09 +0800 CST

在数据库中添加新列的正确方法

  • 772

当我们将新列添加到现有表中时，正确的方法是什么？

例如，我已经有像 Foo1、Foo2、Bar1、Bar2 这样的列。现在我想添加一个名为 Foo3 的新列。

当我想添加具有相似名称的列时，标准方法是什么（如果是这样的话）？

我看到 2 个选择：

  1. 创建具有新结构的临时表，其中新列位于具有相同名称的列旁边，将数据从现有表复制到新表并删除现有表并重命名临时表。有点复杂的过程，但使数据库字段更具可读性。
  2. 最后添加新列。操作更简单。但是，如果最后列名可能无法清楚地理解。

为了获得一些参考，我们正在使用数据库项目来控制数据库更改，并为应用程序开发人员提供更好的 GUI 来进行数据库更改。我们正在使用某种 ORM 与数据库交互，因此没有人使用数据库对象名称查询数据库。

更新： 我在一些现有列上有几个索引。但是名称相似的列（包括我要添加的列）不是任何索引的一部分。

sql-server database-design

2 个回答

  1. Best Answer

    作为曾经尝试像这样将列保持在一起的人，我强烈建议您使用选项＃2。

    将列视觉上保持在一起的有争议的好处（在 SSMS 列列表中/当您SELECT *从表格中时）与选项 #1 的缺点相比绝对相形见绌。

    随着表变得越来越大，执行此“复制和替换”操作将花费越来越长的时间，并使用更多资源（I/O、内存、CPU）。

    此外，您确实需要确保在复制和替换期间不会丢失任何数据，因此使用对并发最不友好的隔离级别 ( SERIALIZABLE) 将是必要的。这意味着修改现有表的访问将被阻止，直到操作完成。因此，您可以将长时间的阻塞添加到缺点列表中。

    SSDT publish 在重新排序这样的列时会自动使用这种代码（来自我关于该主题的博客文章）：

    BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

CREATE TABLE [dbo].[tmp_ms_xx_Post] (
    [Id]           INT          IDENTITY (1, 1) NOT NULL,
    [CommentCount] INT          NULL,
    [PostType]     VARCHAR (10) NOT NULL
);

IF EXISTS (SELECT TOP 1 1 
           FROM   [dbo].[Post])
    BEGIN
        SET IDENTITY_INSERT [dbo].[tmp_ms_xx_Post] ON;
        INSERT INTO [dbo].[tmp_ms_xx_Post] ([Id], [PostType])
        SELECT [Id],
               [PostType]
        FROM   [dbo].[Post];
        SET IDENTITY_INSERT [dbo].[tmp_ms_xx_Post] OFF;
    END

DROP TABLE [dbo].[Post];

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_Post]', N'Post';

COMMIT TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

    最重要的是，如果您手动执行此操作（而不是使用 SSDT 模式比较），那么很容易出错（将数据放入错误的列，复制/粘贴错误等） - 特别是在带有很多列。

    • 5

  2. 最常见的方法是将列添加到表中并接受表中的列不会按功能分组在一起。

    • 首先，根据我们正在谈论的确切内容，拥有一堆类似命名的列可能意味着您需要暂停并考虑重构您的数据模型。如果表 'X' 实际上有列Foo1并且Foo2您正在寻找添加 a Foo3，这可能意味着您需要一个单独的X_Foo表来存储与 X 相关的所有 foo 。如果它们的名称确实不是那么紧密但您有一堆与同一种概念相关的列，并且您的表有足够的列，很难通过扫描列列表来判断，可能值得创建一个 1:1 的子表来将相关属性组合在一起。例如，如果您有一张Orders桌子，那么拥有一张桌子可能是有意义的Order_Fulfillment与履行订单相关的几十个属性和与处理退货相关的几十个属性的表，而不是具有数百列Order_Return的单个表。Orders
    • 假设数据模型本身不需要调整，如果您担心单独的列名不足以告诉人们它包含什么，一种选择是为您的列添加描述，以便人们浏览表格更多语境。
    • 更好的是，如果您在您最喜欢的 ERD 工具中维护一个单独的、最新的数据模型，并使人们更容易浏览该 ERD 而不是在数据库中四处寻找信息，您的 ERD 可以按任何逻辑顺序显示列你想从物理模型中分离出来。
    • 如果您设计您的系统，以便所有对数据的访问都通过视图（即，而不是Orders表，您有一个Orders_base表和一个每个人都使用的命名视图Orders），您可以通过将列添加到物理表的末尾，但让它出现在每个人都使用的视图的中间。这具有使未来数据模型重构或管理数据权限等许多事情变得更容易的副作用。
    • 3

相关问题