服务器迁移后PHP表单无法使用：SQL列值不匹配错误排查
2026-8-13
问题
服务器迁移后PHP版本从7.4升级至8.1，提交注册表单时触发以下错误：
Fatal error: Uncaught PDOException: SQLSTATE[21S01]: Insert value list does not match column list: 1136 Column count doesn't match value count
已核对表名、占位符?数量、插入参数数量，均与数据库37个字段对应，但排查3小时仍未定位问题。新系统明年下半年才上线，急需修复当前旧代码问题。
代码片段
$folder = "uploads/"; $image = $_FILES['image']['name']; $path = $folder . uniqid().$image ; $target_file= $folder.basename($_FILES["image"]["name"]); $imageFileType = pathinfo($target_file,PATHINFO_EXTENSION); $allowed = array('jpeg','png','PNG','JPG' ,'jpg','gif'); $filename = $_FILES['image']['name']; $ext=pathinfo($filename, PATHINFO_EXTENSION); if(!in_array($ext,$allowed) ) { $msg = "Sorry, only JPG, JPEG, PNG & GIF files are allowed."; // var_dump($msg); } else{ move_uploaded_file( $_FILES['image'] ['tmp_name'], $path); // var_dump($path); $imageSource = $path; } $id = isset($_POST['id']) && !empty($_POST['id']) && $_POST['id'] != 'auto' ? $_POST['id'] : NULL; $status = $_POST['status']; $learner_id = $_POST['learner_id']; $title = $_POST['title']; $name = $_POST['name']; $last_name = $_POST['last_name']; $sex = $_POST['sex']; $dob = $_POST['dob']; $house_number = $_POST['house_number']; $address_line_one = $_POST['address_line_one']; $address_line_two = $_POST['address_line_two']; $city_town = $_POST['city_town']; $country = $_POST['country']; $postcode = $_POST['postcode']; $postcode_enrolment = $_POST['postcode_enrolment']; $phone = $_POST['phone']; $mobile_number = $_POST['mobile_number']; $email_address = $_POST['email_address']; $ethnic_origin = $_POST['ethnic_origin']; $ethnicity = $_POST['ethnicitys']; $health_problem = $_POST['health_problem']; $health_disability_problem_if_yes = isset($_POST['health_disability_problem_if_yes']) ? json_encode($_POST['health_disability_problem_if_yes']) : ''; $lldd = $_POST['lldd']; $education_health_care_plan = $_POST['education_health_care_plan']; $health_problem_start_date = $_POST['health_problem_start_date']; $education_entry = $_POST['education_entry']; $emergency_contact_details = $_POST['emergency_contact_details']; $employment_paid_status = $_POST['employment_paid_status']; $employment_date = $_POST['employment_date']; $unemployed_month = $_POST['unemployed_month']; $education_training_prior = $_POST['education_training_prior']; $education_claiming = $_POST['education_claiming']; $claiming_if_yes = $_POST['claiming_if_yes']; $household_situation = $_POST['household_situation']; $stmt = $pdo->prepare('INSERT INTO contacts VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'); $result = $stmt->execute([$id, $status, $path, date("Y/m/d"), $learner_id, $title, $name, $last_name, $sex, $dob, $house_number, $address_line_one, $address_line_two, $city_town, $country, $postcode, $postcode_enrolment, $phone, $mobile_number, $email_address, $ethnic_origin, $ethnicity, $health_problem, $health_disability_problem_if_yes, $lldd, $education_health_care_plan, $health_problem_start_date, $education_entry, $emergency_contact_details, $employment_paid_status, $employment_date, $unemployed_month, $education_training_prior, $education_claiming, $claiming_if_yes, $household_situation, null]);
数据库结构
`id` int(11) NOT NULL, `status` varchar(20) NOT NULL DEFAULT 'Referral' COMMENT 'status is Referral,InProgress,S2,S3,NoContact,Old,Declined', `image` varchar(225) DEFAULT NULL, `datemovedin` date DEFAULT NULL, `learner_id` varchar(100) DEFAULT NULL, `title` varchar(225) DEFAULT NULL, `name` varchar(50) DEFAULT NULL, `last_name` varchar(50) DEFAULT NULL, `sex` varchar(50) DEFAULT NULL, `dob` varchar(50) DEFAULT NULL, `house_number` varchar(225) DEFAULT NULL, `address_line_one` varchar(225) DEFAULT NULL, `address_line_two` varchar(225) DEFAULT NULL, `city_town` varchar(50) DEFAULT NULL, `country` varchar(50) DEFAULT NULL, `postcode` varchar(50) DEFAULT NULL, `postcode_enrolment` varchar(50) DEFAULT NULL, `phone` varchar(50) DEFAULT NULL, `mobile_number` varchar(50) DEFAULT NULL, `email_address` varchar(225) DEFAULT NULL, `ethnic_origin` varchar(225) DEFAULT NULL, `ethnicitys` varchar(225) DEFAULT NULL, `health_problem` varchar(225) DEFAULT NULL, `health_disability_problem_if_yes` text DEFAULT NULL, `lldd` text DEFAULT NULL, `education_health_care_plan` varchar(225) DEFAULT NULL, `health_problem_start_date` varchar(225) DEFAULT NULL, `education_entry` varchar(225) DEFAULT NULL, `emergency_contact_details` varchar(225) DEFAULT NULL, `employment_paid_status` varchar(225) DEFAULT NULL, `employment_date` varchar(225) DEFAULT NULL, `unemployed_month` varchar(225) DEFAULT NULL, `education_training_prior` varchar(225) DEFAULT NULL, `education_claiming` varchar(225) DEFAULT NULL, `claiming_if_yes` varchar(225) DEFAULT NULL, `household_situation` varchar(225) DEFAULT NULL, `mentor` int(11) DEFAULT NULL
解决方案
问题根源有两个：
日期格式不兼容
数据库中datemovedin是date类型，要求格式为YYYY-MM-DD，但代码中用date("Y/m/d")生成的是YYYY/MM/DD格式。PHP7.4搭配的MySQL版本会自动转换不规范日期，但PHP8.1对应的MySQL（或SQL模式）更严格，直接拒绝该格式，导致该字段插入失败，实际有效参数数量减少，触发字段数不匹配错误。插入语句无字段名的隐性风险
用INSERT INTO contacts VALUES (...)这种不指定字段名的写法，一旦表结构字段顺序变动就会出错，且排查难度大。必须明确指定字段名，同时修正日期格式。
修改后的插入语句示例：
$stmt = $pdo->prepare('INSERT INTO contacts (id, status, image, datemovedin, learner_id, title, name, last_name, sex, dob, house_number, address_line_one, address_line_two, city_town, country, postcode, postcode_enrolment, phone, mobile_number, email_address, ethnic_origin, ethnicitys, health_problem, health_disability_problem_if_yes, lldd, education_health_care_plan, health_problem_start_date, education_entry, emergency_contact_details, employment_paid_status, employment_date, unemployed_month, education_training_prior, education_claiming, claiming_if_yes, household_situation, mentor) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'); $result = $stmt->execute([$id, $status, $path, date("Y-m-d"), $learner_id, $title, $name, $last_name, $sex, $dob, $house_number, $address_line_one, $address_line_two, $city_town, $country, $postcode, $postcode_enrolment, $phone, $mobile_number, $email_address, $ethnic_origin, $ethnicity, $health_problem, $health_disability_problem_if_yes, $lldd, $education_health_care_plan, $health_problem_start_date, $education_entry, $emergency_contact_details, $employment_paid_status, $employment_date, $unemployed_month, $education_training_prior, $education_claiming, $claiming_if_yes, $household_situation, null]);
额外补充两个细节修复：
- 代码中
$ethnicity = $_POST['ethnicitys'];对应数据库的ethnicitys字段，插入语句的字段列表中要写ethnicitys，避免字段名拼写错误； - 图片上传逻辑中，若用户上传不允许的文件类型，
$path变量会未定义，建议在代码开头初始化$path = null;，防止插入时传递无效值。
内容的提问来源于stack exchange，提问作者user19638812
相关产品推荐
相关产品推荐
