MySQL create temporary table with autoincrement from SELECT only

Question

With MySQL I know it's possible to create a temporary table from a SELECT: Create a temporary table in a SELECT statement without a separate CREATE TABLE

I also know it's possible to create autoincrement in a temporary table: Auto Increment in Temporary Table

I want to do both at the same time: create a temporary table from a SELECT statement and I want the temporary table to have an auto increment column.

It is possible to do that?

Answer 1

If you want to create a temporary table with a row number column, then use variables:

create temporary table temp as
    select (@rn := @rn + 1) as seqnum, t.*
    from t cross join
         (select @rn := 0) vars;

This will not be auto-incrementing for new inserts. If you need that, you'll need to alter the table definition.