delimiter //
CREATE PROCEDURE `sp_user` (IN `lastts` timestamp)
BEGIN
CREATE TABLE IF NOT EXISTS `tmp_user`
(
........
) ENGINE=MyISAM DEFAULT CHARSET=utf8; /* 删除动态变化的用户资料 */
IF lastts = '1970-01-01' THEN truncate table tmp_user;
ELSE delete from tmp_user where lasttime >= lastts;
END IF; /* 初始化用户 */
insert into tmp_user (...........)
select * from (
select distinct * from `tb_order` where date(pay_time) >= lastts group by buyer_nick
) as t;
END
//
delimiter;
代码如上，是一个可以完整执行的SQL脚本。自己想做成一个安装文件。存储过程始终无法正确执行。这个函数改如何写求教。
CREATE PROCEDURE `sp_user` (IN `lastts` timestamp)
BEGIN
CREATE TABLE IF NOT EXISTS `tmp_user`
(
........
) ENGINE=MyISAM DEFAULT CHARSET=utf8; /* 删除动态变化的用户资料 */
IF lastts = '1970-01-01' THEN truncate table tmp_user;
ELSE delete from tmp_user where lasttime >= lastts;
END IF; /* 初始化用户 */
insert into tmp_user (...........)
select * from (
select distinct * from `tb_order` where date(pay_time) >= lastts group by buyer_nick
) as t;
END
//
delimiter;
代码如上，是一个可以完整执行的SQL脚本。自己想做成一个安装文件。存储过程始终无法正确执行。这个函数改如何写求教。
所以同名的存储过程被重新定义时将会出错
你的脚本中没有删除 sp_user 的指令，估计就是这个原因
SQL:delimiter // CREATE PROCEDURE `sp_user` (IN `lastts` timestamp) BEGIN CREATE TABLE IF NOT EXISTS `tmp_user` ( `seller_uid` varchar(55) CHARACTER SET utf8 COLLATE utf8_general_ci, `buyer_nick` varchar(125) CHARACTER SET utf8 COLLATE utf8_general_ci default NULL, `buyer_email` varchar(125) default NULL, `receiver_name` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci, `receiver_state` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci, `receiver_city` varchar(125) CHARACTER SET utf8 COLLATE utf8_general_ci default NULL, `receiver_district` varchar(125) NOT NULL default '', `receiver_address` varchar(255) NOT NULL default '', `receiver_mobile` varchar(25) NOT NULL default '', `receiver_phone` varchar(25) NOT NULL default '', `lasttime` date NULL default '0000-00-00', `totalorder` int(10) NOT NULL default '0', `totalpayment` decimal(10,2) NOT NULL default '0.00', `unitprice` decimal(10,2) NOT NULL default '0.00', `refer` varchar(15) default NULL, `year` varchar(4) default NULL, `level` enum('normal','black','vip') NOT NULL default 'normal', `tc` timestamp NULL default '0000-00-00 00:00:00', KEY `IX_TMPUSER_SELLERUID` (`seller_uid`), KEY `IX_TMPUSER_BUYERNICK` (`buyer_nick`), KEY `IX_TMPUSER_BUYEREMAIL` (`buyer_email`), KEY `IX_TMPUSER_RECEIVERNAME` (`receiver_name`), KEY `IX_TMPUSER_STATE` (`receiver_state`), KEY `IX_TMPUSER_CITY` (`receiver_city`), KEY `IX_TMPUSER_DISTRICT` (`receiver_district`), KEY `IX_TMPUSER_LASTIME` (`lasttime`), KEY `IX_TMPUSER_TOTALORDER` (`totalorder`), KEY `IX_TMPUSER_TOTALPAYMENT` (`totalpayment`), KEY `IX_TMPUSER_UNITPRICE` (`unitprice`), KEY `IX_TMPUSER_REFER` (`refer`), KEY `IX_TMPUSER_YEAR` (`year`), KEY `IX_TMPUSER_LEVEL` (`level`), KEY `IX_TMPUSER_TC` (`tc`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; /* 删除动态变化的用户资料 */ IF lastts = '1970-01-01' THEN truncate table tmp_user; ELSE delete from tmp_user where lasttime >= lastts; END IF; /* 初始化用户 */ insert into tmp_user ( `seller_uid`,`buyer_nick`,`buyer_email`,`receiver_name`,`receiver_state`, `receiver_city`,`receiver_district`,`receiver_address`,`receiver_mobile`,`receiver_phone`,`lasttime`, `totalorder`,`totalpayment`,`unitprice`,`refer`,`year`,`level`,`tc`) select * from ( select distinct seller_uid,buyer_nick,buyer_email,receiver_name,receiver_state, receiver_city,receiver_district,receiver_address,receiver_mobile,receiver_phone,date(max(pay_time)) as lasttime, count(oid) as totalcount,sum(payment) as totalpayment,sum(payment) / sum(nums) as unitprice,refer,year(now()) as `year`,'normal',now() as tc from `tb_order` where date(pay_time) >= lastts group by buyer_nick ) as t; END // delimiter;
Error:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delimiter //\x0ACREATE PROCEDURE `sp_user` (IN `lastts` timestamp)\x0ABEGIN\x0A\' at line 1
Errno:1064
Errno:1064不知道你用的是什么编辑器。为何换行符是 0x0a