mysql - DATEDIFF 不在触发器内相乘

Question

大家好，我这里有一个触发器来计算每个客户的发票。所以我想根据他们租车的天数查看应付金额!

DELIMITER //
CREATE TRIGGER update_booking_price_day 
BEFORE update ON bookings
FOR EACH ROW BEGIN 
SET NEW.TheDuration  = DATEDIFF(NEW.end_date, NEW.start_date);
SET NEW.AMOUNT_DUE  = DATEDIFF(NEW.end_date, NEW.start_date);  * SELECT rate_per_day FROM vehicles WHERE vehicle_id=NEW.vehicle_id; 
END;
//
DELIMITER ; 

表结构

   CREATE TABLE  bookings  (
   booking_id  int(50)  NOT NULL AUTO_INCREMENT,
   booking_date  date NOT NULL,
   start_date  date NOT NULL,
   end_date  date NOT NULL,
   invoice_no int(10)  NOT NULL ,
   chauffeur_id int(10) NULL,
   vehicle_id int(10)  NOT NULL ,
   customer_id int(50) NOT NULL ,
   chauffeur_req ENUM('Yes','No') NOT NULL,
   special_instructions varchar(255) NOT NULL,
   PRIMARY KEY (booking_id),
   KEY invoice_nofk2 (invoice_no),
   KEY chauffeur_idfk1 (chauffeur_id),
   KEY customer_idfk2 (customer_id),
   KEY vehicle_idfk2 (vehicle_id),
   CONSTRAINT invoice_nofk1 FOREIGN KEY (invoice_no) REFERENCES invoice (invoice_no),
   CONSTRAINT chauffeur_idfk2 FOREIGN KEY (chauffeur_id) REFERENCES chauffeurs (chauffeur_id),
   CONSTRAINT customer_idfk3 FOREIGN KEY (customer_id) REFERENCES customers (customer_id), 
   CONSTRAINT vehicle_idfk3 FOREIGN KEY (vehicle_id) REFERENCES vehicles (vehicle_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

和

ALTER TABLE bookings
ADD COLUMN TheDuration varchar(10) NOT NULL;

ALTER TABLE bookings
ADD COLUMN AMOUNT_DUE varchar(16) NOT NULL; 

和

CREATE TABLE  vehicles (
   vehicle_id  int(10)  NOT NULL AUTO_INCREMENT,
   category ENUM('Sedan','Hatchback','SUV', 'Coupe', 'Crossover') NOT NULL,
   no_of_seats  int(11) NOT NULL,
   brand  varchar(20) NOT NULL,
   model  varchar(20) NOT NULL,
   product_year  int(5) NOT NULL,
   rate_per_day  int(11) NOT NULL,
   PRIMARY KEY (vehicle_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我收到一条错误消息...

1064 - 您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在 '* SELECTrate_per_day FROM cars WHEREvehicle_id=NEW.vehicle_id;

附近使用的正确语法

END' 在第 5 行

Answer 1

您必须删除 DATEDIFF(NEW.end_date, NEW.start_date) 之后的 ; 并将 select 语句括在括号内:

SET NEW.AMOUNT_DUE  = 
  DATEDIFF(NEW.end_date, NEW.start_date) * 
  (SELECT rate_per_day FROM vehicles WHERE vehicle_id=NEW.vehicle_id);

如果 select 语句仅返回 1 行，这将起作用。