You need to enable JavaScript to run this app. 传入NULL值时MySQL未触发avatar字段DEFAULT默认值导致插入失败问题
优惠活动
大模型
产品
解决方案
定价
更多

传入NULL值时MySQL未触发avatar字段DEFAULT默认值导致插入失败问题

问题解决方案

错误原因分析

  • 逻辑错误1:文件上传判断规则错误
    即便用户没有上传文件,只要表单中存在sPhoto文件字段,$_FILES['sPhoto']变量依然存在,仅error字段值为UPLOAD_ERR_NO_FILE。直接用isset($_FILES['sPhoto'])判断是否上传文件,会把未上传的情况误判为已上传,此时取$_FILES['sPhoto']['name']得到空字符串,explode('.', '')返回[""],!empty($type)判定为真,后续后缀校验不通过就会进入「文件类型不允许」分支。
  • 逻辑错误2:COALESCE函数用法错误
    你写的COALESCE(?, avatar, DEFAULT(avatar))逻辑有误,INSERT操作执行时该行的avatar字段还未赋值,引用avatar取不到任何有效值,无法触发默认值逻辑。

修复代码

1. 修复文件上传判断+SQL默认值逻辑

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$uploadError = '';
if (isset($_POST['submit'])) {
    include 'db.php';
    $sName = $_POST['sName'];
    $company = $_POST['sCmp'];
    $phone = $_POST['sNumber'];
    $target_file = null;
    // 新增:判断是否真的上传了有效文件
    $hasValidUpload = isset($_FILES['sPhoto']) && $_FILES['sPhoto']['error'] === UPLOAD_ERR_OK;
    if ($hasValidUpload) {
        $type = explode('.', $_FILES['sPhoto']['name']);
        $type = strtolower(end($type)); // 转小写避免PNG等大写后缀校验失败
        $allowed = ['png', 'jpg'];
        if (in_array($type, $allowed)) {
            $tmp_name = $_FILES['sPhoto']['tmp_name'];
            $target_dir = 'assets/images/suppliers/';
            $target_file = $target_dir . basename($_FILES["sPhoto"]["name"]);
            move_uploaded_file($tmp_name, $target_file);
        } else {
            $uploadError = '该文件类型不允许上传!';
            echo($uploadError);
        }
    }

    if (empty($uploadError)) {
        // 未上传头像时不写avatar字段,数据库自动调用默认值
        if (!is_null($target_file)) {
            $sql = "INSERT INTO suppliers (name,company,phone,avatar) VALUES (?,?,?,?);";
            $stmt = mysqli_stmt_init($conn);
            mysqli_stmt_prepare($stmt, $sql);
            mysqli_stmt_bind_param($stmt, "ssis", $sName, $company, $phone, $target_file);
        } else {
            $sql = "INSERT INTO suppliers (name,company,phone) VALUES (?,?,?);";
            $stmt = mysqli_stmt_init($conn);
            mysqli_stmt_prepare($stmt, $sql);
            mysqli_stmt_bind_param($stmt, "ssi", $sName, $company, $phone);
        }
        mysqli_stmt_execute($stmt);
    }

} else {
    if (isset($_POST['delSubmit'])) {
        include 'db.php';
        $name = $_POST['name'];
        $sql = "DELETE FROM suppliers WHERE name = ?;";
        $stmt = mysqli_stmt_init($conn);
        mysqli_stmt_prepare($stmt, $sql);
        mysqli_stmt_bind_param($stmt, "s", $name);
        mysqli_stmt_execute($stmt);
    }
}
?>

额外优化建议

数据库suppliers表的phone字段为int类型,最多只能存储10位数字,无法容纳11位国内手机号,建议修改为varchar类型存储手机号。

内容的提问来源于stack exchange,提问作者Ameen Assadi

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.08 06:57:02