`Column count doesn't match value count at row 1` 错误表明 SQL 插入语句中指定的列数与提供的值的数量不匹配。以下是解决此问题的一些方法：
### 检查 SQL 语句中的列和值的数量
确保插入语句中指定的列数与 `VALUES` 子句中的值的数量一致。例如，在原代码中插入语句为：
```php
$sql = "INSERT INTO `bom_mlb_9802` (`unit_sn`,`mlb_sn`,`sip`,`part`,`flag`,`hawb`,`packout_date`,`program`,`shipout`,`shipment_date`,`purchase`,`rma`,`lob`,`vendor`,`create_time`)VALUES " . implode(', ', $placeholders);
```
这里指定了 15 列，那么 `$placeholders` 生成的每个括号内的值的数量也应该是 15 个。
### 检查数据数组的结构
确保 `$data` 数组中的每个子数组包含的元素数量与插入语句中指定的列数一致。在原代码中，`$data` 数组的每个子数组应该包含 15 个元素：
```php
$data[] = array(
'unit_sn' => $unit_sn,
'mlb_sn' => $mlb_sn,
'sip' => $sip,
'part' => $part,
'flag' => $flag,
'hawb' => $hawb,
'packout_date' => $packout_date,
'program' => $program,
'shipout' => $shipout,
'shipment_date' => $shipment_date,
'purchase' => $purchase,
'rma' => $rma,
'lob' => $lob,
'vendor' => $vendor,
'create_time' => $date
);
```
### 调试输出
在生成 SQL 语句之前，可以添加调试输出，检查 `$data` 数组和 `$placeholders` 的内容：
```php
foreach ($dataChunks as $chunk) {
$placeholders = [];
$values = [];
$types = '';
$params = [];
foreach ($chunk as $row) {
foreach ($row as $value) {
if (is_int($value)) {
$types .= 'i';
} else {
$types .= 's';
}
}
$placeholders[] = '(' . implode(',', array_fill(0, count($row), '?')) . ')';
$values = array_merge($values, array_values($row));
}
// 调试输出
echo "Column count: " . count($row) . "\n";
echo "Value count: " . count($values) / count($chunk) . "\n";
echo "Placeholders: " . implode(', ', $placeholders) . "\n";
$sql = "INSERT INTO `bom_mlb_9802` (`unit_sn`,`mlb_sn`,`sip`,`part`,`flag`,`hawb`,`packout_date`,`program`,`shipout`,`shipment_date`,`purchase`,`rma`,`lob`,`vendor`,`create_time`)VALUES " . implode(', ', $placeholders);
$stmt = $db->prepare($sql);
if($stmt){
$stmt->bind_param($types, ...$values);
$stmt->execute();
$stmt->close();
}
}
```
### 检查数据库表结构
确保数据库表 `bom_mlb_9802` 中的列数和列名与插入语句中指定的一致。
### 示例代码
以下是一个完整的示例，包含调试输出：
```php
// 假设 $db 是已经连接好的数据库对象
// 假设 $worksheet 是已经读取好的 Excel 工作表对象
// 假设 $highestRow 是 Excel 工作表的最大行数
// 假设 $date 是当前日期
$sql = "SELECT `unit_sn` FROM `bom_mlb_9802`";
$existingUnitSn = $db->fetchAll($sql);
$i = 0;
$row = 0;
$data = array();
for ($row = 2; $row <= $highestRow; $row++) {
$unit_sn = trim((string)$worksheet->getCellByColumnAndRow(0, $row)->getFormattedValue());
if (strlen($unit_sn) === 0) {
continue;
}
if (!in_array($unit_sn, $existingUnitSn)) {
$mlb_sn = trim((string)$worksheet->getCellByColumnAndRow(1, $row)->getFormattedValue());
$sip = trim((string)$worksheet->getCellByColumnAndRow(2, $row)->getFormattedValue());
$part = trim((string)$worksheet->getCellByColumnAndRow(3, $row)->getFormattedValue());
$flag = trim((string)$worksheet->getCellByColumnAndRow(4, $row)->getFormattedValue());
$hawb = trim((string)$worksheet->getCellByColumnAndRow(5, $row)->getFormattedValue());
$packout_date = trim((string)$worksheet->getCellByColumnAndRow(6, $row)->getFormattedValue());
$program = trim((string)$worksheet->getCellByColumnAndRow(7, $row)->getFormattedValue());
$shipout = trim((string)$worksheet->getCellByColumnAndRow(8, $row)->getFormattedValue());
$shipment_date = trim((string)$worksheet->getCellByColumnAndRow(9, $row)->getFormattedValue());
$purchase = trim((string)$worksheet->getCellByColumnAndRow(10, $row)->getFormattedValue());
$rma = trim((string)$worksheet->getCellByColumnAndRow(11, $row)->getFormattedValue());
$lob = trim((string)$worksheet->getCellByColumnAndRow(12, $row)->getFormattedValue());
$vendor = trim((string)$worksheet->getCellByColumnAndRow(13, $row)->getFormattedValue());
$data[] = array(
'unit_sn' => $unit_sn,
'mlb_sn' => $mlb_sn,
'sip' => $sip,
'part' => $part,
'flag' => $flag,
'hawb' => $hawb,
'packout_date' => $packout_date,
'program' => $program,
'shipout' => $shipout,
'shipment_date' => $shipment_date,
'purchase' => $purchase,
'rma' => $rma,
'lob' => $lob,
'vendor' => $vendor,
'create_time' => $date
);
$i = $i + 1;
}
}
if (!empty($data)) {
$chunkSize = 200;
$dataChunks = array_chunk($data, $chunkSize);
foreach ($dataChunks as $chunk) {
$placeholders = [];
$values = [];
$types = '';
$params = [];
foreach ($chunk as $row) {
foreach ($row as $value) {
if (is_int($value)) {
$types .= 'i';
} else {
$types .= 's';
}
}
$placeholders[] = '(' . implode(',', array_fill(0, count($row), '?')) . ')';
$values = array_merge($values, array_values($row));
}
echo "Column count: " . count($row) . "\n";
echo "Value count: " . count($values) / count($chunk) . "\n";
echo "Placeholders: " . implode(', ', $placeholders) . "\n";
$sql = "INSERT INTO `bom_mlb_9802` (`unit_sn`,`mlb_sn`,`sip`,`part`,`flag`,`hawb`,`packout_date`,`program`,`shipout`,`shipment_date`,`purchase`,`rma`,`lob`,`vendor`,`create_time`)VALUES " . implode(', ', $placeholders);
$stmt = $db->prepare($sql);
if ($stmt) {
$stmt->bind_param($types, ...$values);
$stmt->execute();
$stmt->close();
}
}
}
```
### 总结
通过以上方法，可以逐步排查并解决 `Column count doesn't match value count at row 1` 错误。
本文针对在执行SQL插入操作时遇到的Columncountdoesn'tmatchvaluecountatrow1错误进行了详细解析。通常这类错误是因为在插入的数据中多加了字符或者符号，导致与创建表时定义的字段数量不匹配。
扫一扫
3573
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
