Menu
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.
520
In MySQL, the syntax to change the starting value for an AUTO_INCREMENT column using the ALTER TABLE statement is: ALTER TABLE table_name AUTO_INCREMENT = start_value; table_name.
Syntax for MySQLMySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature. By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record. VALUES ('Lars','Monsen'); The SQL statement above would insert a new record into the "Persons" table.
If you do really need to have a second column with "auto increment" type behavior, one way to get that is to add a second dummy table with an auto_increment column, and use a BEFORE INSERT trigger to do an insert into the dummy table, and retrieve the id value that was inserted.
Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.
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.
21
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
