我需要使用cakephp和mysql将字符串传递给存储过程。
这是我要传递给存储过程的字符串，
PlantingBlock.id IN (13,10)
下面是我的存储过程，
SET whereClause = " AND 1 = 1 ";
IF(in_clause IS NULL OR in_clause = '') THEN
SET whereClause = CONCAT(whereClause, ' AND ',in_clause);
END IF;
SET statement = 'SELECT PlantingBlock.* FROM (
SELECT PB.*,
B.block,
V.variety,
ST.size_type,
PLA.date AS plant_date,
PLA.week_no,
C.crop,
C.id AS crop_id
FROM lf_planting_blocks AS PB,
lf_blocks AS B,
lf_properties AS P,
lf_property_types AS PT,
lf_size_types AS ST,
lf_varieties AS V,
lf_plantings AS PLA,
lf_crops AS C
WHERE
PB.block_id = B.id
AND B.property_id = P.id
AND P.property_type_id = PT.id
AND B.size_type_id = ST.id
AND PB.variety_id = V.id
AND V.crop_id = C.id
AND PB.planting_id = PLA.id
AND PB.plant_still_active = true
AND B.is_deleted = false
AND P.is_deleted = false
AND PT.is_deleted = false
AND ST.is_deleted = false
AND V.is_deleted = false
AND C.is_deleted = false';
SET statement = CONCAT(statement, whereClause, ') AS PlantingBlock ORDER BY PlantingBlock.plant_date DESC');
这将给出一个名为SQLSTATE[42S22]的错误：未找到列：1054字段列表中的未知列PlantingBlock.id
问题是，在引用id列时，您试图使用提供给此子查询的别名向内部select添加条件。
因此，您可以用括号关闭子查询，并将WHERE子句应用于外部SELECT
SELECT PlantingBlock.*
FROM
(
...
) AS PlantingBlock
WHERE PlantingBlock.id IN (13,10)
ORDER BY PlantingBlock.plant_date DESC
或者更好的是，只需在内部选择中引用id字段。
SELECT PlantingBlock.*
FROM
(
...
AND PB.id IN (13,10)
) AS PlantingBlock
ORDER BY PlantingBlock.plant_date DESC
