MySQL create temporary table from SELECT keep original field lengths

Question

Considering this simple example:

INSERT INTO TemporaryTable
SELECT RealTable.name as name
FROM RealTable
LIMIT 1

I'm creating a temporary table from a SELECT.

Problem: the temporary table's field size are set to the SELECT's result set's max length of each field. For instance, if the result set contains something like: name = "John Doe", the "name" field in the temporary table will be VARCHAR(8) because the length of "John Doe" is 8 even if RealTable.name is a varchar(255)

I want the TempoarayTable.name's length to be the same as the name from the real table. Is there a way to do it automatically? Thank you.

Answer 1

Do a

CREATE TEMPORARY TABLE tempTable
LIKE oldTable;

INSERT INTO tempTable
SELECT ... FROM ...

Answer 2

Is there a way to do it automatically?

NO. You will have to define the temporary table manually per the target table schema and then insert to it like

create temporary table temp_test(name varchar(255));


INSERT INTO temp_test
SELECT RealTable.name as name
FROM RealTable
LIMIT 1;