php批量存储数据时需注意的问题

拼接SQL

需过滤字符串预防 SQL 注入，存入 NULL 值需要使用 MYSQL 函数。

假定表中有 2 个字段 name(VARCHAR(20) NULL) 和 age(TINYINT(4) NULL) ， 想要 name 字段写入 null 值，可以直接使用下面的 sql 语句：

insert into test(name,age) values ('lily',6);
insert into test(name,age) values ('null','null');
insert into test(name,age) values  (null,null)

那如果要批量存入数据呢？

$dsn = "mysql:host=127.0.0.1;port=3306;dbname=test;charset=utf8";
$user = "root";
$password = "";
$pdo = new PDO($dsn, $user, $password);
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

$values = [
    ['name' => 'rose', 'age' => 5],
    ['name' => 'null', 'age' => 'null'],
];
$sql = "insert into test(name,age) values ";
foreach ($values as $index => $value) {
    $name = $value['name'];
    $age = $value['age'];
    if ($index > 0) $sql .= ",";
    $sql .= "('$name',$age)";
}
$affect_rows = $pdo->exec($sql);
var_dump(affect_rows);

可以看出：数值类型 age 存入 mysql 的 null，而字符串类型的 name 存入字符串 'null' ，因为字符串格式的字段一定要用引号括起来。在不修改 sql 的情况下，要解决这个问题，就要使用 mysql 的 NULLIF 函数NULLIF(expression1, expression2)。>如果传递的两个表达式相等，则 NULLIF() 函数返回 NULL，否则如果两个表达式都不相等，则返回第一个表达式。

修改如下，且加上字符串过滤

function filterStr($input)
{
    // 正则匹配高危字符（包括注释、引号、逻辑运算符等）
    $pattern = '/(\/\*|\*\/|--|#|\'|"|;|\\\|\||\b(OR|AND|UNION|SELECT|INSERT|UPDATE|DELETE|DROP|ALTER|CREATE|EXEC|XP_)\b)/i';
    $filtered = preg_replace($pattern, '', strip_tags(trim($input)));
    $filtered = preg_replace('/0x[0-9a-f]+/i', '', $filtered);
    return htmlspecialchars($filtered, ENT_QUOTES, 'UTF-8');
}


$dsn = "mysql:host=127.0.0.1;port=3306;dbname=test;charset=utf8";
$user = "root";
$password = "";
$pdo = new PDO($dsn, $user, $password);
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

$values = [
    ['name' => 'daisy', 'age' => 7],
    ['name' => 'null', 'age' => 'null'],
];
$sql = "insert into test(name,age) values  ";
foreach ($values as $index => $value) {
    $name = $value['name'] == 'null' || $value['name'] == '' ? '' : filterStr($value['name']);
    $age = $value['age'];
    if ($index > 0) $sql .= ",";
    $sql .= "(NULLIF('$name',''),$age)";
}
$affect_rows = $pdo->exec($sql);
var_dump($affect_rows);

null 相关的 mysql 函数

IFNULL(expression1, expression2) expression1 为 null 则返回 expression2，否则返回 expression1
ISNULL(expression) expression 为 null 则返回 1，否则返回 0

预编译

可以预防SQL注入，不需要预处理字符串，且可以正常存入 NULL 值，且不需要拼接 SQL，批量处理数据效率也会比较高

$values = [
    ['name' => 'daisy2', 'age' => 17],
    ['name' => 'daisy3', 'age' => 27],
    ['name' => null, 'age' => null],
];
$sql = "INSERT into test(`name`,`age`) values (:name,:age) ";
$stmt = $pdo->prepare($sql);
$num = 0;
foreach ($values as $index => $value) {
    $stmt->execute($value);
    $num += $stmt->rowCount();
}
var_dump($num);

REPLACE 和 INSERT ON DUPLICATE KEY UPDATE 语句的影响行数

有自增主键表 test_id

CREATE TABLE `test_id` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(100) NOT NULL COLLATE 'utf8mb4_unicode_ci',
	`description` TEXT NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`created_at` TIMESTAMP NOT NULL DEFAULT current_timestamp(),
	`updated_at` TIMESTAMP NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
	PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB

联合主键表 test_em

CREATE TABLE `test_em` (
	`department_id` INT(11) NOT NULL,
	`employee_id` INT(11) NOT NULL,
	`role` VARCHAR(50) NOT NULL COLLATE 'utf8mb4_unicode_ci',
	`join_date` DATE NULL DEFAULT NULL,
	`created_at` TIMESTAMP NOT NULL DEFAULT current_timestamp(),
	`updated_at` TIMESTAMP NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
	PRIMARY KEY (`department_id`, `employee_id`) USING BTREE,
	INDEX `idx_role` (`role`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB

自增主键表测试下 INSERT ON DUPLICATE KEY UPDATE 语句的影响行数，先清空数据表<

//不存在冲突
$name = 'aa';
$sql = "INSERT into test_id(`id`,`name`) values (:id,:name) 
        ON DUPLICATE KEY UPDATE `name` = :name";
$stmt = $pdo->prepare($sql);
$result = $stmt->execute(['id' => 0, 'name' => $name]);
var_dump('不存在冲突-----------');
var_dump($stmt->rowCount(), $pdo->lastInsertId());

// 存在冲突不更新字段
$sql = "INSERT into test_id(`id`,`name`) values (:id,:name) 
        ON DUPLICATE KEY UPDATE `name` = :name";
$stmt = $pdo->prepare($sql);
$result = $stmt->execute(['id' => 1, 'name' => $name]);
var_dump('存在冲突不更新字段-----------');
var_dump($stmt->rowCount(), $pdo->lastInsertId());

//存在冲突更新字段
$name = "bb";
$sql = "INSERT into test_id(`id`,`name`) values (:id,:name) 
        ON DUPLICATE KEY UPDATE `name` = :name";
$stmt = $pdo->prepare($sql);
$result = $stmt->execute(['id' => 1, 'name' => $name]);
var_dump('存在冲突更新字段-----------');
var_dump($stmt->rowCount(), $pdo->lastInsertId());

联合主键表测试下 REPLACE 语句的影响行数，先清空数据表

//不存在冲突
$name = 'aa';
$sql = "REPLACE into test_id(`id`,`name`) values (0,?)";
$stmt = $pdo->prepare($sql);
$result = $stmt->execute([$name]);
var_dump('不存在冲突--------');
var_dump($stmt->rowCount(), $pdo->lastInsertId());

//存在冲突不更新字段
$sql = "REPLACE into test_id(`id`,`name`) values (1,?)";
$stmt = $pdo->prepare($sql);
$result = $stmt->execute([$name]);
var_dump('存在冲突不更新字段----------');
var_dump($stmt->rowCount(), $pdo->lastInsertId());

//存在冲突更新字段
$name = 'bb';
$sql = "REPLACE into test_id(`id`,`name`) values (1,?)";
$stmt = $pdo->prepare($sql);
$result = $stmt->execute([$name]);
var_dump('存在冲突更新字段----------');
var_dump($stmt->rowCount(), $pdo->lastInsertId());

多次反复试验后结论：

• REPLACE 语句在没有冲突时插入新记录，影响行数为 1，这点没有争议。有冲突且更新字段时影响行数为 2，有冲突且不更新字段是影响行数为 1 或者 2
• INSERT ON DUPLICATE KEY UPDATE 语句在没有冲突时插入新记录，影响行数为 1，有冲突且更新字段时影响行数为 2，有冲突且不更新字段是影响行数为 0，行为较REPLACE语句稳定。

INSERT 和 REPLACE 的区别：

• INSERT：向表中插入新记录，若主键/唯一索引冲突则报错（除非使用 IGNORE 或 ON DUPLICATE KEY UPDATE），记录不存在时插入。
• REPLACE：根据主键/唯一索引若记录存在，先删除旧记录再插入新记录，不存在则直接插入。未指定的字段会被重置为默认值（若未定义默认值则报错）。仅在确定需要整行替换且字段完整性允许时使用。
• INSERT ... ON DUPLICATE KEY UPDATE：冲突时直接更新指定字段（相当于 UPDATE），原记录其他字段保持不变。多数场景下更安全高效，避免字段意外重置和 ID 变更