存储过程在windows下正常，在linux下报错1312
创建了存储过程，根据设备编号查询是否存在表，如果存在插入数据，如果不存在新建表后插入数据，现在遇到的情况是该存储过程在windows下可以正常，在linux下报错1312. 请教下如何解决。CREATE PROCEDURE `RTB_SetDeviceInfovalue`(userid varchar(40),deviceID varchar(40),StartTime datetime,EndTime datetime,StepCount INT(4),Length decimal(6,2),Pace decimal(6,2),Calor decimal(6,2),sb int,CreateTime datetime)
BEGIN
DECLARE TableName varchar(40);#表名
DECLARE SqlStr varchar(4000); #生成表sql
DECLARE TableExis varchar(40);#查询表是否存在
DECLARE InsertSqlStr varchar(4000); #生成表sql
SELECT RTBTableName INTO TableName FROM org_deviceinfo WHERE Mid=userid LIMIT 1;#查询出表名
IF ISNULL(TableName) THEN #表为空，重新生成表名，并更新设备表
SET TableName = CONCAT('RTB_',replace(replace(replace(Now(),'-',''),':',''),' ',''),left(RAND() * 100000000000,4)); #生成表名
INSERT INTO org_deviceinfo (RTBTableName,Mid,SerialNo) VALUES (TableName,userid,deviceID);
#UPDATE org_deviceinfo SET RTBTableName = TableName WHERE Uid=deviceID; # 更新设备表
END IF;
SELECT `TABLE_NAME` INTO TableExis from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA`='ablt2010' and `TABLE_NAME`=TableName LIMIT 1; #查询表是否存在
IF ISNULL(TableExis) THEN#不存在 ，建立新表
SET sqlstr = CONCAT("CREATE TABLE ",TableName,"(id int(4) NOT NULL AUTO_INCREMENT COMMENT 'id' PRIMARY KEY,StartTime datetime DEFAULT NULL COMMENT '开始时间',EndTime datetime DEFAULT NULL COMMENT '结束时间',StepCount INT(4) DEFAULT '0' COMMENT '步数',Length decimal(6,2) DEFAULT '0' COMMENT '长度',Pace decimal(6,2) DEFAULT 0 COMMENT '步速',Calor decimal(6,2) DEFAULT 0 COMMENT '卡',Flagint NULL DEFAULT 0 COMMENT 'Flag',CreateTime datetime DEFAULT NULL,INDEX CreateTime (CreateTime) USING BTREE);");
SET @sqlstr = sqlstr;
prepare stmt from @SqlStr;
EXECUTE stmt;
deallocate prepare stmt;
END IF;
#以下是插入数据
SET InsertSqlStr = CONCAT("INSERT INTO ",TableName," (StepCount,Length,Pace,Calor,StartTime,EndTime,CreateTime) VALUES ('",StepCount,"','",Length,"','",Pace,"','",Calor,"','",StartTime,"','",EndTime,"','",CreateTime,"')");
SET @InsertSqlStr = InsertSqlStr;
prepare stmt from @InsertSqlStr;
EXECUTE stmt;
deallocate prepare stmt;
#CALL RTB_SetDeviceInfovalue_DayData(userid,StartTime,StepCount,Length,Pace,Calor,CreateTime);
END
页:
[1]