Howto create a MySQL table that revert itself after closing the connection

Howto create a table that revert itself after closing the connection, for example you always want the same data everytime you run your script/program. This way you can ensure the same data is in the table everytime you run a program / script. I used it for a unit test purpose.

You have a user table with three columns; id, firstname and lastname. Create the same table but then as a temporary table,

SHOW CREATE TABLE `user`

rename user to user_temp
delete every constraint, foreign keys and references and then create the temporary table.

CREATE TEMPORARY_TABLE `user_temp` (
  `id` int(8) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `lastname` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

fill the the temporary table with the data from the real table, I use the first 1000 records.

INSERT INTO user_temp (SELECT * FROM user LIMIT 1000);

the rename the temporary table to the original name,

ALTER TABLE `user_temp` RENAME TO `user`

Now when you do a insert, update or delete on the tabel user everything is lost when the connection is closed or the temporary table is dropped.

DROP TEMPORARY TABLE `user`