使用 ALTER TABLE 实现自定义字段
我们目前正在考虑为我们的 Web 应用程序实现自定义字段的不同方法。用户应该能够为某些实体定义自定义字段并填写/查看此数据（并可能稍后查询数据）。
我知道有不同的方法来实现自定义字段（例如使用名称/值表或使用更改表等），并且我们目前倾向于使用 ALTER TABLE 来动态地将新用户字段添加到数据库中。
在浏览了其他相关的 SO 主题后，我找不到这个解决方案的任何大缺点。相比之下，能够以快速方式查询数据（例如直接使用 SQL 的 where 语句）对我们来说是一个很大的优势。
您认为以这种方式实现自定义字段有什么缺点吗？我们正在讨论最多 100 个用户同时使用的 Web 应用程序（不是并发请求..），并且可以使用 MySQL 和 MS SQL Server 数据库。
We are currently thinking about different ways to implement custom fields for our web application. Users should be able to define custom fields for certain entities and fill in/view this data (and possibly query the data later on).
I understand that there are different ways to implement custom fields (e.g. using a name/value table or using alter table etc.) and we are currently favoring using ALTER TABLE to dynamically add new user fields to the database.
After browsing through other related SO topics, I couldn't find any big drawbacks of this solution. In contrast, having the option to query the data in fast way (e.g. by directly using SQL's where statement) is a big advantage for us.
Are there any drawbacks you could think of by implementing custom fields this way? We are talking about a web application that is used by up to 100 users at the same time (not concurrent requests..) and can use both MySQL and MS SQL Server databases.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
作为更新，我们决定通过 ALTER TABLE 将新列添加到现有数据库表中以实现自定义字段。经过一些研究和测试，这看起来是大多数数据库引擎的最佳解决方案。包含有关自定义字段的元信息的单独表提供了管理、查询和使用自定义字段所需的信息。
Just as an update, we decided to add new columns via ALTER TABLE to the existing database table to implement custom fields. After some research and tests, this looks like the best solution for most database engines. A separate table with meta information about the custom fields provides the needed information to manage, query and work with the custom fields.
我看到的第一个缺点是您需要向您的应用程序服务授予 ALTER 权限。
这意味着您的安全模型需要仔细注意，因为应用程序不仅能够添加字段，还能够删除和重命名它们以及创建一些表（至少对于 MySQL 而言）。
其次，您如何区分每个用户所需的字段？或者说用户A创建的字段可以被用户B访问吗？
请注意，列的基数也可能显着增加。如果每个用户添加 2 个字段，我们已经讨论了 200 个字段。
就我个人而言，我会使用这两种方法之一或两者的组合：
我会向表中添加一个文本字段，在其中存储一个或多个序列化字典：
缺点是值不容易搜索。
字段表：
值表：
当然，它需要一个连接，实现起来有点复杂，但更通用，如果索引正确，效率相当高。
The first drawback I see is that you need to grant your application service with ALTER rights.
This implies that your security model needs careful attention as the application will be able to not only add fields but to drop and rename them as well and create some tables (at least for MySQL).
Secondly, how would you distinct fields that are required per user? Or can the fields created by user A be accessed by user B?
Note that the cardinality of the columns may also significantly grow. If every user adds 2 fields, we are already talking about 200 fields.
Personally, I would use one of the two approaches or a mix of them:
I would add one text field to the table in which I would store a serialized dictionary or dictionaries:
The drawback is that the values are not easily searchable.
fields table:
values table:
Of course, it requires a join and is a bit more complicated to implement but far more generic and, if indexed properly, quite efficient.
我认为向数据库表添加新的自定义字段没有任何问题。
通过这种方法，可以使用特定/最合适的类型，即需要一个 int 字段？将其定义为 int。而对于名称/值类型表，您会将多种数据类型存储为一种类型（可能是 nvarchar） - 除非您使用不同类型的多个列来完成该名称/值表并填充相应的列，但这有点可怕。
此外，添加新列使查询更容易/无需涉及到新名称/值表的联接。
它可能感觉不那么通用，但我觉得这比拥有一个“一刀切”的名称/值表更好。
从 SQL Server 的角度来看（2005 年起）....
另一种方法是存储创建 1 个 XML 类型的“自定义数据”字段 - 这将是真正通用的，不需要创建字段或需要单独的名称/值表。还有一个好处是，并非所有记录都必须具有相同的自定义数据（即，一个字段是通用的，但它包含的内容不必是通用的）。不是 100% 对性能产生影响，但 XML 数据可以建立索引。
I see nothing wrong with adding new custom fields to the database table.
With this approach, the specific/most appropriate type can be used i.e. need an int field? define it as int. Whereas with a name/value type table, you'd be storing multiple data types as one type (nvarchar probably) - unless you complete that name/value table with multiple columns of different types and populate the appropriate one but that is a bit horrible.
Also, adding new columns makes it easier to query/no need to involve a join to a new name/value table.
It may not feel as generic, but I feel that's better than having a "one-size fits all" name/value table.
From an SQL Server point of view (2005 onwards)....
An alternative, would be to store create 1 "custom data" field of type XML - this would be truly generic and require no field creation or the need for a separate name/value table. Also has the benefit that not all records have to have the same custom data (i.e. the one field is common, but what it contains doesn't have to be). Not 100% on the performance impact but XML data can be indexed.