The efficiency problem of inserting a unique random string into Mysql by PHP

there is a CAPTCHA data table with 2 million pieces of data. CAPTCHA fields need to be unique
you need to insert a large amount of data into this table, about tens of thousands of pieces of data at a time
how to optimize the execution time of each time

current practice:

makes an array containing the unique CAPTCHA.
check the verification code table according to the array, and find the existing regenerations
5000 insert in batches at a time

private function generateUniqueValidationCodes(int $number)
{
    // 
    $codeArr = $this->generateUniqueCodes($number);
    // 
    $result = ValidationCode::query()->whereIn("validation_code", $codeArr)->get();
    // 
    if ($result->isNotEmpty()) {
        $this->generateUniqueValidationCodes($number);
    } else {
        return $codeArr;
    }
}

Laravel random-number algorithm php mysql

Mar.22,2021

there is a minor error in the code

$this->generateUniqueValidationCodes($number);

here we have to add that the return
CAPTCHA field requires unique , so why is this unique?
if it's the only thing, you can add Unique Index

agrees with the upstairs view that it is not necessary to query whether there is any repetition before each insertion, just add a unique index, as long as it is executed as many times as you want;
then the successful execution is not repeated, and it can be called again if it fails. This saves the time needed for each query, and the efficiency will be improved a lot

there are return in the code, not finished, and there is also a unique index in the data table, which is the only business need. What I would like to ask is is there any other optimization method?

first of all, since it needs to be unique, it can be solved by adding a unique index, and then recursively executed if it fails. And since the CAPTCHA is defined by you, then the generation rules are also in your hands. You should distinguish from the CAPTCHA generation rules, which basically ensures uniqueness, there is no need to consider too much, and simple problems can be dealt with simply.

Previous: How to get the IP address of the current client in vue

Next: There is only one result for node mysql connection query?

MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-1e8d034-1121.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-1e8d034-1121.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?