MySQL 数据库表可以没有主键吗？

Can a database table be without a primary key?

提问by bodacydo

Can anyone tell me if a table in a relational database (such as MySQL / SQL SERVER) can be without a primary key?

谁能告诉我关系数据库（例如 MySQL / SQL SERVER）中的表是否可以没有主键？

For example, I could have table day_temperature, where I register temperatureand time. I don't see the reason to have a primary key for such a table.

例如，我可以有 table day_temperature，我在那里注册temperature和time. 我没有看到为这样的表设置主键的原因。

回答by Quassnoi

Technically, you can declare such a table.

从技术上讲，您可以声明这样一个表。

But in your case, the timeshould be made the PRIMARY KEY, since it's probably wrong to have different temperatures for the same time and probably useless to have same more than once.

但是在您的情况下，time应该将 设为PRIMARY KEY，因为在同一时间使用不同的温度可能是错误的，并且多次使用相同的温度可能没用。

Logically, each table should have a PRIMARY KEYso that you could distinguish two records.

从逻辑上讲，每个表都应该有一个，PRIMARY KEY以便您可以区分两条记录。

If you don't have a candidate key in you data, just create a surrogate one (AUTO_INCREMENT, SERIALor whatever your database offers).

如果您的数据中没有候选键，只需创建一个代理（AUTO_INCREMENT，SERIAL或您的数据库提供的任何内容）。

The only excuse for not having a PRIMARY KEYis a log or similar table which is a subject to heavy DMLand having an index on it will impact performance beyond the level of tolerance.

没有 a 的唯一借口PRIMARY KEY是日志或类似的表，这是一个沉重的主题，DML并且在其上建立索引会影响超出容忍水平的性能。

回答by Grzegorz Gierlik

Like always it depends.

像往常一样，这取决于。

Table does not haveto have primary key. Much more importantis to have correct indexes. On database engine depends how primary key affects indexes(i.e. creates unique index for primary key column/columns).

表不一定要有主键。更重要的是拥有正确的索引。在数据库引擎上取决于主键如何影响索引（即为主键列/列创建唯一索引）。

However, in your case (and 99% other cases too), I would add a new auto increment unique columnlike temp_idand make it surrogate primary key.

但是，在您的情况下（以及 99% 的其他情况），我会添加一个新的自动增量唯一列，例如temp_id并使其代理主键。

It makes much easiermaintaining this table -- for example finding and removing records (i.e. duplicated records) -- and believe me -- for every table comes time to fix things :(.

它使维护这个表变得更加容易——例如查找和删除记录（即重复的记录）——相信我——因为每个表都有时间修复:(。

回答by p.marino

If the possibility of having duplicate entries (for example for the same time) is not a problem, and you don't expect to have to query for specific records or range of records, you can do without any kind of key.

如果重复条目的可能性（例如同时）不是问题，并且您不希望查询特定记录或记录范围，则可以不用任何类型的键。

回答by reko_t

You don't need a PK, but it's recommended that you have one. It's the best way to identify unique rows. Sometimes you don't want an auto incremental int PK, but rather create the PK on something else. For example in your case, if there's only one unique row per time, you should create the PK on the time. It makes looks up based on time faster, plus it ensures that they're unique (you can be sure that the data integrity isn't violated):

您不需要PK，但建议您拥有一个。这是识别唯一行的最佳方式。有时您不想要自动增量 int PK，而是在其他东西上创建 PK。例如，在您的情况下，如果每次只有一个唯一行，则您应该按时创建 PK。它可以更快地根据时间进行查找，并且确保它们是唯一的（您可以确保不会违反数据完整性）：

回答by Steve

I would include a surrogate/auto-increment key, especially if there is any possibility of duplicate time/temperature readings. You would have no other way to uniquely identify a duplicate row.

我会包括一个代理/自动增量键，特别是如果有任何重复的时间/温度读数的可能性。您没有其他方法可以唯一标识重复的行。

回答by Gleeb

I run into the same question on one of the tables i did.

我在我做过的一张桌子上遇到了同样的问题。

The problem was that the PK was supposed to be composed out of all the rows of the table all is well but this means that the table size will grow very fast with each row inserted.

问题是 PK 应该由表的所有行组成，一切都很好，但这意味着表的大小会随着插入的每一行快速增长。

I choose to not have a PK, but only have an index on the row i do the lookup on.

我选择没有 PK，但只有在我进行查找的行上有一个索引。

回答by Teruki Shinohara

When you replicate a database on mysql, A table without a primary key may cause delay in the replication.

在mysql上复制数据库时，没有主键的表可能会导致复制延迟。

http://lists.mysql.com/mysql/227217

http://lists.mysql.com/mysql/227217

The most common mistake when using ROW or MIXED is the failure to verify that every table you want to replicate has a PRIMARY KEY on it. This is a mistake because when a ROW event (such as the one documented above) is sent to the slave and neither the master's copy nor the slave's copy of the table has a PRIMARY KEY on the table, there is no way to easily identify which unique row you want replication to change.

使用 ROW 或 MIXED 时最常见的错误是无法验证要复制的每个表上是否都有 PRIMARY KEY。这是一个错误，因为当一个 ROW 事件（例如上面记录的那个）被发送到从属时，并且主副本和从属副本都没有表上的 PRIMARY KEY，没有办法轻松识别哪个您希望复制更改的唯一行。

回答by Oooogi

According to your answer I would consider three options:

根据您的回答，我会考虑三种选择：

• put a PK on both cols, this way for each time there could be only one temp and vise versa. This solution allows for multiple rows with the same temp or the same time just that there wouldn't be any two rows with same temp AND time.
• don't put a PK at all but do put a unique index on both cols. one unique index containing both cols. this would allow for nulls in temp and time but incurs more space to maintain index.

• 将 PK 放在两个 cols 上，这样每次只能有一个 temp，反之亦然。此解决方案允许具有相同温度或相同时间的多行，只是不会有任何两行具有相同的温度和时间。
• 根本不要放置 PK，而是在两个列上放置唯一索引。一个包含两个列的唯一索引。这将允许临时和时间为空，但会产生更多空间来维护索引。

these two options would be best for retrieval speed if you have heavy reads but would result in lower inserts rate as indices would have to be updated as well.

如果您有大量读取，这两个选项最适合检索速度，但会导致插入率较低，因为索引也必须更新。

• don't put any index at all, nor PK. this would be best for inserts but very bad for searching. useful for logging where retrieval is done by another mechanism or when inserting device is not required to check for dups.

• 根本不放任何索引，也不放PK。这对于插入来说是最好的，但对于搜索来说非常糟糕。对于记录由另一种机制完成的检索或不需要插入设备来检查重复的记录很有用。

Also, it is very important to consider cardinality here and think about future consequences of using an auto incremented number. if you're planning to do A LOT OF inserts then even an auto incremented unsigned bigint would be a risk because it would eventually run out. In your example I guess you'll be saving data daily - for how long? this would be problematic if you saved temp every minute... so I'll take this as an extreme example.

此外，在这里考虑基数并考虑使用自动递增数字的未来后果非常重要。如果你打算做很多插入，那么即使是自动递增的 unsigned bigint 也会有风险，因为它最终会用完。在您的示例中，我猜您每天都会保存数据 - 保存多长时间？如果您每分钟都保存温度，这将是有问题的...所以我将把它作为一个极端的例子。

I guess it is best to think about what you need from the table. are you doing "save-and-forget" for the entire year for the temp at every minute? are you going to use this table frequently in real-time decision making in your business logic? I think it is best to segregate data necessary for real-time (oltp) from long-term saving data that would be required seldom and its retrieval latency is allowed to be high (olap). it's even worth duplicating the data into two different tables, one heavily indexed and get erased once in a while to control cardinality and the second is actually saved on a magentic disk with almost no indices at all (it is possible to transfer a schema from your main fs into another fs).

我想最好从表格中考虑您需要什么。您是否每分钟都在为温度进行全年“保存并忘记”？您是否会在业务逻辑的实时决策中频繁使用此表？我认为最好将实时（oltp）所需的数据与很少需要的长期保存数据分开，并且允许其检索延迟很高（olap）。甚至值得将数据复制到两个不同的表中，一个被大量索引并偶尔被擦除以控制基数，第二个实际上保存在几乎没有索引的磁盘上（可以从您的主文件进入另一个文件）。

回答by Avinash Pawar

Even if you do not add a primary key to an InnoDB table in MySQL, MySQL adds a hidden clustered index to that table. If you do not define a primary key, MySQL locates the first UNIQUE index where all the key columns are NOT NULL and InnoDB uses it as the clustered index.

即使您没有向 MySQL 中的 InnoDB 表添加主键，MySQL 也会向该表添加一个隐藏的聚集索引。如果没有定义主键，MySQL 会定位第一个 UNIQUE 索引，其中所有键列都不是 NULL，InnoDB 将其用作聚集索引。

If the table has no primary key or suitable UNIQUE index, InnoDB internally generates a clustered index GEN_CLUST_INDEX on a synthetic column containing row ID values.

如果表没有主键或合适的 UNIQUE 索引，则 InnoDB 在包含行 ID 值的合成列上内部生成聚集索引 GEN_CLUST_INDEX。

https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html

https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html

回答by JonH

The time would then become your primary key. It will help index that column so that you can query data based on say a date range. The PK is what ultimately makes your row unique, so in your example, the datetime is the PK.

时间将成为您的主键。它将帮助索引该列，以便您可以根据日期范围查询数据。PK 最终使您的行独一无二，因此在您的示例中，日期时间是 PK。