PHP do/while准备语句失败

Question

我有一段代码，用于在数据库上运行检查以查看给定值是否是唯一的，如果是，则将其插入到数据库中。如果它不是唯一的，重复这个过程，直到找到一个唯一的值。

do {
    // Generate a new user ID (15 numbers) and check to make sure it doesn't already exist.
    $new_user_id = mt_rand(1000000000, 9999999999);

    // Generate a fake Login Email address and check to make sure it doesn't already exist.
    $new_username = rand_str(13, 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890');
    $new_username_email = 'BP' . $new_username . '@web.com';

    // Generate a new fake password
    $new_password = rand_str(15, 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890!@#$%^&*()');

    // Check to make sure the extremely random Username and user ID doesn't already exist in the database
    // On the off chance that it does, we need to regenerate and try again.

    $preparedStatement = $connection->prepare("SELECT user_id, username FROM `{$mysql_table}` WHERE user_id = :new_user_id OR username = :new_username");
    $preparedStatement->execute(array(':new_user_id' => $new_user_id, ':new_username' => $new_username_email));
    $result_2 = $preparedStatement->fetchAll();

    //TODO Not sure if this is actually working if there is a duplicate entry
} while (!empty($result_2));

// Once it is unique, insert the values into the database

$preparedStatement = $connection->prepare(
    "INSERT INTO `{$mysql_table}` (
        open_id, 
        user_id, 
        username, 
        password
    ) VALUES (
        :open_id_value, 
        :user_id_value, 
        :username_value, 
        :password_value
    )");

    if (!$preparedStatement->execute(array(
        ':open_id_value' => $_SESSION['user'], 
        ':user_id_value' => $new_user_id, 
        ':username_value' => $new_username_email, 
        ':password_value' => $new_password
    ))) {
        $arr = $preparedStatement->errorInfo();
                    die(print_r($arr));                             
    } else {
    // Send the new user to the account settings page, where they can set up their account information
        $_SESSION['new_user'] = 1;
        //echo 'You are a new user!';
        header("Location: /account-settings/");
        exit;                       
    }

我得到的问题是，由mt_rand生成的值表明它是重复的。

Array ( [0] => 23000 [1] => 1062 [2] => Duplicate entry '2147483647' for key 1 ) 1

首先，我不知道为什么我会从这段代码中得到一个重复的错误--它有什么问题？其次，在我得到一个副本的机会，它应该重新生成，并再次尝试，直到它工作-但这显然没有发生。

有什么线索吗？

Answer 1

为了详细说明我的comment...Your脚本，它生成了一个随机数，大约80%的时间会超过整数的最大值。测试user_id是否已经被获取的查询将返回false (mysql允许您在列的范围限制之外查询，它只是说没有具有该id的记录)，但是插入查询将失败，因为该数字将减少到最大整数大小。要解决这个问题，您可以切换到BIGINT或限制您的随机范围。

Answer 2

尝试将您的ID列更改为BIGINT vs INT，因为您似乎正在尝试生成大于INT类型容量的随机数。这就是为什么它会降到2147483647，这是整型最大数。

Answer 3

int数据类型不能处理更多的Limit mt_rand(1000000000,2147483647);。