MySQL有一个rand((函数https://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html#function_rand
我希望在数据库的每一列中插入随机数。
出现的问题是，由于某种原因，每列内部都传递了相同的随机生成值。
$sql = "SELECT * FROM players";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// output data of each row
$targScore = 3;
while($row = $result->fetch_assoc()) {
$target = 3; // The target number the sequence is adding up to
$n = 4; // Max numbers within sequence
while ($n) {
if (1 < $n--) {
$addend = rand(0, $target - ($n - 1));
$target -= $addend;
$num[] = $addend;
} else {
$num[] = $target;
}
}
/* Rows to update */
/* Output looks like:
[0] = 1
[1] = 2
[0] = 0
[0] = 1
*/
$changeScore = "UPDATE players SET doja='$num[0]' WHERE score='$targScore'";
$conn->query($changeScore);
$changeScore = "UPDATE players SET 7acres='$num[1]' WHERE score='$targScore'";
$conn->query($changeScore);
$changeScore = "UPDATE players SET tweed='$num[2]' WHERE score='$targScore'";
$conn->query($changeScore);
$changeScore = "UPDATE players SET bickel='$num[3]' WHERE score='$targScore'";
$conn->query($changeScore);
}
}
数据如下所示：https://i.stack.imgur.com/5mQMO.jpg
我需要的是，每个循环，它将再次运行随机化器，但改变其插入的数字，使每一行都有不同的数字。
您需要首先获得满足score条件的所有行的列表。然后你必须一个接一个地更新它们，这样你就可以在每个中设置不同的随机值。
$select_stmt = $conn->prepare("SELECT id FROM players WHERE score = ?");
$select_stmt->bind_param("i", $targScore);
$select_stmt->execute();
$res = $select_stmt->get_result();
$update_stmt = $conn->prepare("UPDATE players SET doja = ?, 7acres = ?, tweed = ?, bickel = ? WHERE id = ?");
$update_stmt->bind_param("iiiii", $doja, $acres, $tweed, $bickel, $id);
$target = 3;
while ($row = $res->fetch_assoc()) {
$n = 4;
$num = [];
while ($n) {
if (1 < $n--) {
$addend = rand(0, $target - ($n - 1));
$target -= $addend;
$num[] = $addend;
} else {
$num[] = $target;
}
}
[$doja, $acres, $tweed, $bickel] = $num;
$id = $row['id'];
$update_stmt->execute();
}
也不需要使用四个UPDATE语句来更新同一行中的4列。
(请注意，它不是完全随机/安全的，但PHPsrand()也不是(。
如果你不需要知道值是什么，你可以简单地做UPDATE myCol=FLOOR(RAND() * 200) WHERE myConditionalField=MyCondition
这将分配一个从0到199的随机数；MySQL手册(链接到上面(中关于如何设置范围的示例，例如FLOOR(7 + (RAND() * 5))给出了从7到11的整数。