[EMAIL PROTECTED] wrote:
> [Donny Lairson] Quick bump I never got an answer > > I have a table fsearch_temp I use it as a memory table to keep things > light and fast but after a restart I want to repopulate some data > automatically. So I thought I just said load from TEST2 which would by a > myisam table containing the hardbackup I need. But obviously not the > right way of saying this. I must be reading the instructions wrong can > someone clarify this for me? <snip>
Which instructions are you reading? I expect you get a syntax error, right? From the manual <http://dev.mysql.com/doc/mysql/en/create-table.html>, the correct syntax is
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options] [select_statement]Gleb Paharenko wrote:
Hello.
) ENGINE = MEMORY LOAD FROM TEST2 DEFAULT CHARSET = utf8 AUTO_INCREMENT =0
You should use select statement, not LOAD. For example: CREATE TABLE ..... SELECT * FROM TEST2;
And table options like DEFAULT CHARSET you should put before select statement. See: http://dev.mysql.com/doc/mysql/en/create-table.html
I think this is accurate but misleading. CREATE ... SELECT adds columns from the SELECT to the columns defined in the CREATE, so you cannot fix this simply by getting the last line right. You have to leave out the column definitions. On the other hand (from the manual page you cite),
CREATE TABLE ... SELECT does not automatically create any indexes for you. This is done intentionally to make the statement as flexible as possible. If you want to have indexes in the created table, you should specify these before the SELECT statement...
so you do need to keep the index definitions. Thus, assuming fsearch_temp's create_definition matches that of table TEST2, to create fsearch_temp as a copy of TEST2, you would
CREATE TABLE fsearch_temp ( PRIMARY KEY (fsearchId), KEY fsearchIp (fsearchIp) ) ENGINE = MEMORY DEFAULT CHARACTER SET utf8 SELECT * FROM TEST2;
but I don't think this is what you want, either.
First, there is this caveat (from the manual):
Some conversion of column types might occur. For example, the AUTO_INCREMENT attribute is not preserved, and VARCHAR columns can become CHAR columns.
To avoid that, you need to first CREATE the table, then populate it with a copy of TEST2 in a separate INSERT ... SELECT statement. See the manual for details <http://dev.mysql.com/doc/mysql/en/insert-select.html>.
In any case, MEMORY tables don't go away unless they are dropped. Only the rows disappear when mysql stops. If you've previously created this table and haven't dropped it, it should still exist as an empty table on startup. In that case, you only need to reload the rows.
INSERT INTO fsearch_temp SELECT * FROM TEST2;
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
