在数据库中使用 ID
在数据库中创建新表时，使用id的重要性是什么？出于我们的目的，我们在每个字段中使用唯一的用户名和电子邮件来将信息与该唯一的用户名或电子邮件进行匹配。那么id有什么用呢？
另外，长度/值字段的作用是什么？这是新的。
非常感谢！
When creating a new table in a database, what is the importance of using id. For our purposes, we are using unique username and email in each field to match the info with that unique username or email. So what is the use of id?
Also, what is the length/value field for? New to this.
thanks a bunch!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
id字段是代理键的示例。使用代理键作为数据库中的主键是一个好主意，因为它与现实世界中的外部事件完全无关，因此不受其影响。使用自然键（例如电子邮件地址）可能会导致问题，因为如果用户更改其电子邮件地址，您的密钥必须改变。这可能会造成困难，因为它会破坏外键约束。随着时间的推移，它还会使查询与特定用户相关的事件变得更加困难，因为您无法保证与该用户的整个历史记录保持一致的单个密钥。
如果您的公司中有多个数据库需要密钥，或者您将数据从数据库导出到其他应用程序或系统，那么当您更改数据库中的密钥时，您可能还需要更改这些系统中的密钥，例如这不能通过使用 ON CASCADE UPDATE 自动完成。
The
idfield is an example of a surrogate key. It is a good idea to use a surrogate key as a primary key in a database because it is totally unrelated to and therefore unaffected by external events in the real world.Using a natural key such as the email address could cause problems because if a user changes their email address your key will have to change. This can create difficulties as it will break foreign key contraints. It will also make querying for events relating to a specific user over time more difficult as you have no guaranteed single key that is consistent for that user's entire history.
If you have more than one database in your company that needs the keys, or you export data from your database to other applications or systems then when you change a key in your database you may also need to change the keys in those systems too, something which cannot be done automatically by using ON CASCADE UPDATE.
正如其他人指出的那样，记录有两种类型的密钥：自然密钥和代理（人工）密钥。那么，两个主要问题是：您是否需要使用代理键，如果需要，代理键应该是什么？
关于第一个问题：您仅需要使用代理密钥如果您没有有效的自然密钥用作主键在表格上。所有理智的数据库系统都支持“ON UPDATE CASCADE”子句，这意味着如果您使用碰巧发生更改的自然键，更改将传播到声明为引用它的所有内容。当然，如果您的数据库系统不支持外键，那么您最好的选择是使用代理键，如果只是为了解决数据库系统中功能的缺乏（并且代理键将使您的数据库更容易根据这一事实进行一致性检查）。也就是说，如果您正在设计一个需要高正常运行时间和高稳健性的应用程序，请选择一个能够正确获取外键的数据库实现，否则您很可能会发现在开发后期（甚至在维护过程中）会发现数据完整性错误）并且您将必须编写实用程序来检查数据在各种故障模式下的一致性。
对于第二个问题：如果您使用代理键，特别是当您正在解决数据库系统的缺陷时，您应该始终将其视为不可变并且全球独一无二。 始终。这将在以后的许多情况下有所帮助：公司可以合并（和拆分），数据库可以合并（和拆分），并且可能会发生大约一百万种其他情况，这些情况是在如果代理键不是全局唯一的，数据库的设计可能会导致问题。由于代理键与它们所保存的数据完全无关（除了您赋予它的人工字段之外，它们与表中的其他字段没有关系），因此最好是这种方式。由于这些原因，当我必须使用代理键时，我会使用 UUID（本质上是一个 128 位整数，但不是增量整数）。现在，当发生意外事件时，您不必担心对记录编号和引用进行重新编号。 （是的，它确实会减慢速度，特别是如果您的服务器运行在 32 位平台上。但是，如果您需要处理更多负载，请更好地分配负载 — 不要为了速度而牺牲完整性，永远< /em>，当您处理重要数据时！）
As others have pointed out, there are two types of keys for records: natural keys and surrogate (artificial) keys. The two major questions, then, are: do you need to use a surrogate key, and if so, what should that surrogate key be?
As to the first question: You only need to use a surrogate key if you have no valid natural key for use as a primary key on the table. All sane database systems support the 'ON UPDATE CASCADE' clause, which means that if you are using a natural key which happens to change, the change will be propagated to everything which is declared to reference it. Of course, if your database system does not support foreign keys, then your best bet is to use a surrogate key, if only to work around the lack of functionality in the database system (and surrogate keys will make your database easier to consistency check in light of that fact). That said, if you are designing an application that has requirements for high uptime and high robustness, select a database implementation that gets foreign keys correct, or you will most likely find that data integrity bugs will be found late in development (or even in maintenance) and you will have to write utilities that will check your data for consistency in various modes of failure.
For the second question: If you use a surrogate key, especially if you are working around a deficiency of a database system, you should always treat it as if it were immutable and globally unique. ALWAYS. This will aid in many situations later on: companies can merge (and split), databases can be merged (and split), and about a million other situations can happen that aren't anticipated when the database is designed that are capable of causing problems if the surrogate keys are not globally unique. Since surrogate keys are not at all related to the data they hold (they have no relation to the other fields in the table other than the artificial one that you have bestowed upon it) it's just best that way. For these reasons, when I must use a surrogate key, I use a UUID (which is essentially a 128-bit integer, but not incremental). Now you don't have to worry about renumbering record numbers and references when unexpected events occur. (Yes, it does slow things down, particularly if your server is running on a 32-bit platform. But if you need to handle more load, distribute the load better---do not sacrifice integrity for speed, ever, when you're working with important data!)
表之间的关系。
与
用户名或电子邮件地址相关无效，因为这是一个字符串并比较此值需要更多的时间，并且索引更大，最佳解决方案是添加ID，就像主键一样，用于与其他表的关系作为userid。Relations between tables.
Is uneffective have relation to
usernameoremailaddress because this is astringand comparing this values takes much more time, and indexes are bigger, optimal solution is addIDlike a primary key for relations to other tables asuserid.尽管您希望用户名是唯一的，但您不应依赖数据库来控制唯一性。您的代码的最佳实践是测试用户名和电子邮件是否已存在于数据库中。
长度的目的是为了限制数据的输入。例如，长度为 10 的 varchar 只允许输入 10 个字符长度。该值用于默认目的。如果您插入新行而不声明此字段，它将自动填充该值（如果已设置）。
Although you want usernames to be unique you should not rely on your database to control uniqueness. It is best practice for your code to test if the username and e-mail already exists in the database.
The purpose of length is for limiting the input of data. For instance varchar with a length of 10 will only allow a 10 character length input. Value is for default purposes. If you insert a new row without declaring this field it will automatically be filled with the value, if set.
重点是指数的减仓。如果您不使用身份字段并选择使用（用户名、电子邮件）作为主键，则搜索用户将花费更多时间首先查找用户名，然后查找电子邮件。此外，这些字段是字符串，您无法比较整数的大小和用户名和电子邮件等字符串的大小。
使用身份字段将允许您执行诸如
评论（id，user_id）之类的操作，而不是评论（id，用户名，电子邮件）...The point is to lighten the index. If you don't use an identity field and you choose to use (username, email) as primary key, the search for a user will take some more time looking for the username firt and then the email. Furthermore, these fields are strings, you can't compare the size of an integer and the size of strings like usernames and emails.
And using an identity field will allow you to do things like
Comments (id, user_id)and notComments (id, username, email)...