MySql IN踩過的坑

private DynamicParameters GetParameters()
        {
            var parameters = new DynamicParameters();
            if (request.ProductId > 0)
            {
                parameters.Add("@ID", request.ProductId);
            }
            else
            {
                parameters.Add("@ID", null);
            }
            if (!string.IsNullOrEmpty(request.ProductName))
            {
                parameters.Add("@Name", string.Format("%{0}%", request.ProductName));
            }
            else
            {
                parameters.Add("@Name", null);
            }

            if (request.FK_StartCitys != null && request.FK_StartCitys.Any())
            {
                parameters.Add("@FK_StartCitys", string.Join(",", request.FK_StartCitys));
            }
            else
            {
                parameters.Add("@FK_StartCitys", null);
            }
            if (request.FK_Areas != null && request.FK_Areas.Any())
            {
                parameters.Add("@FK_Areas", string.Join(",", request.FK_Areas));
            }
            else
            {
                parameters.Add("@FK_Areas", null);
            }
            if (request.FK_DestinationPlays != null && request.FK_DestinationPlays.Any())
            {
                parameters.Add("@FK_DestinationPlays", string.Join(",", request.FK_DestinationPlays));
            }
            else
            {
                parameters.Add("@FK_DestinationPlays", null);
            }
            if (request.Ranks != null && request.Ranks.Any())
            {
                parameters.Add("@Rank", string.Join(",", request.Ranks));
            }
            else
            {
                parameters.Add("@Rank", null);
            }
            DateTime startDate;
            if (!string.IsNullOrEmpty(request.StartDateB) && DateTime.TryParse(request.StartDateB, out startDate))
            {
                parameters.Add("@StartDateB", startDate);
            }
            else
            {
                parameters.Add("@StartDateB", null);
            }

            DateTime endDate;
            if (!string.IsNullOrEmpty(request.StartDateE) && DateTime.TryParse(request.StartDateE, out endDate))
            {
                endDate = endDate.AddDays(1).AddMilliseconds(-1);
                parameters.Add("@StartDateE", endDate);
            }
            else
            {
                parameters.Add("@StartDateE", null);
            }
            return parameters;
        }

        private string GetWhereExt()
        {
            var whereExt = string.Empty;
           
            if (request.FK_DestinationPlays != null && request.FK_DestinationPlays.Any())
            {

                whereExt += string.Format(" AND pext.FK_DestinationPlay IN ({0}) ", string.Join(",", request.FK_DestinationPlays));
            }
            return whereExt;
        }
           

SELECT COUNT(1) FROM
(SELECT p.ID
FROM product p
LEFT JOIN productext pext ON pext.FK_Product=p.ID
LEFT JOIN rank r ON r.ID = pext.FK_Rank
LEFT JOIN productsalescity sc ON sc.FK_Product=p.ID AND sc.IsValid='T'
INNER JOIN tour t ON t.FK_Product=p.ID AND t.IsValid='T' AND t.IsShowH5='T' AND t.CanBooking='T'
	AND t.ExpireDate>=NOW() AND t.DeadLineDate>= NOW() AND t.`Status`=2 AND IFNULL(t.IsLock,'F')<>'T'
LEFT JOIN tourminpriceinventory tmpi ON tmpi.FK_Tour=t.ID
WHERE p.IsValid='T' AND p.Category IN (1,2,3)
AND p.ID=IFNULL(@ID,p.ID)
AND p.name LIKE IFNULL(@Name,p.name)
AND IFNULL(pext.FK_DestinationPlay,0) IN (IFNULL(@FK_DestinationPlays,IFNULL(pext.FK_DestinationPlay,0)))
AND IFNULL(pext.FK_Rank,0) IN (IFNULL(@Rank,IFNULL(pext.FK_Rank,0)))
AND IFNULL(sc.FK_City,0) IN (IFNULL(@FK_StartCitys,IFNULL(sc.FK_City,0)))
AND IFNULL(p.FK_Area,0) IN (IFNULL(@FK_Areas,IFNULL(p.FK_Area,0)))
AND t.StartDate >= IFNULL(@StartDateB,t.StartDate)
AND t.StartDate <= IFNULL(@StartDateE,t.StartDate)
#{0}
GROUP BY p.ID
) AS A;
           

變量@FK_DestinationPlays的值為：194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,306,307,308,309,349,377

其他變量均為null；

當采用寫法：AND IFNULL(pext.FK_DestinationPlay,0) IN (IFNULL(@FK_DestinationPlays,IFNULL(pext.FK_DestinationPlay,0)))

時，會自動将變量值添加引号，造成僅比對到FK_DestinationPlay為194的資料，造成sql查詢結果不準确；正确寫法為調用方法GetWhereExt，即：

AND pext.FK_DestinationPlay IN (194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,306,307,308,309,349,377)