newbie's questions

我有以下查询。

SELECT Day(vehicleTrip.startDateTime) As day, 
       Month(vehicleTrip.startDateTime) As month,
       Sum(TIMESTAMPDIFF(SECOND, startDateTime, endDateTime )) As totalDuration,
       count(vehicleTrip.startDateTime) As totalTrip, 
       sum(endMileage-startMileage) As totalMleage
FROM vehicleTrip                            
WHERE vehicleTrip.vehicleID=:vehicleID
  AND vehicleTrip.vehicleEndCodeID!=1
  AND vehicleTrip.startDateTime BETWEEN :startDateTime And :endDateTime                             
Group By Day(vehicleTrip.startDateTime),Month(vehicleTrip.startDateTime)

目前它工作正常，因为它可以按每日日期分组duration, totalTrip, totalMileage etc.但唯一的问题是我希望日期由 desc 安排。我试过按它给出的 startDateTime desc 下订单me error that its not in GROUP By clause and containts nonaggregated column。我已经尝试了很多方法，比如把它Max(vehicleTrip.startDateTime)还是一样的。我怎样才能最好地解决这个问题？

下面是我的表格设计及其索引。

CREATE TABLE `vehicleTrip` (
  `vehicleEndCodeID` tinyint NOT NULL DEFAULT '1',
  `vehicleID` mediumint NOT NULL,
  `startDateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `endDateTime` timestamp NULL DEFAULT NULL,
  `startMileage` float(11,3) DEFAULT NULL,
  `endMileage` float(11,3) DEFAULT NULL,
  `startLatitude` float(10,8) NOT NULL,
  `startLongitude` float(11,8) NOT NULL,
  `endLatitude` float(10,8) DEFAULT NULL,
  `endLongitude` float(11,8) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


ALTER TABLE `vehicleTrip`
  ADD PRIMARY KEY (`vehicleID`,`startDateTime`) USING BTREE;
COMMIT;

我的表格如下所述。

1. orders是将存储所有详细信息的主表，例如客户ID、商家ID、分配给哪个骑手ID。订单详细信息，如总计、小计、税收以及客户详细信息，如地址纬度、经度。它还保存了每个细节，例如订单被接受、准备好、交付的时间。

CREATE TABLE `orders` (
  `orderID` int UNSIGNED NOT NULL,
  `orderIDHash` varchar(10) NOT NULL,
  `customerID` mediumint UNSIGNED NOT NULL,
  `merchantID` smallint UNSIGNED NOT NULL,
  `branchID` smallint UNSIGNED NOT NULL,
  `riderID` smallint UNSIGNED NOT NULL,
  `orderDateTime` timestamp NOT NULL,
  `subTotal` decimal(10,0) NOT NULL,
  `tax` decimal(10,0) NOT NULL,
  `total` decimal(10,0) NOT NULL,
  `quantity` tinyint NOT NULL,
  `customerAddress` varchar(150) NOT NULL,
  `customerLatitude` float NOT NULL,
  `customerLongitude` float NOT NULL,
  `paymentID` varchar(30) NOT NULL,
  `paymentType` tinyint NOT NULL,
  `instructions` varchar(100) NOT NULL,
  `orderType` tinyint NOT NULL,
  `neworderNotify` tinyint NOT NULL,
  `orderAcceptedDateTime` timestamp NOT NULL,
  `merchantUserAcceptedID` smallint NOT NULL DEFAULT '0',
  `orderAcceptedNotify` tinyint NOT NULL DEFAULT '0',
  `orderReadyDateTime` timestamp NOT NULL,
  `merchantUserReadyID` smallint NOT NULL DEFAULT '0',
  `orderReadyNotify` tinyint NOT NULL DEFAULT '0',
  `orderInDeliveryDateTime` timestamp NOT NULL,
  `orderInDeliveryNotify` tinyint NOT NULL DEFAULT '0',
  `orderPickerOrDeliveryDateTime` timestamp NOT NULL,
  `orderCancelDateTime` timestamp NOT NULL,
  `orderStatus` tinyint NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

ALTER TABLE `orders`
  ADD PRIMARY KEY (`orderID`),
  ADD UNIQUE KEY `orderIDHash` (`orderIDHash`),
  ADD KEY `customerID` (`customerID`),
  ADD KEY `merchantID` (`merchantID`),
  ADD KEY `riderID` (`riderID`),
  ADD KEY `branchID` (`branchID`),
  ADD KEY `orderStatus` (`orderStatus`),
  ADD KEY `orderAcceptedNotify` (`orderAcceptedNotify`),
  ADD KEY `orderReadyNotify` (`orderReadyNotify`),
  ADD KEY `orderInDeliveryNotify` (`orderInDeliveryNotify`);


ALTER TABLE `orders`
  MODIFY `orderID` int UNSIGNED NOT NULL AUTO_INCREMENT;
COMMIT;

2. 第二个表是ordersLine。该表基本上存储与一个特定订单相关的所有项目。因此它与上面的订单表有关。

CREATE TABLE `orderLine` (
  `orderID` int NOT NULL,
  `itemID` mediumint NOT NULL,
  `itemQuantity` tinyint NOT NULL,
  `itemPrice` decimal(10,0) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

ALTER TABLE `orderLine`
  ADD PRIMARY KEY (`orderID`,`itemID`);
COMMIT;

3. CustomerMessageLog 基本上是例如上面的订单表，我们有这个字段 orderAcceptedNotify 它将更改其值，例如默认为 0，然后更改为 1，表示商家接受订单，当订单已通知用户时更改为 2。因此，我们将消息存储在此日志中，因此当用户登录时，他可以看到所有消息。不同类型的消息可以有不同的 messageID。

CREATE TABLE `customerMessageLog` (
  `orderID` int NOT NULL,
  `customerID` mediumint NOT NULL,
  `messageID` tinyint NOT NULL,
  `messageDateTime` timestamp NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


ALTER TABLE `customerMessageLog`
  ADD PRIMARY KEY (`orderID`,`customerID`,`messageID`);
COMMIT;

4. 该表用于存储所有通知给骑手的消息。

CREATE TABLE `riderMessageLog` (
  `orderID` int NOT NULL,
  `riderID` smallint NOT NULL,
  `messageID` tinyint NOT NULL,
  `messageDateTime` timestamp NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
ALTER TABLE `riderMessageLog`
  ADD PRIMARY KEY (`orderID`,`riderID`,`messageID`);
COMMIT;

5. 该表用于存储所有通知给商家的消息。

CREATE TABLE `merchantMessageLog` (
  `orderID` int NOT NULL,
  `merchantID` smallint NOT NULL,
  `branchID` smallint NOT NULL,
  `messageID` tinyint NOT NULL,
  `messageDateTime` timestamp NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


ALTER TABLE `merchantMessageLog`
  ADD PRIMARY KEY (`orderID`,`merchantID`,`branchID`,`messageID`);
COMMIT;

6. 这是将存储所有 orderStatus 以及上面大多数表中的 messageID 的表通过连接引用此表。

CREATE TABLE `statusMessage` (
  `statusID` tinyint UNSIGNED NOT NULL,
  `message` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `statusMessage`
--

INSERT INTO `statusMessage` (`statusID`, `message`) VALUES
(0, 'Pending'),
(1, 'Payment Completed'),
(2, 'Accepted'),
(3, 'Completed'),
(4, 'Self Picked Up'),
(5, 'Assigned Rider'),
(6, 'In Delivery'),
(7, 'Delivered');


ALTER TABLE `statusMessage`
  ADD PRIMARY KEY (`statusID`);

所以现在的挑战是我已将 orderID 设置为 DDMMYYHHMMSS，这是我在提交订单进行付款之前从用户手机获得的。为什么我这样做是支付网关的原因，我需要 orderID。另一方面，我也可以将其设置为自动增量，也可以在付款前插入所有订单和 orderLine，但如果付款失败，那就是另一个问题了处理插入的订单和订单线。那么哪种方法最有效。我还想在向用户显示主要内容时对其进行哈希处理？

我正在mysql 8上尝试以下插入查询。

Insert Into fence Set       
            fenceName='aa',
            radius=2,                   
            fenceGeometry=ST_GeomFromText('POINT(102.1893310546875 3.880696482497261)', 4326)

它在 mysql 5.7 上完美运行，但在 mysql 8 中我得到了这个error Latitude 102.189331 is out of range in function st_geomfromtext. It must be within [-90.000000, 90.000000].

根据一些建议，我也这样做了。

ALTER TABLE fence MODIFY fenceGeometry geometry NOT NULL SRID 4326;

但它给了我同样的结果。