ubuntu中mysql中的存储过程

Question

我在Window-7中用MySQL创建了存储过程。它在windows上成功运行。但是当我切换到Ubuntu时，它会在存储过程中出现错误。在windows上，我使用SQLyog创建存储过程。在Ubuntu上，我运行SQL脚本并调用那个存储过程，但是它会产生错误。下面是我的存储过程。

DELIMITER $$

USE `adserver`$$

DROP PROCEDURE IF EXISTS `getDaypartTimeDetail`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `getDaypartTimeDetail`
(currentDate DATE,noOfdays INT,cityId BIGINT)
BEGIN

DECLARE i INT;
DECLARE dateCnt INT;
SET dateCnt = 0;

DROP TEMPORARY TABLE IF EXISTS OnlyDate;
DROP TEMPORARY TABLE IF EXISTS AdvScheduleData;
CREATE TEMPORARY TABLE OnlyDate(dday DATE); 
CREATE TEMPORARY TABLE AdvScheduleData(dday BIGINT,daypartId INT,totalFile BIGINT,totalDur BIGINT); 

/* Generate Dates */
WHILE(dateCnt < noOfdays) DO
    SET i = 1;
    INSERT INTO OnlyDate(dday) VALUES (DATE_ADD(currentDate, INTERVAL dateCnt DAY));
SET dateCnt = dateCnt + 1;
END WHILE;

/* Insert all dayparts for all dates */ 
INSERT INTO AdvScheduleData (dday, daypartID) SELECT (UNIX_TIMESTAMP(dday)*1000), id FROM OnlyDate, daypart;
/* Update total files and duration */       
UPDATE AdvScheduleData SET 
    TotalFile =    (SELECT COUNT(advt_id) 
            FROM adv_schedule AdvSch
            INNER JOIN advertisement Adv ON Adv.id = AdvSch.advt_id
              WHERE AdvScheduleData.dday BETWEEN AdvSch.start_date AND AdvSch.end_date
              AND AdvSch.status = 2
              AND AdvSch.active = 1
              AND AdvSch.id IN (SELECT schedule_id FROM schedule_daypart 
               WHERE daypart_id = AdvScheduleData.daypartId )
              AND AdvSch.id IN (SELECT schedule_id FROM schedule_cities WHERE city_id = cityId)
              AND Adv.is_active = 1 
              AND Adv.is_deleted = 0
              AND Adv.status = 2 
              AND Adv.expiry_date >= AdvScheduleData.dday),
    totalDur =    (SELECT SUM(Adv.duration)
            FROM adv_schedule AdvSch
            INNER JOIN advertisement Adv ON Adv.id = ADVSCH.advt_id
              WHERE AdvScheduleData.dday BETWEEN AdvSch.start_date AND AdvSch.end_date
              AND AdvSch.status = 2
              AND AdvSch.active = 1
              AND AdvSch.id IN (SELECT schedule_id FROM schedule_daypart 
               WHERE daypart_id = AdvScheduleData.daypartId )
              AND AdvSch.id IN (SELECT schedule_id FROM schedule_cities WHERE city_id = cityId)
              AND Adv.is_active = 1 
              AND Adv.is_deleted = 0
              AND Adv.status = 2 
              AND Adv.expiry_date >= AdvScheduleData.dday);
SELECT * FROM AdvScheduleData;
END$$

DELIMITER ;

我在Ubuntu中得到的输出是

mysql> call getDaypartTimeDetail('2012-08-13',5,30534)；错误1054 (42S22)：on子句中未知列'ADVSCH.advt_id‘

Answer 1

表名和别名在Ubuntu中区分大小写。

因此，此引用提供了一个错误：

totalDur =    (SELECT SUM(Adv.duration)
        FROM adv_schedule AdvSch
        INNER JOIN advertisement Adv ON Adv.id = ADVSCH.advt_id

将其更改为AdvSch.advt_id