create autoincrement in MySQL with pre text

Question

I have trouble for my project using mysql, i want to create Auto Increment in view table, i create sintax like this one:

SELECT 
    concat(@AI:= @AI + 1,`code`)
FROM 
    `TEST TABLE`, (SELECT @AI:=0) as `AI`

Why if i add syntax in first line like this one:

CREATE VIEW `TEST VIEW` as

I have some error :

How fix it, or other method for this?. thanks for advance!

Answer 1

If you were using Oracle, you would use an object called a sequence for this purpose. But, who has the money for Oracle licenses?

If you need a series of numbers and you're using the MariaDB fork, you can do

     SELECT seq FROM seq_0_to_99 

or some such use of the SEQUENCE engine.

If you need persistent sequence numbers in MySQL, here's a workaround. It's a kludge: If you create the following table:

 CREATE TABLE sequence (                                       /*MySQL*/
     sequence_id BIGINT NOT NULL AUTO_INCREMENT,
     PRIMARY KEY (`sequence_id`)
 ) 

Then issue these three queries one after the other:

INSERT INTO sequence () VALUES ();                            /*MySQL*/
DELETE FROM sequence WHERE sequence_id < LAST_INSERT_ID();
SELECT LAST_INSERT_ID() AS sequence;

The third query is guaranteed to return a unique sequence number. This guarantee holds even if you have dozens of different client programs connected to your database. (The DELETE query merely keeps this otherwise pointless table from taking up too much space.)

The error message you received makes it clear that you can't use a session variable in a view.

Answer 2

https://dev.mysql.com/doc/refman/5.7/en/create-view.html says:

A view definition is subject to the following restrictions:

• The SELECT statement cannot refer to system variables or user-defined variables.

You can't create a view for your query. You'll have to execute the query directly. The only other suggestion I can make is to develop a stored procedure for the query.

It sounds like you want to create a row number for a query result, not an auto-increment column to store in the table.

MySQL 8.0.2 has added the window function ROW_NUMBER(), but 8.0 is still under development as we're writing this. Perhaps in 2018 it will be finished and released as GA.