首页 / Mysql / 深入mysql存储过程中表名使用参数传入的详解

深入mysql存储过程中表名使用参数传入的详解

BEGINdeclare date_str varchar(8);
declare table_prefix varchar(20);
set table_prefix=’mail_rare_visit_’;
set date_str = DATE_FORMAT(CURRENT_DATE(),’%Y%m%d’);
set @table_name = concat(table_prefix, date_str);
set @s = concat(‘CREATE TABLE ‘, @table_name, ‘(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `city_id` int(11) NOT NULL,
  `email` varchar(300) NOT NULL,
  `channel_id` int(11) DEFAULT NULL,
  `w_revenue` decimal(7,6) NOT NULL DEFAULT  0.000000,
  `w_buy` decimal(7,6) NOT NULL DEFAULT 0.000000,
  `w_browse` decimal(7,6) NOT NULL DEFAULT 0.000000,
  `rec_goods` varchar(800) DEFAULT NULL,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_city_id` (`user_id`),
  KEY `idx_email` (`email`),
  KEY `idx_channel_id` (`channel_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;’);

BEGIN

declare date_str varchar(8);
declare table_prefix varchar(20);
set table_prefix=’mail_rare_visit_’;
set date_str = DATE_FORMAT(CURRENT_DATE(),’%Y%m%d’);
set @table_name = concat(table_prefix, date_str);
set @s = concat(‘CREATE TABLE ‘, @table_name, ‘(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `city_id` int(11) NOT NULL,
  `email` varchar(300) NOT NULL,
  `channel_id` int(11) DEFAULT NULL,
  `w_revenue` decimal(7,6) NOT NULL DEFAULT  0.000000,
  `w_buy` decimal(7,6) NOT NULL DEFAULT 0.000000,
  `w_browse` decimal(7,6) NOT NULL DEFAULT 0.000000,
  `rec_goods` varchar(800) DEFAULT NULL,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_city_id` (`user_id`),
  KEY `idx_email` (`email`),
  KEY `idx_channel_id` (`channel_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;’);

prepare stmt from @s;
execute stmt;

DEALLOCATE prepare stmt;

end

本文来自网络,不代表我是攻城狮立场,转载请注明出处:https://www.54gcs.cn/sql/mysql/1358.html

helwo作者

上一篇
下一篇

为您推荐

发表评论

联系我们

联系我们

在线咨询: QQ交谈

邮箱: web@5vo.cn

工作时间:周一至周五,9:00-17:30,节假日休息
关注微信
微信扫一扫关注我们

微信扫一扫关注我们

手机访问
手机扫一扫打开网站

手机扫一扫打开网站

返回顶部
跳至工具栏