[MySQL] Creating a timestamped backup table

Sometimes it's useful to create a backup table before doing a datas update in a SQL script.
But if this script must be run frequently, the table must be timestamped. However, it's not possible to create a table using a function (as CREATE TABLE MyTable_CURDATE()).

To do this, the usage of Prepared Statements is really useful :

SET @CUR:=CURDATE() +0;
SET @createTab=CONCAT("CREATE TABLE MyTable_",@CUR , " LIKE MyTable;");

PREPARE stmt FROM @createTab;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Remember that is possible to create backup table from different ways :

1st method :

CREATE TABLE MyTable_BACKUP LIKE MyTable;
INSERT INTO MyTable_BACKUP SELECT * FROM MyTable;

2nd method :

CREATE TABLE MyTable_BACKUP SELECT * FROM MyTable;

The first method is longer because of backup table indexes creation, this one inherits those of the original table. So, the insert command force to calculate indexes during the process.
The second one creates a new table with the same structure without indexes, so inserts are quicker. It's better to add indexes at the end of the backup.

Pavnay

[MySQL] Creating a timestamped backup table

Articles similaires

3.26 Copyright (C) 2008 Compojoom.com / Copyright (C) 2007 Alain Georgette / Copyright (C) 2006 Frantisek Hliva. All rights reserved."

RTFM

Actualités

Graphic

Miniature painting

Suggestions