Workaround to allow a TEXT column in mysql MEMORY/HEAP table

I want to use a temporary MEMORY table to store some intermediate data, but I need/want it to support TEXT columns. I had found a workaround involving casting the TEXT to a VARCHAR or something, but like an idiot I didn't write down the URL anywhere I can find now.

Does anyone know how to, for example, copy a table x into a memory table y where x may have TEXT columns? If anyone knows how to cast columns in a "CREATE TABLE y SELECT * FROM x" sorta format, that would definitely be helpful.

Alternatively, it would help if I coul开发者_运维技巧d create a table that uses the MEMORY engine by default, and "upgrades" to a different engine (the default maybe) if it can't use the MEMORY table (because of text columns that are too big or whatever).

You can specify a SELECT statement after CREATE TEMPORARY TABLE:

CREATE TEMPORARY TABLE NewTempTable
SELECT
    a
,   convert(b, char(100)) as b
FROM OtherTable

Re comment: it appears that CHAR is limited to 512 bytes, and you can't cast to VARCHAR. If you use TEXT in a temporary table, the table is stored on disk rather than in memory.

What you can try is defining the table explicitly:

CREATE TEMPORARY TABLE NewTempTable (
    a int
,   b varchar(1024)
)

insert into NewTempTable
select a, b
from OtherTable

You can use varChar(5000). No need to cast. If you have example data, you can use it as a measure. There is 64Kb space.

The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions. The effective maximum length of a VARCHAR in MySQL 5.0.3 and later is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.

Do you mean CAST(text_column AS CHAR)? Note that you shouldn't need it, MySQL will cast it automatically if the target column is VARCHAR(n).