Creating a MySQL view with an auto-incrementing id column

try this,

create view view_st as
select row_number() over (order by column_st) id, column_st 
from table_st;

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
....

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.

Tags:

Mysql

View