MySqli预处理语句execute()返回false问题排查求助

问题描述 #

我在开发供应商信息插入功能时碰到了一个棘手的问题：编写的addVendorForm函数里，$q->prepare()和$q->bind_param()都返回bool(true)，但调用$q->execute()时却返回bool(false)。需要插入数据的rtl_vendor表共有38列（排除自增id列，实际需要插入37列），相关代码和表结构信息如下：

问题代码 #

public function addVendorForm($data,$db){ 
    $date = date('Y-m-d'); 
    $q = $db->prepare('INSERT INTO `rtl_vendor`(date,name,email,mobile_no,telephone_no,home_address_one,home_address_two,home_city,home_state,home_pincode,home_addr_proof,business_address_one,business_address_two,business_city,business_state,business_pincode,business_addr_proof,fax_no,website,contact_person_detail,account_person_detail,business_entity,business_commodity,pan_card,gst_certificate,annual_sales,vendor_references,employee_detail,director_detail,bank_name,bank_address,beneficiary_name,bank_acno,bank_ifsc,bank_cheque,notes,status) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'); 
    $q->bind_param('sssssssssssssssssssssiisssssssssssssi',$date,$data['name'],$data['email'],$data['phone'],$data['telephone'], $data['home_address_one'],$data['home_address_two'],$data['home_city'],$data['home_state'],$data['home_pincode'],$data['uploaded']['home_address_proof'],$data['business_address_one'],$data['business_address_two'],$data['business_city'],$data['business_state'],$data['business_pincode'],$data['uploaded']['business_address_proof'],$data['fax_no'],$data['website'],$data['contact_person'],$data['accounts_person'],$data['business_entity'],$data['business_commodity'],$data['uploaded']['pan_card'],$data['uploaded']['gst_certificate'],$data['annual_sales'],$data['vendor_references'],$data['employee_detail'],$data['director_detail'],$data['bank_name'],$data['bank_address'],$data['beneficiary_name'],$data['bank_acno'],$data['bank_ifsc'],$data['uploaded']['cheque_leaf'],$data['notes'],$data['status']); 
    if($q->execute()) { 
        return true; 
    } 
    return false; 
}

表结构说明 #

rtl_vendor表的name字段设置了NOT NULL非空约束，其余字段也对应配置了匹配业务需求的数据类型与约束（比如date为日期类型、mobile_no为字符串类型等）。

我已经确认MySQLi连接对象本身没有异常，实在找不到问题所在，恳请帮忙定位错误原因。

解决方案 #

遇到这种prepare()和bind_param()成功但execute()失败的情况，最有效的排查方法就是直接输出MySQLi语句的错误信息。我们可以修改代码，在调用execute()后添加错误输出的逻辑：

修改后的排查代码 #

public function addVendorForm($data,$db){ 
    $date = date('Y-m-d'); 
    $q = $db->prepare('INSERT INTO `rtl_vendor`(date,name,email,mobile_no,telephone_no,home_address_one,home_address_two,home_city,home_state,home_pincode,home_addr_proof,business_address_one,business_address_two,business_city,business_state,business_pincode,business_addr_proof,fax_no,website,contact_person_detail,account_person_detail,business_entity,business_commodity,pan_card,gst_certificate,annual_sales,vendor_references,employee_detail,director_detail,bank_name,bank_address,beneficiary_name,bank_acno,bank_ifsc,bank_cheque,notes,status) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'); 
    $q->bind_param('sssssssssssssssssssssiisssssssssssssi',$date,$data['name'],$data['email'],$data['phone'],$data['telephone'], $data['home_address_one'],$data['home_address_two'],$data['home_city'],$data['home_state'],$data['home_pincode'],$data['uploaded']['home_address_proof'],$data['business_address_one'],$data['business_address_two'],$data['business_city'],$data['business_state'],$data['business_pincode'],$data['uploaded']['business_address_proof'],$data['fax_no'],$data['website'],$data['contact_person'],$data['accounts_person'],$data['business_entity'],$data['business_commodity'],$data['uploaded']['pan_card'],$data['uploaded']['gst_certificate'],$data['annual_sales'],$data['vendor_references'],$data['employee_detail'],$data['director_detail'],$data['bank_name'],$data['bank_address'],$data['beneficiary_name'],$data['bank_acno'],$data['bank_ifsc'],$data['uploaded']['cheque_leaf'],$data['notes'],$data['status']); 
    $q->execute(); 
    print_r($q->error); // 输出语句执行的错误信息
    exit; 
    if($q->execute()) { 
        return true; 
    } 
    return false; 
}

执行这段代码后，我们得到了明确的错误提示：Column 'name' cannot be null。这说明传入的$data['name']值为null，而表中name字段设置了非空约束，导致插入操作失败。

接下来只需要确保调用addVendorForm函数时，传入的$data['name']包含合法的非空值，就能解决这个问题了。

内容的提问来源于stack exchange，提问作者Silambarasan R