MYSQL 存储过程带参数 按时间创建相应的表

废话少说直接看代码。不明白的就留言

create procedure sm_creatMonthTbProc(mdate varchar (8))
begin
  declare sm_results_name varchar(30);
  set @sm_results_name = concat('sm_results_',mdate);
  set @csql = concat("create table if not exists ",@sm_results_name , " (ssh_id int(20) NOT NULL,`result` text,`testime` bigint(20) NOT NULL default '0',  `mid` bigint(20) NOT NULL,  PRIMARY KEY  (`ssh_id`,`testime`,`mid`),KEY  ",@sm_results_name,"_index01 (`ssh_id`),  KEY  ",@sm_results_name,"_index02  (`mid`),  KEY  ",@sm_results_name,"_index03 (`testime`)) ENGINE=MyISAM ;");
 
  PREPARE create_stmt from @csql;
  EXECUTE create_stmt;
end

执行 命令查看是否创建成功

SHOW PROCEDURE STATUS

mysql> SHOW PROCEDURE STATUS //
+--------------+---------------------+-----------+----------------+---------------------+---------------------+---------------+---------+
| Db           | Name                | Type      | Definer        | Modified            | Created             | Security_type | Comment |
+--------------+---------------------+-----------+----------------+---------------------+---------------------+---------------+---------+
| monitordb3   | sm_creatMonthTbProc | PROCEDURE | root@localhost | 2011-11-04 16:58:11 | 2011-11-04 16:58:11 | DEFINER       |         |
| seldnsdistdb | dx_lt_proc          | PROCEDURE | root@localhost | 2011-10-11 22:23:32 | 2011-10-11 22:23:32 | DEFINER       |         |
| seldnsdistdb | tt_dns_proc         | PROCEDURE | root@localhost | 2011-10-11 22:23:32 | 2011-10-11 22:23:32 | DEFINER       |         |
| seldnsdistdb | yd_dns_proc         | PROCEDURE | root@localhost | 2011-10-11 22:23:32 | 2011-10-11 22:23:32 | DEFINER       |         |
+--------------+---------------------+-----------+----------------+---------------------+---------------------+---------------+---------+
4 rows in set, 1 warning (0.00 sec)

红色行是我们创建的

执行命令删除 存储过程

DROP PROCEDURE  sm_creatMonthTbProc

执行命令 调用创建的存储过程

call  sm_creatMonthTbProc('20111104')

执行结果我就不贴上来了 ，不会的同学试试吧。