Mysql插入时出现未知列错误

Question

我有一个关于sql命令的问题但它之前是工作的，所以我不明白。

my table is;

CREATE TABLE `cdrConfCall` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`callingNumber` varchar(75) COLLATE utf8_unicode_ci DEFAULT NULL,
`calledNumber` varchar(75) COLLATE utf8_unicode_ci DEFAULT NULL,
`dateTimeConnect` int(11) DEFAULT NULL,
`dateTimeDisconnect` int(11) DEFAULT NULL,
`duration` int(11) DEFAULT NULL,
`origDeviceName` varchar(95) COLLATE utf8_unicode_ci DEFAULT NULL,
`destDeviceName` varchar(95) COLLATE utf8_unicode_ci DEFAULT NULL,
`jabber` int(11) DEFAULT NULL,
`confid` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`dateTimeConnectSTR` datetime DEFAULT NULL,
`dateTimeDisconnectSTR` datetime DEFAULT NULL,
`causeCode` int(11) DEFAULT NULL,
`callingName` varchar(95) COLLATE utf8_unicode_ci DEFAULT NULL,
`calledName` varchar(95) COLLATE utf8_unicode_ci DEFAULT NULL,
`catCalling` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`catCalled` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`ay` varchar(75) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

sql命令;

INSERT INTO CMOON.cdrConfCall (
callingNumber,
calledNumber,
dateTimeConnect,
dateTimeDisconnect,
duration,
origDeviceName,
destDeviceName,
jabber,
confid,
dateTimeConnectSTR,
dateTimeDisconnectSTR) 
SELECT * from
(select  '9999' as callingNumber ,'9995' as calledNumber,1625275399 as dateTimeConnect,1625275446 as dateTimeDisconnect,
'47' as duration,'xxxxx' as origDeviceName,'xxxxx' as destDeviceName,1 as jabber,'1625275399-1625275446-9999-9995' as confid,
'2021-07-03 04:23:19' as dateTimeConnectSTR,'2021-07-03 04:24:06'  as dateTimeDisconnectSTR) as tmp
WHERE NOT EXISTS (Select * From CMOON.cdrConfCall c WHERE c.dateTimeConnect=1625275399 and c.dateTimeDisconnect=1625275446 and c.callingNumber='9999' and c.calledNumber='9995') LIMIT 1

然而mysql给出以下错误信息;

错误码:1054。'字段列表'中未知列'dateTimeConnectSTR'

我不明白这个问题。关于这个问题我需要帮助，你能帮我发现问题吗?

我忘记写触发器了，所以我编辑了

CREATE DEFINER=`locku`@`%` TRIGGER `CMOON`.`cdrConfCall_BEFORE_INSERT` BEFORE INSERT ON `cdrConfCall` FOR EACH ROW
BEGIN
declare calledNumberTXT varchar(95);
declare callingNumberTXT varchar(95);
declare catCalling varchar(45);
declare catCalled varchar(45);
IF new.calledName is NULL then

set calledNumberTXT =    (SELECT displayname FROM `CMOON`.`cdrDevicelist` where ext=new.calledNumber order by id limit 1);
set catCalled =  (SELECT cat FROM `CMOON`.`cdrDevicelist` where ext=new.calledNumber order by id limit 1);
if calledNumberTXT is NULL then
set calledNumberTXT = (select displayName from `CMOON`.`cdrLdapUser` where telephoneNumber=new.calledNumber or mobileNumber = CONCAT('+9',new.calledNumber) or mobileNumber=new.calledNumber or mail = CONCAT(new.calledNumber,'@xxx') limit 1);
set catCalled = (select cat from `CMOON`.`cdrLdapUser` where telephoneNumber=new.calledNumber  or mobileNumber = CONCAT('+9',new.calledNumber) or mobileNumber=new.calledNumber  or mail = CONCAT(new.calledNumber,'@xxx') limit 1);

end if;
set new.calledName=calledNumberTXT;
set new.catCalling=catCalled;
end if;
if new.callingName is NULL then
set callingNumberTXT =    (SELECT displayname FROM `CMOON`.`cdrDevicelist` where ext=new.callingNumber order by id limit 1);
set catCalling =  (SELECT cat FROM `CMOON`.`cdrDevicelist` where ext=new.callingNumber order by id limit 1);
if callingNumberTXT is NULL then
set callingNumberTXT = (select displayName from `CMOON`.`cdrLdapUser` where telephoneNumber=new.callingNumber  or mobileNumber = CONCAT('+9',new.callingNumber)  or mobileNumber=new.callingNumber or mail = CONCAT(new.callingNumber,'@xxx') limit 1);
set catCalling =(select cat from `CMOON`.`cdrLdapUser` where telephoneNumber=new.callingNumber  or mobileNumber = CONCAT('+9',new.callingNumber) or mobileNumber=new.callingNumber or mail = CONCAT(new.callingNumber,'@xxx') limit 1);
end if;
set new.callingName=callingNumberTXT;
set new.catCalled = catCalling;
end if;
set new.ay = concat(MONTHNAME(dateTimeConnectSTR),year(dateTimeConnectSTR));
END

Answer 1

我找到了解决这个问题的方法，这个问题是由以下行触发的;

set new.ay = concat(MONTHNAME(dateTimeConnectSTR),year(dateTimeConnectSTR));

正确行;

set new.ay = concat(MONTHNAME(new.dateTimeConnectSTR),year(new.dateTimeConnectSTR));