创建一个包含日期名称的表 [英] create a table with date name

问题描述

我正在写一个简单的程序来备份表格 -

I am writing a simple procedure to backup a table-

CREATE PROCEDURE daily_backup()
BEGIN
   DECLARE given_date VARCHAR(25);
   SET given_date = now(); 

   CREATE TABLE given_date LIKE db1.table1;
   INSERT INTO given_date SELECT * FROM db1.table1;


END

但它创建一个名为given_date 。我想创建一个日期为名称的表。如何做同样的事？

But it creates a table with name given_date . I want to create a table with date as name. How to do the same ?

推荐答案

试试这个

DELIMITER |
CREATE PROCEDURE daily_backup()
BEGIN
  SET @s = CONCAT('CREATE TABLE bak_', DATE_FORMAT(CURDATE(), '%Y%m%d'), ' SELECT * FROM table1');
  PREPARE stmt FROM @s;
  EXECUTE stmt;
END
|
DELIMITER ;

UPDATE ：您可以每天执行多次

UPDATE: You can make it run multiple times a day

DROP PROCEDURE daily_backup;
DELIMITER |
CREATE PROCEDURE daily_backup()
BEGIN
  SET @tbl = DATE_FORMAT(CURDATE(), '%Y%m%d');
  SET @s = CONCAT('DROP TABLE IF EXISTS bak_', @tbl);
  PREPARE stmt FROM @s;
  EXECUTE stmt;

  SET @s = CONCAT('CREATE TABLE bak_', @tbl, ' SELECT * FROM table1');
  PREPARE stmt FROM @s;
  EXECUTE stmt;
END
|
DELIMITER ;

这是 SQLFiddle 演示

Here is SQLFiddle demo

这篇关于创建一个包含日期名称的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！