MySQL 如何在 VIEW 中拥有主键（不依赖于源表的键）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1971826/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I have a primary key in a VIEW (a key that doesn't depend on source tables)
提问by Kabeer
I'm creating a VIEW out of 3 TABLES. Each TABLE has it's primary key. However, in the VIEW in order to have a primary key, I'll have to use a composite primary key (combination of primary keys of 3 TABLES).
我正在从 3 个表中创建一个视图。每个 TABLE 都有它的主键。但是，在 VIEW 中为了有一个主键，我将不得不使用一个复合主键（3 个 TABLES 的主键的组合）。
I would however like to add a column in the VIEW as a primary key that is created just for the purpose of the VIEW. As part of the VIEW definition, it should be UNIQUE(autoincrement since it would be mostly an INT). How can I achieve this?
但是，我想在 VIEW 中添加一列作为主键，该列仅为了 VIEW 的目的而创建。作为 VIEW 定义的一部分，它应该是 UNIQUE（自动增量，因为它主要是一个 INT）。我怎样才能做到这一点？
I am using MySQL 5.1
我正在使用 MySQL 5.1
采纳答案by Paul Creasey
you could use various methods to insert a unique ID to the view data, such as:
您可以使用各种方法向视图数据插入唯一 ID，例如：
SELECT @rownum:=@rownum+1 as id, mytable.*
FROM (SELECT @rownum:=0) r, mytable;
However this is nota primary key, it is not consistant and will change when the data changes.
然而，这不是主键，它是不一致的，并且会随着数据的变化而变化。
What exactly do you need a key for?
你究竟需要一把钥匙做什么？
回答by David Clifton
The highest voted answer using
使用得票最高的答案
SELECT @rownum:=@rownum+1 as id, mytable.*
FROM (SELECT @rownum:=0) r, mytable;
Is incorrect - you cannot create a view in mysql that uses a sub-select in the FROM. You're only option is to treat a set of columns as a composite key.
不正确 - 您不能在 mysql 中创建使用 FROM 中的子选择的视图。您唯一的选择是将一组列视为组合键。
回答by mopoke
Views don't have primary keys or indexes - the mysql engine will use the indexes and keys defined on the base table(s).
视图没有主键或索引 - mysql 引擎将使用在基表上定义的索引和键。
回答by Rob Farley
A view is just a stored sub-query. The idea of a PK is irrelevant.
视图只是一个存储的子查询。PK 的想法是无关紧要的。
...unless you need an indexed view, in which case you need a Clustered Index on the view (but still not necessarily a Primary Key)
...除非您需要索引视图，在这种情况下，您需要视图上的聚集索引（但仍然不一定是主键）
(although, I'm talking SQL Server... I'm not sure how this translates to MySQL)
（虽然，我说的是 SQL Server ......我不确定这如何转换为 MySQL）
