php - 使用 mysql 存储函数和选择查询

Question

首先让我解释一下我的目的，我有一个车辆预订应用程序，访问者将在其中添加旅程的开始日期和结束日期，在数据库中有可用的司机列表(available_from_date 和 available_to_date)，这是一种在它们运行的​​持续时间中，有一个排除日期字段用于表示它们不工作时的某些特定日期。该应用程序需要查找在用户输入的行程日期期间可用的车辆列表。例如，用户输入他想要在2014年9月13日至2014年9月17日期间从地点A前往B那么数据库需要返回该时间段内可用的出租车列表，并且该时间段内不得有任何排除日期。

现在我已将排除日期以逗号分隔的格式存储在表中(我可以创建一个单独的表，但执行查询将花费更多时间)我试图创建一个 mysql 函数，该函数将在实际搜索查询中调用，如果在持续时间内存在一些排除的日期，则返回 true，如果没有，则返回 false。

这些是我写的查询

SELECT id, exclude_dates  
FROM `taxi_route` 
WHERE  status = 1 
  AND `to_city` = 'Surat' 
  AND `from_city` = 'Ahmedabad' 
  AND `trip_type` = 2 
  AND `available_from_date` <= '2014-09-13' 
  AND available_to_date >= '2014-09-17' 
  AND STR_TO_DATE((SELECT `split`(exclude_dates, ',', 1)),'%d-%m-%Y') 
      NOT BETWEEN STR_TO_DATE('13-09-2014','%d-%m-%Y') 
              AND STR_TO_DATE('17-09-2014','%d-%m-%Y')

Split是我在mysql中创建的一个函数，用于分隔以逗号格式显示的日期

DELIMITER $$

CREATE FUNCTION split( str VARCHAR(500), delchar VARCHAR(2), x INT )
RETURNS VARCHAR(500)
BEGIN
RETURN SUBSTR(SUBSTRING_INDEX(str, delchar, x),
LENGTH(SUBSTRING_INDEX(str, delchar, x-1))+IF(x > 1, 2, 1));
END$$

DELIMITER ;

只要我在 split(exclude_dates, ',', 1) 中传递 1 ，此方法就可以正常工作，但如果 except_dates 具有多个日期，那么这将不起作用有人可以建议或指导如何实现这一点吗？数据库快照在这里http://i.imgur.com/JaI8MSx.png

Answer 1

与为排除日期定义单独的表相比，您的查询很可能需要更多时间来执行。在列中使用逗号分隔列表进行搜索并不是一个好的做法，这违反了规范化规则。

您应该单独定义表(例如，taxi、taxi_route、taxi_route_exclusion、route_exclusion)，然后添加必要的索引以使搜索更加高效。

示例:

taxi---------idcountry*********

taxi_route-------------------idtaxi_idavailable_from_dateavailable_to_datefrom_cityto_city

route_exclusion---------------idtaxi_idexclusion_date

And also add a relation table between taxi_route and route_exclusion tables to represent many-to-many relationship. Later define foreign keys on taxi_route_route_exclusion table to point taxi_route and route_exclusion tables.

taxi_route_route_exclusion--------------------------taxi_route_idroute_eclusion_id

Define foreign keys like:

• taxi_route.taxi_id -> taxi.id
• taxi_route_route_exclusion.taxi_route_id -> taxi_route.id
• taxi_route_route_exclusion.route_exclusion_id -> route_exclusion.id

Define indexes like:

• taxi: IX1 (status, trip_type)
• taxi_route: IX1(to_city, from_city, available_from_date, available_to_date)

Your final query should look like this:

SELECT tr.id, re.exclusion_date
FROM `taxi_route` tr JOIN `taxi_route_route_exclusion` trre
  ON tr.id = trre.taxi_route_id
JOIN `route_exclusion` re
  ON re.id = trre.route_exclusion_id
JOIN `taxi` t
  ON t.id = tr.id
WHERE
  t.status = 1
  AND t.trip_type = 2 
  AND tr.to_city = 'Surat' 
  AND tr.from_city = 'Ahmedabad' 
  AND tr.available_from_date <= '2014-09-13' 
  AND tr.available_to_date >= '2014-09-17'