使用INSERT INTO、ON DUPLICATE KEY和IF()预处理语句的类型错误问题

问题描述 #

需要在INSERT INTO...ON DUPLICATE KEY UPDATE语句中标记任意数据类型的字段不更新，但使用字符串标记（如'DO NOT UPDATE'）时，会因MySQL插入阶段的类型检查报错——字符串无法转换为int类型字段的值，即使实际场景中所有主键已存在、不会执行插入操作。

原代码及错误：

$stmt = "INSERT INTO user (id, state, age) 
VALUES (?, ?, ?),(?, ?, ?) AS foo_baz
ON DUPLICATE KEY UPDATE 
name = if(foo_baz.name='DO NOT UPDATE', user.name, foo_baz.name),
age = if(foo_baz.age='DO NOT UPDATE', user.age, foo_baz.age)";

$data = [1, 'Oregon', 55, 2, 'Washington', 'DO NOT UPDATE'];

$sql = $pdo->prepare($stmt);
$sql->execute($data);

错误信息："Invalid datetime format: 1292 Truncated incorrect DOUBLE value: "

解决方案 #

方案1：动态构建SQL（推荐，通用兼容所有类型） #

在PHP层面过滤掉标记为不更新的字段，仅生成需要更新的字段的ON DUPLICATE KEY UPDATE子句，同时确保VALUES中的值都符合对应字段的数据类型（避免插入阶段类型错误）。

示例代码：

// 定义待处理记录，用'DO NOT UPDATE'标记无需更新的字段
$records = [
    ['id' => 1, 'state' => 'Oregon', 'age' => 55],
    ['id' => 2, 'state' => 'Washington', 'age' => 'DO NOT UPDATE']
];

$fields = array_keys($records[0]);
$placeholders = [];
$updateClauses = [];
$data = [];

foreach ($records as $record) {
    $rowPlaceholders = [];
    foreach ($fields as $field) {
        $value = $record[$field];
        if ($value === 'DO NOT UPDATE') {
            // 填充符合字段类型的占位值（int用0，varchar用空字符串，避免类型错误）
            $rowPlaceholders[] = '?';
            $data[] = match($field) {
                'age' => 0,
                default => ''
            };
        } else {
            $rowPlaceholders[] = '?';
            $data[] = $value;
            // 仅为需要更新的字段添加UPDATE子句（主键id跳过）
            if ($field !== 'id') {
                $updateClauses[] = "$field = VALUES($field)";
            }
        }
    }
    $placeholders[] = '(' . implode(', ', $rowPlaceholders) . ')';
}

// 去重重复的UPDATE子句
$updateClauses = array_unique($updateClauses);

// 拼接最终SQL
$stmt = sprintf(
    "INSERT INTO user (%s) VALUES %s ON DUPLICATE KEY UPDATE %s",
    implode(', ', $fields),
    implode(', ', $placeholders),
    implode(', ', $updateClauses)
);

$sql = $pdo->prepare($stmt);
$sql->execute($data);

方案2：使用NULL作为通用标记（需字段允许NULL） #

如果字段允许存储NULL，可以用NULL作为不更新的标记，利用IS NULL判断，避免类型转换错误：

示例代码：

$stmt = "INSERT INTO user (id, state, age) 
VALUES (?, ?, ?),(?, ?, ?) AS foo_baz
ON DUPLICATE KEY UPDATE 
state = IF(foo_baz.state IS NULL, user.state, foo_baz.state),
age = IF(foo_baz.age IS NULL, user.age, foo_baz.age)";

// 用NULL标记无需更新的字段
$data = [1, 'Oregon', 55, 2, 'Washington', null];

$sql = $pdo->prepare($stmt);
$sql->execute($data);

方案3：针对不同类型设置哨兵值（适合字段不允许NULL的场景） #

为不同数据类型约定业务中不会出现的哨兵值，在UPDATE分支判断是否保留原字段值：

示例代码：

$stmt = "INSERT INTO user (id, state, age) 
VALUES (?, ?, ?),(?, ?, ?) AS foo_baz
ON DUPLICATE KEY UPDATE 
state = IF(foo_baz.state = '__NO_UPDATE__', user.state, foo_baz.state),
age = IF(foo_baz.age = -999999, user.age, foo_baz.age)";

// 用对应类型的哨兵值标记不更新字段
$data = [1, 'Oregon', 55, 2, '__NO_UPDATE__', -999999];

$sql = $pdo->prepare($stmt);
$sql->execute($data);

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