传入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
相关产品推荐
相关产品推荐

