php - 有人处理过多个中型表上的 ALTER TABLE 语句吗？

Question

我在几个表中做了一些测试，这些表的记录数超过 1M，并且在我的测试机器上运行 ALTER TABLE 语句来添加/删除各种列类型将花费大约 13 秒的时间，所以我想知道这是否是一个好的方法EAV 模型的替代方案？

我之所以对几M条记录进行测试是因为我的具体用例，我认为每个动态创建的表不会有超过5M的记录，但我会有几百个表并且字段会被搜索很多。我也不认为字段会经常改变。

是否有人使用此工作流程向表中动态添加列(与 EAV 模型相反)？如果是这样，比较如何？

谢谢。

Answer 1

对 EAV(又名“可扩展字段”、“自定义字段”等)使用 ALTER TABLE 总是一个坏主意。虽然在大多数情况下，将新的可空列附加到现有表并不是一个过于昂贵的操作，但删除、重新排序和非附加插入列的成本非常昂贵 - 通常需要将所有数据复制到新的目标表并删除原始表(必须在事务中完成，通常使用表级锁定)，也不要忘记删除和重新创建外键约束。

理想情况下，EAV 数据应存储在专用的 EAV 数据库系统中，该系统可以处理稀疏数据并强制执行 EAV 所需的特殊约束(例如，限制给定类型的父实体或其他业务规则允许使用哪些 EAV 属性)。通常，这是通过 NoSQL 或文档存储数据库(例如 MongoDB)来完成的。

但是，如果您唯一的工具是 MySQL 并且无法运行其他类型的服务器，那么您最好采用实际的 EAV 模式来存储数据，如下所示(伪代码 SQL):

CREATE TABLE Attributes (
    AttributeId bigint IDENTITY(1,1) PRIMARY KEY
    Name nvarchar(50),
    Type smallint /* some enum you define */
)

CREATE TABLE AttributeHolders (
    HolderId bigint IDENTITY(1,1) PRIMARY KEY
)

CREATE TABLE AttributeValues (
    HolderId bigint FK AttributeHolders.HolderId,
    AttributeId bigint FK Attributes.AttributeId,

    TextValue nvarchar(100) NULL,
    IntValue  bigint NULL,
    DateValue datetimeoffset NULL
)

然后，您需要从其他实体表向 AttributeHolders.HolderId 添加 FK，并添加约束以防止这些实体的不同实例共享相同的 HolderId .

...这是留给读者的问题。不幸的是，没有简单的跨平台解决方案。尽管一种选择是创建一个 SEQUENCE 对象，该对象充当可以成为 Holders 的各种实体的身份值源。