DELIMITER $$CREATE EVENT `FF1` ON SCHEDULE EVERY 5 MINUTE STARTS '2012-08-11 00:00:01' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN CREATE TABLE IF NOT EXISTS tt(id INT); SET @i=1; WHILE @i<=1000 DO INSERT INTO tt VALUES(@i); SET @i=@i+1; END WHILE ; END$$DELIMITER ;
忘了说表里的字段了。这个表的字段是:id name age sex,id是自动编号,其他为字符串。
DELIMITER $$CREATE EVENT `FF1` ON SCHEDULE EVERY 5 MINUTE STARTS '2012-08-11 00:00:01' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN SET @asql=CONCAT('CREATE TABLE IF NOT EXISTS tb_',DATE_FORMAT(CURDATE(),'%Y%m%d'),'(id INT AUTO_INCREMENT, name varchar(10), age varchar(10) ,sex varchar(1),key (id))'); PREPARE stml FROM @asql; EXECUTE stml; SET @i=1; WHILE @i<=1000 DO INSERT INTO tt VALUES(@i); SET @i=@i+1; END WHILE ; END$$DELIMITER ;
DELIMITER $$CREATE EVENT `FF1` ON SCHEDULE EVERY 5 MINUTE STARTS '2012-08-11 00:00:01' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN SET @AA=CONCAT('Tb_',DATE_FORMAT(CURDATE(),'%Y%m%d')); SET @asql=CONCAT('CREATE TABLE IF NOT EXISTS tb_',DATE_FORMAT(CURDATE(),'%Y%m%d'),'(id INT AUTO_INCREMENT, name varchar(10), age varchar(10) ,sex varchar(1),key (id))'); PREPARE stml FROM @asql; EXECUTE stml; SET @i=1; WHILE @i<=1000 DO SET @ASQL=CONCAT('INSERT INTO ',@AA,'(ID) VALUES(',@i,')'); PREPARE stml FROM @asql; EXECUTE stml; SET @i=@i+1; END WHILE ; END$$DELIMITER ;
你发的两个,哪个是正确的? 你给我的这个是存储过程?我用MySQL Command Line Client控制台怎么把它执行。
mysql> DELIMITER $$ mysql> mysql> CREATE EVENT `FF1` ON SCHEDULE EVERY 5 MINUTE STARTS '2012-08-11 00:00:01 ' ON COMPLETION NOT PRESERVE ENABLE DO -> BEGIN -> SET @AA=CONCAT('Tb_',DATE_FORMAT(CURDATE(),'%Y%m%d')); -> SET @asql=CONCAT('CREATE TABLE IF NOT EXISTS tb_',DATE_FORMAT(CURDATE(),' %Y%m%d'),'(id INT AUTO_INCREMENT, '> name varchar(10), age varchar(10) ,sex varchar(1),key (id))'); -> PREPARE stml FROM @asql; -> EXECUTE stml; -> SET @i=1; -> WHILE @i<=1000 DO -> SET @ASQL=CONCAT('INSERT INTO ',@AA,'(ID) VALUES(',@i,')'); -> PREPARE stml FROM @asql; -> EXECUTE stml; -> SET @i=@i+1; -> END WHILE ; -> END$$ Query OK, 0 rows affected (0.00 sec)mysql> mysql> DELIMITER ; mysql>
我的库名是test1你的代码里那个是库的名称?
直接写一个shell 类似#!/bin/bash date1=`date --date "1 days" +%Y%m%d` date2=`date --date "180 days ago" +%Y%m%d` mysql itemchangehis --socket=/home/mysql/data_82/mysql.sock -uroot -ptoodou_newpass -e"CREATE TABLE td_itemchangehis_"$date1" ( id INT NOT NULL AUTO_INCREMENT , item_id INT NOT NULL , oper_type TINYINT(4) NOT NULL , change_type TINYINT(4) NOT NULL , add_time TIMESTAMP NOT NULL , PRIMARY KEY (id), key (item_id) ) ENGINE = InnoDB DEFAULT CHARACTER SET = gbk;" 然后做到crontab里面
http://blog.csdn.net/acmain_chm/article/details/7703870定时执行mysql数据库任务方案 命题: 每日 02:00 am 定时删除 表 t (id int primary key,ptime TIMESTAMP DEFAULT CURRENT_TIMESTAMP) 中三天以前的记录。 ======================================= 方法一:操作系统 ======================================= (...
5.1.6以上的Mysql才支持。但是不妨碍,一会卸了重装。
BEGIN
CREATE TABLE IF NOT EXISTS tt(id INT);
SET @i=1;
WHILE @i<=1000 DO
INSERT INTO tt VALUES(@i);
SET @i=@i+1;
END WHILE ;
END$$DELIMITER ;
BEGIN
SET @asql=CONCAT('CREATE TABLE IF NOT EXISTS tb_',DATE_FORMAT(CURDATE(),'%Y%m%d'),'(id INT AUTO_INCREMENT,
name varchar(10), age varchar(10) ,sex varchar(1),key (id))');
PREPARE stml FROM @asql;
EXECUTE stml;
SET @i=1;
WHILE @i<=1000 DO
INSERT INTO tt VALUES(@i);
SET @i=@i+1;
END WHILE ;
END$$DELIMITER ;
BEGIN
SET @AA=CONCAT('Tb_',DATE_FORMAT(CURDATE(),'%Y%m%d'));
SET @asql=CONCAT('CREATE TABLE IF NOT EXISTS tb_',DATE_FORMAT(CURDATE(),'%Y%m%d'),'(id INT AUTO_INCREMENT,
name varchar(10), age varchar(10) ,sex varchar(1),key (id))');
PREPARE stml FROM @asql;
EXECUTE stml;
SET @i=1;
WHILE @i<=1000 DO
SET @ASQL=CONCAT('INSERT INTO ',@AA,'(ID) VALUES(',@i,')');
PREPARE stml FROM @asql;
EXECUTE stml;
SET @i=@i+1;
END WHILE ;
END$$DELIMITER ;
你给我的这个是存储过程?我用MySQL Command Line Client控制台怎么把它执行。
mysql>
mysql> CREATE EVENT `FF1` ON SCHEDULE EVERY 5 MINUTE STARTS '2012-08-11 00:00:01
' ON COMPLETION NOT PRESERVE ENABLE DO
-> BEGIN
-> SET @AA=CONCAT('Tb_',DATE_FORMAT(CURDATE(),'%Y%m%d'));
-> SET @asql=CONCAT('CREATE TABLE IF NOT EXISTS tb_',DATE_FORMAT(CURDATE(),'
%Y%m%d'),'(id INT AUTO_INCREMENT,
'> name varchar(10), age varchar(10) ,sex varchar(1),key (id))');
-> PREPARE stml FROM @asql;
-> EXECUTE stml;
-> SET @i=1;
-> WHILE @i<=1000 DO
-> SET @ASQL=CONCAT('INSERT INTO ',@AA,'(ID) VALUES(',@i,')');
-> PREPARE stml FROM @asql;
-> EXECUTE stml;
-> SET @i=@i+1;
-> END WHILE ;
-> END$$
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> DELIMITER ;
mysql>
我的库名是test1你的代码里那个是库的名称?
类似#!/bin/bash
date1=`date --date "1 days" +%Y%m%d`
date2=`date --date "180 days ago" +%Y%m%d`
mysql itemchangehis --socket=/home/mysql/data_82/mysql.sock -uroot -ptoodou_newpass -e"CREATE TABLE td_itemchangehis_"$date1" (
id INT NOT NULL AUTO_INCREMENT ,
item_id INT NOT NULL ,
oper_type TINYINT(4) NOT NULL ,
change_type TINYINT(4) NOT NULL ,
add_time TIMESTAMP NOT NULL ,
PRIMARY KEY (id),
key (item_id)
) ENGINE = InnoDB DEFAULT CHARACTER SET = gbk;"
然后做到crontab里面
先执行mysql>use test1然后再复制6楼的代码执行么?
按6楼的代码执行完后显示:
Query OK, 0 rows affected (0.00 sec)这算是执行成功了吧?
等会看看有效果没。应给没5分钟test的表里会插入1000数据。
命题: 每日 02:00 am 定时删除 表 t (id int primary key,ptime TIMESTAMP DEFAULT CURRENT_TIMESTAMP) 中三天以前的记录。 ======================================= 方法一:操作系统 ======================================= (...