将标签存储在 sql server 表中的最佳方法?

2022-01-18 00:00:00 tags sql-server

为记录存储标签的最佳方式是什么?只使用 varchar 字段?选择包含标签 x 的行时怎么办?使用like运算符?

What's the best way to store tags for a record? Just use a varchar field? What about when selecting rows that contains tag x? Use the like operator?

谢谢!

推荐答案

取决于两件事:
1)标签/标签记录的数量
2) 你是否对规范化有宗教观点:-)

Depends on two things:
1) The amount of tags/tagged records
2) Whether or not you have a religious opinion on normalization :-)

除非处理大量数据,否则我建议有一个标签"表,将 varchar 值映射到整数标识符,然后第二个表将标记记录映射到它们的标签 ID.我建议先实现它,然后检查它是否不满足您的性能需求.在这种情况下,为标记的行和标记的实际文本保留一个带有 id 的表,但是在这种情况下,我建议您使用 char 列,因为如果优化器执行全表扫描,它将终止您的查询带有 varchar 列的大表.

Unless dealing with very large volumes of data, I'd suggest having a 'Tags' table mapping varchar values to integer identifiers then second table mapping tagged records to their tag ids. I'd suggest implementing this first, then check if it doesn't meet your performance needs. In that case, keep a single table with a id for the tagged row and the actual text of the tag, but in this I'd suggest you use a char column as it will kill your query if the optimizer does a full table scan against a large table with a varchar column.

相关文章