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: 
try this,

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

```
I was looking for the same as you, but came in the conclusion that what I was looking for was a way to uniquely and stably identify records in a view.

My use case is a "bank operations" view, which involves consolidating the records from a "deposits" table with the ones from a "withdrawals" table, each of which contain an autonumeric id column, as best practices suggest.

So, I have two operation types

1. Deposit
2. Withdrawal

I decide that it is valid for this case if every row in my view that starts with 1 is a deposit and if starts by 2 is a withdrawal. So to concatenate both, you better add some zeroes to the operation type itself, so 1 becomes, let's say, 10000000 (if you add 7 0s) and suppose that this withdrawal had an autonumeric id of say, 33.

Then you go: 10000000+33 = 10000033
And so, If it happens that you have a withdrawal record which id is 33, It shouldn't collide with our deposit (as long as we added enough 0s), because it would turn out to be something like: 20000033.

Got it? Ok, now run this:

```
DELIMITER $$
CREATE FUNCTION renderVirtualId(part1 INT, part2 INT, len INT) RETURNS bigint(20) unsigned
    NO SQL
BEGIN
return part1*POW(10,len)+part2;
end$$
DELIMITER;

```

And then you compile your view like this:

```
CREATE OR REPLACE VIEW v_balance AS
SELECT renderVirtualId(1,id,10) as id,amount,account,datetime FROM deposits
UNION
SELECT renderVirtualId(2,id,10) as id,-amount,account,datetime FROM withdrawals
ORDER BY datetime DESC

```

Hope you enjoy it. I actually did.