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.

Answer 3

try this,

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

Answer 4

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.

For example, In TABLE wp_postmeta -> meta_id [3,5,6,10,2,11] which would appear in VIEW view_wp_postmeta -> view_id [6,5,4,3,2,1]

Answer 5

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.

Answer 6

this works:

SET @autoIncrementNumber = 0;

SELECT @autoIncrementNumber:=@autoIncrementNumber + 1 AS Num, columnName FROM viewName limit 0,8;

Answer 7

if you prefer this solution within an one-liner check this:

SELECT @autoIncrementNumber:=@autoIncrementNumber + 1 AS Num, 
columnName FROM viewName,(select @autoIncrementNumber:=0) as temp limit 0,8;