不,您不需要需要主键来使表在 MySQL 中工作。也就是说,主键允许唯一值从另一个表或使用该表的任何代码中引用表中的行。
不过,您确实需要主键才能使表在 MySQL 中正常工作。索引(主键是其中之一)允许 MySQL 搜索小型、高度优化的表子集,以处理关系和搜索。一般来说,在 WHERE 子句中使用的任何字段或用于将两个表链接在一起的任何字段都应该建立索引。
No you do not need a primary key to make a table work in MySQL. That said, a primary key allows for a unique value to refer to a row in a table from another table, or in any code using the table.
You do need a primary key to make a table work well in MySQL though. Indexes (which the primary key is one of) allow MySQL to search through small, highly optimized subsets of the table to process relationships and searches. In general, any fields that you use in a WHERE clause or use to link two tables together should be indexed.
当您在此类工具中手动编辑结果集或表数据时,该工具会运行 UPDATE 命令。
如果没有唯一键,则可能会插入多条相同的记录,然后无法仅更新其中一条/部分记录。在 SQL 编辑器中,您可以手动编辑其中一条记录,但是当更新命令发送到 mysql 时,它要么失败,要么更新所有相同的记录而不是那一条记录。
Actually, InnoDB uses its own row id as PK for the table in case you didn't create one, so it can use it for indexing. And that has some negative effects on performance.
Do explicitly define a primary key, and make it numeric.
The primary key should be sequential.
The primary key should be as small as possible.
As a side note: some SQL editors and tools may have issues if there is no PK on a table.
When you are manually editing result sets or table data in such a tool, the tool runs an UPDATE command.
In case there is no unique key, several identical records may be inserted, and then there is no way to update only one/some of them. In an SQL editor you can manually edit one of those records, but when the update command is sent to the mysql - it will either fail, or update all identical records instead of that one record.
By default MySQL (InnoDB engine) uses primary key to determine the order in which the data is physically stored in the main data file. If there is no primary key MySQL will automatically add a hidden AUTOINCREMENT column to act as pkey. This might cause performance issues because during inserting the unique autoincrement value acts like a global lock for all inserts. Also primary keys are used to associate all of a table’s indexes with the main data file. So the primary key is replicated in every row of every index.
ID is not a requirement, and neither is primary key, but it is a fundamental concept of the relational database model. You wouldn't usually want to try and use name as a key, for example.
By "ID", I think you mean "identifier" - something that uniquely identifies a row in a database table. They are also called keys, which is the more concise technical term used in database design.
Implementing keys achieves at least two important things: keys prevent redundant and therefore potentially anomalous data from getting into your table; keys ensure that consumers of the data can accurately identify, use and update the information in the table. For these reasons, yes, you ought to create keys in your tables.
发布评论
评论(6)
不,您不需要需要主键来使表在 MySQL 中工作。也就是说,主键允许唯一值从另一个表或使用该表的任何代码中引用表中的行。
不过,您确实需要主键才能使表在 MySQL 中正常工作。索引(主键是其中之一)允许 MySQL 搜索小型、高度优化的表子集,以处理关系和搜索。一般来说,在
WHERE
子句中使用的任何字段或用于将两个表链接在一起的任何字段都应该建立索引。No you do not need a primary key to make a table work in MySQL. That said, a primary key allows for a unique value to refer to a row in a table from another table, or in any code using the table.
You do need a primary key to make a table work well in MySQL though. Indexes (which the primary key is one of) allow MySQL to search through small, highly optimized subsets of the table to process relationships and searches. In general, any fields that you use in a
WHERE
clause or use to link two tables together should be indexed.实际上,InnoDB 使用自己的行 ID 作为表的 PK(如果您没有创建表),因此它可以使用它来建立索引。这会对性能产生一些负面影响。
在这里看到一个很好的解释:http://blog.johnjosephbachir.org/2006/10/22/everything-you-need-to-know-about-designing-mysql-innodb-primary-keys/
至总结起来,有 3 个规则:
附带说明:如果表上没有 PK,某些 SQL 编辑器和工具可能会出现问题。
当您在此类工具中手动编辑结果集或表数据时,该工具会运行
UPDATE
命令。如果没有唯一键,则可能会插入多条相同的记录,然后无法仅更新其中一条/部分记录。在 SQL 编辑器中,您可以手动编辑其中一条记录,但是当更新命令发送到 mysql 时,它要么失败,要么更新所有相同的记录而不是那一条记录。
Actually, InnoDB uses its own row id as PK for the table in case you didn't create one, so it can use it for indexing. And that has some negative effects on performance.
See a very good explanation here: http://blog.johnjosephbachir.org/2006/10/22/everything-you-need-to-know-about-designing-mysql-innodb-primary-keys/
To sum it up, there are 3 rules:
As a side note: some SQL editors and tools may have issues if there is no PK on a table.
When you are manually editing result sets or table data in such a tool, the tool runs an
UPDATE
command.In case there is no unique key, several identical records may be inserted, and then there is no way to update only one/some of them. In an SQL editor you can manually edit one of those records, but when the update command is sent to the mysql - it will either fail, or update all identical records instead of that one record.
默认情况下,MySQL(InnoDB引擎)使用主键来确定数据在主数据文件中物理存储的顺序。如果没有主键,MySQL会自动添加一个隐藏的AUTOINCREMENT列来充当pkey。这可能会导致性能问题,因为在插入期间,唯一的自动增量值的作用类似于所有插入的全局锁。主键还用于将表的所有索引与主数据文件相关联。因此,主键会复制到每个索引的每一行中。
By default MySQL (InnoDB engine) uses primary key to determine the order in which the data is physically stored in the main data file. If there is no primary key MySQL will automatically add a hidden AUTOINCREMENT column to act as pkey. This might cause performance issues because during inserting the unique autoincrement value acts like a global lock for all inserts. Also primary keys are used to associate all of a table’s indexes with the main data file. So the primary key is replicated in every row of every index.
这不是一个要求,但如果您将来可能引用此表中的任何数据,那么将其作为主键是一个非常好的主意。
Its not a REQUIREMENT, but if you may ever in the future reference any data in this table, its a very good idea to have one as a primary key.
ID 不是必需的,也不是主键,但它是关系数据库模型的基本概念。例如,您通常不想尝试使用名称作为键。
还发现:SQL 表中的 ID 字段:规则还是法律?
ID is not a requirement, and neither is primary key, but it is a fundamental concept of the relational database model. You wouldn't usually want to try and use name as a key, for example.
also found : ID fields in SQL tables: rule or law?
我认为“ID”指的是“标识符”——唯一标识数据库表中的一行的东西。它们也称为键,这是数据库设计中使用的更简洁的技术术语。
实现键至少可以实现两件重要的事情:键可以防止冗余数据(因此可能存在异常数据)进入您的表;键确保数据的使用者能够准确地识别、使用和更新表中的信息。出于这些原因,是的,您应该在表中创建键。
By "ID", I think you mean "identifier" - something that uniquely identifies a row in a database table. They are also called keys, which is the more concise technical term used in database design.
Implementing keys achieves at least two important things: keys prevent redundant and therefore potentially anomalous data from getting into your table; keys ensure that consumers of the data can accurately identify, use and update the information in the table. For these reasons, yes, you ought to create keys in your tables.