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

Question

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 could 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).

Answer 1

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

Answer 2

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).

Answer 3

I just came across this:

http://www.bigdbahead.com/?p=183

Thinking about using that instead of a MEMORY table. Anyone know anything about it or have any comments?