CODEWITHSUNDEEP
mysqlsql
greece87
greece87

Reputation: 3

How to alter and add 'id' column in my MySQL view

I have view in Mysql, but I do not have id column with AI, how can I sort it out? I tried this but did not work

ALTER VIEW `user_address_view` 
    ADD `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY

Upvotes: 0

Views: 436

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270091

You are confusing views and tables. When you modify a view, you need to provide the query that is to be executed.

You can do this in MySQL 8.0 as:

create view v as
    select row_number() over (order by ?) as id, t.*
    from t;

You can do this in a query as:

select (@rn := @rn + 1) as id, t.*
from t cross join
     (select @rn := 0) params;

Unfortunately, MySQL does not allow variables in views, so there is not an efficient way to do this in a view.

Upvotes: 1

Related Questions