php - 未知的 mysql 语法错误代码在工作台中运行良好，但在 php mysqli 中抛出错误

Question

我必须创建一个事务来实现我的目标，但 php 抛出一些错误说

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 'set @reservation_id = NULL; set @guest_id_0 =34712; set @biller_id =@guest_i' at line 1

代码:

       START TRANSACTION;
   -- Start Global Variables 

   set @biller_id = NULL;
   set @reservation_id = NULL;
   -- End Global Variables 

   -- Start Guest 0 Insert/Select 

   set @guest_id_0 =34712;
   set @biller_id =@guest_id_0;
   -- End Guest 0 Insert/Select 

   -- Start Guest 1 Insert/Select 

   set @guest_id_1 =43290;
   -- End Guest 1 Insert/Select 

   --- Start Reservation Insert 

   INSERT INTO `reservations`(
   `property_id`,
   `agent_id`,
   `guest_id`,
   `total_amount`,
   `discount`,
   `total_payable`,
   `current_status`,
   `booking`,
   `active`,
   `created`,
   `updated`)
   VALUES(
   1000,
   NULL,
   @biller_id,
   25600,
   0,
   0,
   'confirm',
   'group',
   1,
   UTC_TIMESTAMP(),
   UTC_TIMESTAMP());
   set @reservation_id = LAST_INSERT_ID();
   -- End Reservation Insert 

   -- Start ReservationRoom 0 Insert 

   INSERT INTO `reservation_rooms`(
   `reservation_id`,
   `guest_id`,
   `room_id`,
   `rent_id`,
   `checkin`,
   `checkout`,
   `start`,
   `end`,
   `status`)
   VALUES(
   @reservation_id,
   @guest_id_0,
   1057,
   1000,
   NULL,
   NULL,
   '2015-08-24',
   '2015-08-24',
   'confirm');
   set @reservation_room_0 = LAST_INSERT_ID();
   -- Start ReservationRoomCharge 0 Insert 

   INSERT INTO `reservation_room_charges`(
   `reservation_room_id`,
   `name`,
   `quantity`,
   `cost`)
   VALUES(
   @reservation_room_0,
   'Basic',
   4,
   3200.00);
   -- End ReservationRoomCharge 0 Insert 

   -- Start ReservationRoomCharge 0 Insert 

   INSERT INTO `reservation_room_charges`(
   `reservation_room_id`,
   `name`,
   `quantity`,
   `cost`)
   VALUES(
   @reservation_room_0,
   'Extra Adult',
   0,
   0);
   -- End ReservationRoomCharge 0 Insert 

   -- Start ReservationRoomCharge 0 Insert 

   INSERT INTO `reservation_room_charges`(
   `reservation_room_id`,
   `name`,
   `quantity`,
   `cost`)
   VALUES(
   @reservation_room_0,
   'Extra Child',
   0,
   0);
   -- End ReservationRoomCharge 0 Insert 

   -- Start ReservationRoomCharge 0 Insert 

   INSERT INTO `reservation_room_charges`(
   `reservation_room_id`,
   `name`,
   `quantity`,
   `cost`)
   VALUES(
   @reservation_room_0,
   'Extra Bed',
   0,
   0);
   -- End ReservationRoomCharge 0 Insert 

   -- End ReservationRoom 0 Insert 

   -- Start ReservationRoom 1 Insert 

   INSERT INTO `reservation_rooms`(
   `reservation_id`,
   `guest_id`,
   `room_id`,
   `rent_id`,
   `checkin`,
   `checkout`,
   `start`,
   `end`,
   `status`)
   VALUES(
   @reservation_id,
   @guest_id_1,
   1060,
   1000,
   NULL,
   NULL,
   '2015-08-24',
   '2015-08-24',
   'confirm');
   set @reservation_room_1 = LAST_INSERT_ID();
   -- Start ReservationRoomCharge 1 Insert 

   INSERT INTO `reservation_room_charges`(
   `reservation_room_id`,
   `name`,
   `quantity`,
   `cost`)
   VALUES(
   @reservation_room_1,
   'Basic',
   4,
   3200.00);
   -- End ReservationRoomCharge 1 Insert 

   -- Start ReservationRoomCharge 1 Insert 

   INSERT INTO `reservation_room_charges`(
   `reservation_room_id`,
   `name`,
   `quantity`,
   `cost`)
   VALUES(
   @reservation_room_1,
   'Extra Adult',
   0,
   0);
   -- End ReservationRoomCharge 1 Insert 

   -- Start ReservationRoomCharge 1 Insert 

   INSERT INTO `reservation_room_charges`(
   `reservation_room_id`,
   `name`,
   `quantity`,
   `cost`)
   VALUES(
   @reservation_room_1,
   'Extra Child',
   0,
   0);
   -- End ReservationRoomCharge 1 Insert 

   -- Start ReservationRoomCharge 1 Insert 

   INSERT INTO `reservation_room_charges`(
   `reservation_room_id`,
   `name`,
   `quantity`,
   `cost`)
   VALUES(
   @reservation_room_1,
   'Extra Bed',
   0,
   0);
   -- End ReservationRoomCharge 1 Insert 

   -- End ReservationRoom 1 Insert 

   -- Start ReservationPayment 1 Insert 

   INSERT INTO `reservation_payments`(
   `reservation_id`,
   `amount`,
   `payment_date`)
   VALUES(
   @reservation_id,
   5000,
   '2015-08-21');
   -- End ReservationPayment 1 Insert 

   COMMIT;

Answer 1

您的 SQL 代码由单独的 SQL 查询组成。
因此，通过单独的 mysqli::query 调用运行它们。
就像每个 PHP 应用程序一样。

虽然事务与 mysqli::query 调用完全无关。在知道需要这样的机制之前您必须先了解事务是什么。