Row Number on MySQL PDO

There are few approaches to generate sequence row number.

On regular MySQL or MySQLi query it would be utilize SET statement

set @row_number = 0;
select 
	(@row_number:=@row_number + 1) AS num,
	column_name
from table_name

But PDO can’t execute multiple queries separated by semicolon.

If you running MySQL version 8, it have built in function row_number() and this is how it looks like

select 
	row_number() over (
		order by column_name
	) as num,
column_name
from table_name

Unfortunately if client still on older version of MySQL below version 8, the function row_number is not exists.

The solution for row number below MySQL 8 is put variable as derivative table, like this

select
	(@row_number:=@row_number + 1) as num,
	column_name
from
(select @row_number:=0) as derivative
join table_name