使用自动递增的 id 列创建 MySQL 视图
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2808759/
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
Creating a MySQL view with an auto-incrementing id column
提问by Igal Tabachnik
I have a MySQL database from which a view is created. Is is possible to add an auto-incrementing id for each row in the view?
我有一个从中创建视图的 MySQL 数据库。是否可以为视图中的每一行添加一个自动递增的 id？
I tried
我试过
CREATE ALGORITHM=UNDEFINED DEFINER=`database_name`@`%` SQL SECURITY DEFINER VIEW `MyView` AS
set @i = 0;
select @i:=@i+1 as `id`
...
but that doesn't work in a View.
但这在视图中不起作用。
采纳答案by John M
Sorry - you can't autoincrement in a VIEW (You could do this in a Stored Procedure though).
抱歉 - 您不能在 VIEW 中自动递增（尽管您可以在存储过程中执行此操作）。
From the MySQL Manual:
从MySQL 手册：
A view definition is subject to the following restrictions: The SELECT statement cannot refer to system or user variables.
视图定义受以下限制： SELECT 语句不能引用系统或用户变量。
回答by letimome
I know this question is old, but just in case others come across this question there is another alternative.
我知道这个问题很老，但以防万一其他人遇到这个问题，还有另一种选择。
IMPORTANT: This alternative is valid as long as the autoincrement is not really important, and so you only need an unique identifier for the view rows:
重要提示：只要自动增量不是很重要，此替代方法就有效，因此您只需要视图行的唯一标识符：
You can use the UUID() function which provides you with a unique alphanumerical identifier. Check documentation at mysql-reference-manual
您可以使用 UUID() 函数，该函数为您提供唯一的字母数字标识符。检查mysql-reference-manual 中的文档
Hence you could create a view like this:
因此，您可以创建这样的视图：
Create view my-view AS
Select UUID() as 'id', t.name, t.value
from table t
....
回答by u12137093
try this,
尝试这个，
create view view_st as
select row_number() over (order by column_st) id, column_st
from table_st;
回答by Rahen Rangan
Try this,
尝试这个，
CREATE VIEW view_wp_postmeta AS(
SELECT (
SELECT count( meta_id ) +1
FROM wp_postmeta
AS vtmp
WHERE vtmp.meta_id < pm.meta_id
) AS vtmp_id, pm. *
FROM wp_postmeta AS pm
ORDER BY pm.meta_id DESC
)
In WordPress meta_id = AUTO_INCREMENT in wp_postmeta TABLE, i create a VIEW for this, in which view_id behaves like AUTO_INCREMENT in DESC order.
在 wp_postmeta TABLE 中的 WordPress meta_id = AUTO_INCREMENT 中，我为此创建了一个 VIEW，其中 view_id 的行为类似于 DESC 顺序中的 AUTO_INCREMENT。
For example, In TABLEwp_postmeta -> meta_id [3,5,6,10,2,11] which would appear in VIEWview_wp_postmeta -> view_id [6,5,4,3,2,1]
例如，在TABLEwp_postmeta -> meta_id [3,5,6,10,2,11] 这将出现在VIEWview_wp_postmeta -> view_id [6,5,4,3,2,1]
