Creating a MySQL view with an auto-incrementing id column

Question

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?

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.

Answer 1

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

Hence you could create a view like this:

Create view my-view AS
Select UUID() as 'id', t.name, t.value
from table t
....

Answer 2

Sorry - you can't autoincrement in a VIEW (You could do this in a Stored Procedure though).

From the MySQL Manual:

A view definition is subject to the following restrictions: The SELECT statement cannot refer to system or user variables.