MySQL: Create a table if there is not always adding data instead of skipping the table creation

advertisements

I have a table 'table1' and want to create another table, but store it in memory (engine=memory), with data from table1.

In my test server SQL works as expected - create the table once, put data in it from 'table1' and next time just skips insertion, because table 'mem_table' already exists.

In production server SQL is unexpected - it's create table 'mem_table', insert data from 'table1'. Every run it's inserting data again and again.

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;


This behavior, as documentation says, is version dependent.

So, the solution is not using "IF NOT EXISTS" in CREATE TABLE. And if the table is exists, mysql just return error and didn't insert data again and again.

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;