MySQL: create table if it doesn't exist, always appending data instead of creating gaps table
I have table 'table1' and want to create another table, but store it in memory (engine = memory) with data from the table.
In my test server the SQL works as expected - create the table once, put the data into it from "table1" and next time just skip the insert because the "mem_table" table already exists.
In a production SQL server, unexpectedly - it creates a "mem_table" table, inserts data from "table1". Everyone starts it inserting data over and over.
SQL:
CREATE TABLE IF NOT EXISTS `mem_table` (
`f1` mediumint(9) NOT NULL AUTO_INCREMENT,
`f2` mediumint(9) NOT NULL,
`f3` varchar(100) NOT NULL
PRIMARY KEY (`f1`),
KEY `f3` (`f3`)
) ENGINE=MEMORY DEFAULT CHARSET=cp1251 AUTO_INCREMENT=1 SELECT NULL f1, f2, f3 FROM table1;
source to share
This behavior, as the documentation says, is version dependent.
So the solution doesn't use "IF NOT EXISTING" in CREATE TABLE. And if the table exists, mysql just returns an error and doesn't insert data over and over.
CREATE TABLE `mem_table` (
`f1` mediumint(9) NOT NULL AUTO_INCREMENT,
`f2` mediumint(9) NOT NULL,
`f3` varchar(100) NOT NULL
PRIMARY KEY (`f1`),
KEY `f3` (`f3`)
) ENGINE=MEMORY DEFAULT CHARSET=cp1251 AUTO_INCREMENT=1 SELECT NULL f1, f2, f3 FROM table1;
source to share
Discovered through our comments on your question:
The problem seems to be related to not being allocated memory for the table.
Take a look at the common issues for storing memory based tables for MySQL: https://dev.mysql.com/doc/refman/4.1/en/full-table.html
source to share