Auto increment temporary column in select statement MySQL 8

Hello i use this type of sentence to return an auto increment column in my prepared statement selects

cnt := cnt + 1

        SET @query = CONCAT('SELECT * FROM (SELECT (@cnt := @cnt + 1) AS id, a.idProducto idProducto, a.idExProducto idExterno, trim(a.descripcion) nombreProducto, trim(a.descripcionAlt) nombreLargoProducto, trim(a.serial) serial,',    
                        ' a.peso, a.volumen, a.IdTpoGrupo, trim(b.descripcion) nombreGrupo,',
                        ' a.idTpoLinea, trim(c.descripcion) nombreLinea,',                          
                        ' a.idTpoMarca, trim(d.descripcion) nombreMarca,', 
                        ' a.idTpoUnidad, trim(e.descripcion) nombreTipoUnidad,', 
                        ' CASE WHEN a._estado = 1 THEN ''true'' ELSE ''false'' END estado, ',  
                        ' g.nombreArchivo imagen ',  
                        ' FROM tblProducto a',          
                        ' INNER JOIN tblProducto_TpoGrupo b ON a.IdTpoGrupo = b.IdTpoGrupo',
                        ' INNER JOIN tblProducto_TpoLinea c ON a.idTpoLinea = c.idTpoLinea',
                        ' INNER JOIN tblProducto_TpoMarca d ON a.idTpoMarca = d.idTpoMarca',
                        ' INNER JOIN tblTpoUnidadMedida e ON a.idTpoUnidad = e.idTpoUnidadMed ',
                        ' LEFT JOIN tblProductoXImagen f ON a.idProducto = f.idProducto  AND f._estado=1',
                        ' LEFT JOIN tblImagen g on f.idImagen = g.idImagen AND g._estado=1',
                        ' WHERE a._estado<2 ',whereLike,whereConcat, ' order by idProducto ) allrecords');             
                        
                        
                        
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

that works perfect on MySQL 5, but now in MySQL 8 is deprecated and my transaction return a warning, but the warning stop the execution of my stored procedure, i want to know if there is a solution to can achieve this behavior in MySQL 8.

Thanks for your help.

Answer

You can use window functions. The logic you sem to want is:

row_number() over(order by idProducto) as id

This gives you an incrementing integer value, that starts at 1 and increments according to idProducto. If two rows in the resultset have the same idProducto, it is undefined which one will be ordered “first” (it is, however, guaranteed that they will not get the same row number) – in that case, you might want to add one or more columns to the order by clause, so you do get a predictable, stable result.

Advertisement

Answer