MySQL如何按给定人员数组循环为candidates表每行分配负责人员

新增记录时自动分配方案 #

插入数据时直接计算对应分配人员，无需事后多条件判断更新：

$staffs = ["john","mark","belly","rick", "tom", "harry"];
$staffTotal = count($staffs);

// 插入前先计算当前记录对应分配的人员
// 方式1：表内无删除过数据、ID连续无断层的场景，插入后计算
$insertSql = "INSERT INTO candidates (name, email, date_signup) VALUES (?, ?, ?)";
$stmt = $mysqli->prepare($insertSql);
$stmt->bind_param("sss", $name, $email, $date_signup);
$stmt->execute();
// 取刚插入的自增ID计算下标，ID从1开始所以先减1再取模
$newId = $mysqli->insert_id;
$assignStaff = $staffs[($newId - 1) % $staffTotal];
// 直接更新staff字段即可，无需多条件判断
$updateSql = "UPDATE candidates SET staff = ? WHERE Id = ?";
$updateStmt = $mysqli->prepare($updateSql);
$updateStmt->bind_param("si", $assignStaff, $newId);
$updateStmt->execute();

// 方式2：表内有删除记录、ID不连续的场景，插入前计算
$countRes = $mysqli->query("SELECT COUNT(*) as total FROM candidates");
$currentTotal = $countRes->fetch_assoc()['total'];
$assignStaff = $staffs[$currentTotal % $staffTotal];
// 插入时直接带入staff字段，无需后续更新
$insertSql = "INSERT INTO candidates (name, email, date_signup, staff) VALUES (?, ?, ?, ?)";
$stmt = $mysqli->prepare($insertSql);
$stmt->bind_param("ssss", $name, $email, $date_signup, $assignStaff);
$stmt->execute();

历史数据批量分配方案 #

如果需要给已经存在的所有记录批量补充分配人员，可直接用foreach循环处理，或者纯SQL实现：

PHP循环实现 #

$staffs = ["john","mark","belly","rick", "tom", "harry"];
$staffTotal = count($staffs);
// 按ID升序取出所有记录ID
$idRes = $mysqli->query("SELECT Id FROM candidates ORDER BY Id ASC");
$idList = [];
while($row = $idRes->fetch_assoc()){
    $idList[] = $row['Id'];
}
// 循环更新，用数组下标取模匹配对应人员
foreach($idList as $index => $id){
    $assignStaff = $staffs[$index % $staffTotal];
    $updateStmt = $mysqli->prepare("UPDATE candidates SET staff = ? WHERE Id = ?");
    $updateStmt->bind_param("si", $assignStaff, $id);
    $updateStmt->execute();
}

纯SQL实现（大数据量场景性能更高） #

UPDATE candidates 
SET staff = ELT(
    (Id - 1) MOD 6 + 1,
    'john','mark','belly','rick','tom','harry'
)
ORDER BY Id ASC;

以上SQL里MOD后面的6对应员工数组长度，ELT后面按顺序放数组里的员工名称即可，无需拉取数据到PHP层处理。

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