Syntax error while selecting sequential table in Mysql 8

Question

Query:

select 
    (@rownum:= @rownum + 1) as row_number 
from 
    (select * from information_schema.tables limit 25) t,
    (select @rownum := (date_format('2018-12-16 00:00:00','%k'))) r;

Getting a syntax error in MySQL 8, but not in MySQL 5.7.

Please help.

MySQL 8.0 supports [ROW_NUMBER](https://stackoverflow.com/questions/1895110/row-number-in-mysql/46753800#46753800) I would suggest rewriting code to utilize new feature. — Lukasz Szozda, Dec 24 '18 at 08:55
Are you running this code directly in MySQL, or from somewhere else? — Tim Biegeleisen, Dec 24 '18 at 09:00
Try change `row_number` to something else like `rn` as Lukas said now is a function of MySql 8 — Juan Carlos Oropeza, Dec 24 '18 at 09:13
[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 25 years** ago) and its use is discouraged — marc_s, Dec 24 '18 at 09:32

score 1 · Accepted Answer · answered Dec 24 '18 at 09:53

As commented already by brilliant SO folks : since version 8.0 of mysql, row_number has become a language keyword, you cannot use it as a column alias.

So you need to change this :

select 
(@rownum:= @rownum + 1) as row_number from
...

To :

select (@rownum:= @rownum + 1) as rn from 
....

Another solution is to quote the qualifier, like :

select (@rownum:= @rownum + 1) as "row_number" from 
....

Syntax error while selecting sequential table in Mysql 8

1 Answers1