通常，是否将字符串（或varchar）字段用作联接字段？ [英] Generally, are string (or varchar) fields used as join fields?

问题描述

我们有两个表。第一个包含名称（varchar）字段。第二个字段包含引用第一个表中名称字段的字段。对于与该名称关联的每一行，将重复第二个表中的此外键。通常不建议使用varchar / string字段作为两个表之间的联接吗？何时可以将字符串字段用作联接字段？

We have two tables. The first contains a name (varchar) field. The second contains a field that references the name field from the first table. This foreign key in the second table will be repeated for every row associated with that name. Is it generally discouraged to use a varchar/string field as a join between two tables? When is the best case where a string field can be used as a join field?

推荐答案

当然可以使用varchar作为关键字段（或仅仅是要加入的内容）。它的主要问题取决于您通常在varchar字段中存储的内容。可变数据。严格来说，不建议更改关键字段。一个人的姓名，电话号码，甚至他们的SSN都可以更改。但是，即使有两个John Smiths，内部ID为3的员工也始终为ID 3。

It's certainly possible to use a varchar as a key field (or simply something to join on). The main problems with it are based on what you normally store in a varchar field; mutable data. Strictly speaking, it's not advisable to have key fields change. A person's name, telephone number, even their SSN can all change. However, the employee with internal ID 3 will always be ID 3, even if there are two John Smiths.

第二，字符串比较取决于一些挑剔的人。细节，例如文化，排序规则，空格翻译等，它们可能会在没有明显原因的情况下中断联接。假设您对要加入的特定字符串使用制表符character。后来，您更改软件以用3个空格替换\t，以减少原始字符串中的字符转义符。现在，您已经破坏了所有需要将带有转义的制表符的字符串与外观相同但组成不同的字符串进行匹配的功能。

Second, string comparison is dependent on a number of nit-picky details, such as culture, collation, whitespace translation, etc. that can break a join for no immediately-apparent reason. Say you use a tabspace character \t for a certain string you're joining on. Later, you change your software to replace \t with 3 spaces to reduce character escapes in your raw strings. You have now broken any functionality requiring a string with escaped tabs to be matched to an identical-looking, but differently-composed, string.

最后，即使给出了两个完全相同的字符串字符串，比较两个整数比比较两个字符串有一点性能上的好处。整数比较实际上是恒定时间。字符串比较最多是线性的，具体取决于字符串的长度。

Lastly, even given two perfectly identical strings, there is a slight performance benefit to comparing two integer numbers than comparing two strings. Integer comparison is effectively constant-time. String comparison is linear at best, based on the length of the string.

这篇关于通常，是否将字符串（或varchar）字段用作联接字段？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！