MySQL存储过程调用报1054错误：未知列'created_at'，求PHP调用方案

问题场景 #

调用存储过程projects_view时传入条件"(p.created_at >= '2023-09-01' AND p.created_at <= '2023-09-17')"，触发mysqli_sql_exception错误，错误码1054，提示未知列created_at。

错误信息 #

Type: mysqli_sql_exception
Message: Unknown column 'created_at' in 'field list'
call projects_view(20,0, "(p.created_at >= '2023-09-01' AND p.created_at <= '2023-09-17')") Error Code: 1054. Unknown column 'created_at' in 'field list'

存储过程代码 #

CREATE DEFINER=`root`@`localhost` PROCEDURE `projects_view`(IN 
  `lmt` INT, IN `ofst` INT, IN vfilter VARCHAR(1000))
BEGIN
    declare declared_in_param varchar(1000);
    declare lim int;
    declare ofs int;
    set @declared_in_param = vfilter;
    set @lim = lmt;
    set @ofs = ofst;
    SET @sql = CONCAT("SELECT p.*
        ,b.id as brand
        ,b.name as brandname
        ,j.id as closed
        ,c.name as customername
        ,l.name as productline
        ,u.user_name as username
        ,TIMESTAMPDIFF(HOUR,j1.min_start,j1.max_delivery) as total_hours
        ,TIMESTAMPDIFF(HOUR,min_start,now()) as interval_hours
        ,j1.min_start
        ,j1.max_delivery
        ,now()
        ,j2.allclosed
        ,j3.closed
        
        FROM project AS p
        left join customer as c on c.id = p.customer
        left join brand as b on b.id = c.brand 
        left join customer_product_line as l on l.id = p.product_line
        left join users as u on u.id = p.created_by
        left join (select project_id,`status`,id from job where `status` <> 1 group by project_id,`status`) as j on j.project_id = p.id
        left join (select project_id,MIN(start_date) AS min_start,MAX(delivery_date) AS max_delivery,ifnull(count(id),0) as closed from job GROUP BY project_id ) as j1 on j1.project_id = p.id
        left join (select project_id,ifnull(count(id),0) as allclosed from job GROUP BY project_id ) as j2 on j2.project_id = p.id left join (select project_id,ifnull(count(id),0) as closed from job where status = 1 GROUP BY project_id ) as j3 on j3.project_id = p.id 
        
        WHERE ",@declared_in_param, " order by p.id desc LIMIT ",@lim,"  OFFSET ",@ofs);

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END

PHP调用代码 #

call projects_view(20,0, "(p.created_at >= '2023-09-01' AND p.created_at <= '2023-09-17')");

错误原因排查 #

1. 核心原因：project表不存在created_at字段
   错误提示明确指出field list中找不到created_at，结合存储过程逻辑，p是project表的别名，传入的条件使用p.created_at，但如果project表本身没有这个字段，就会触发1054错误。
2. 次要排查点：SQL拼接语法问题
   检查存储过程的动态SQL拼接，确认WHERE关键字与传入条件之间有空格（代码中已包含），排除因拼接导致条件被解析为SELECT字段的情况。

解决方法 #

1. 确认并修复表结构 #

首先执行SQL查看project表结构：

DESCRIBE project;

• 如果没有created_at字段，执行以下语句添加：

ALTER TABLE project ADD COLUMN created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间';

• 如果字段存在，检查是否拼写错误（如create_at），修正条件中的字段名。

2. 优化存储过程，避免SQL注入（推荐） #

原存储过程直接拼接用户传入的条件字符串，存在严重SQL注入风险。建议将时间范围拆分为单独参数，改用参数化查询：

CREATE DEFINER=`root`@`localhost` PROCEDURE `projects_view`(IN 
  `lmt` INT, IN `ofst` INT, IN start_date DATETIME, IN end_date DATETIME)
BEGIN
    SET @sql = CONCAT("SELECT p.*
        ,b.id as brand
        ,b.name as brandname
        ,j.id as closed
        ,c.name as customername
        ,l.name as productline
        ,u.user_name as username
        ,TIMESTAMPDIFF(HOUR,j1.min_start,j1.max_delivery) as total_hours
        ,TIMESTAMPDIFF(HOUR,min_start,now()) as interval_hours
        ,j1.min_start
        ,j1.max_delivery
        ,now()
        ,j2.allclosed
        ,j3.closed
        
        FROM project AS p
        left join customer as c on c.id = p.customer
        left join brand as b on b.id = c.brand 
        left join customer_product_line as l on l.id = p.product_line
        left join users as u on u.id = p.created_by
        left join (select project_id,`status`,id from job where `status` <> 1 group by project_id,`status`) as j on j.project_id = p.id
        left join (select project_id,MIN(start_date) AS min_start,MAX(delivery_date) AS max_delivery,ifnull(count(id),0) as closed from job GROUP BY project_id ) as j1 on j1.project_id = p.id
        left join (select project_id,ifnull(count(id),0) as allclosed from job GROUP BY project_id ) as j2 on j2.project_id = p.id left join (select project_id,ifnull(count(id),0) as closed from job where status = 1 GROUP BY project_id ) as j3 on j3.project_id = p.id 
        
        WHERE p.created_at >= ? AND p.created_at <= ? order by p.id desc LIMIT ? OFFSET ?");

    PREPARE stmt FROM @sql;
    SET @start = start_date;
    SET @end = end_date;
    SET @lim = lmt;
    SET @ofs = ofst;
    EXECUTE stmt USING @start, @end, @lim, @ofs;
    DEALLOCATE PREPARE stmt;
END

3. 正确的PHP调用实现 #

针对优化后的存储过程：

<?php
// 数据库连接配置
$host = 'localhost';
$user = 'root';
$password = 'your_password';
$dbname = 'your_database';

// 建立连接
$mysqli = new mysqli($host, $user, $password, $dbname);
if ($mysqli->connect_error) {
    die("数据库连接失败: " . $mysqli->connect_error);
}

// 调用参数
$limit = 20;
$offset = 0;
$startDate = '2023-09-01 00:00:00';
$endDate = '2023-09-17 23:59:59';

// 准备并执行存储过程
$stmt = $mysqli->prepare("CALL projects_view(?, ?, ?, ?)");
$stmt->bind_param("iiss", $limit, $offset, $startDate, $endDate);
$stmt->execute();

// 获取查询结果
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    // 处理每条数据，示例：打印数据
    print_r($row);
}

// 释放资源
$stmt->close();
$mysqli->close();
?>

若坚持使用原存储过程（不推荐）：

确保project表存在created_at字段，同时对传入条件进行转义防止注入：

<?php
$host = 'localhost';
$user = 'root';
$password = 'your_password';
$dbname = 'your_database';

$mysqli = new mysqli($host, $user, $password, $dbname);
if ($mysqli->connect_error) {
    die("数据库连接失败: " . $mysqli->connect_error);
}

$limit = 20;
$offset = 0;
// 转义条件字符串
$filter = $mysqli->real_escape_string("(p.created_at >= '2023-09-01' AND p.created_at <= '2023-09-17')");

$stmt = $mysqli->prepare("CALL projects_view(?, ?, ?)");
$stmt->bind_param("iis", $limit, $offset, $filter);
$stmt->execute();

$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    print_r($row);
}

$stmt->close();
$mysqli->close();
?>

内容的提问来源于stack exchange，提问作者mohamed elghamry