Laravel 8 用户注册时插入会员表数据报timeout-or-duplicate错误

开发用户会员系统时，注册流程可正常创建user表与usermeta表数据，但向membership表插入会员信息（会员编号、等级、状态等）时，始终出现local.INFO: timeout-or-duplicate错误。移除会员数据插入代码后，注册流程完全正常，尝试两种插入方式均无效。

Membership 模型 #

use Illuminate\Database\Eloquent\Model;
use Carbon\Carbon;
use App\Enums\MembershipStatus;

class Membership extends Model
{
    //use SoftDeletes;
    /**
     * @var bool
     */
    public $timestamps = false;

    protected $fillable = [
        'user_id',
        'membership',
        'membership_no',
        'status',
        'start_date',
        'expiring_date'
    ];

    protected $casts = [
        'start_date' => 'datetime',
        'expiring_date' => 'datetime',
        'deleted_at' => 'datetime',
    ];

    public function user()
    {
        return $this->belongsTo(User::class);
    }
}

User 模型 #

class User extends Authenticatable {
    use SoftDeletes;

    protected $fillable = [
        'name',
        'username',
        'email',
        'password',
        'role',
        'status',
        'refer',
        '2fa',
    ];

    protected $hidden = [
        'password',
        'remember_token',
    ];
    protected $casts = [
        'last_login' => 'datetime',
    ];

    public function user_metas() {
        return $this->hasMany(UserMeta::class);
    }

    public function membership() {
        return $this->hasOne(Membership::class);
    }
}

注册方法 #

public function register(RegistrationRequest $request) {
    if (has_recaptcha()) {
        RecaptchaService::verify($request);
    }

    $emailMetaCount = $this->settingsService->emailMetaCount($request->email);

    if ($emailMetaCount > 0) {
        throw ValidationException::withMessages(['email' => __("The chosen email is already registered with us. Please use a different email address.")]);
    }

    DB::beginTransaction();
    try {
        $data = array_map('strip_tags_map', $request->only('name', 'confirmation')) + $request->all();
        $newUser = $this->auth->createUser($data);

        if (! $newUser) {
            throw ValidationException::withMessages([
                'invalid' => __('An error occurred during registration, please try again later. If the issues continues, contact us.')
            ]);
        }

        if (User::count() > 1 && mandatory_verify()) {
            ProcessEmail::dispatch('users-confirm-email', $newUser);
        }

        DB::commit();

        return view('auth.checkout', compact('newUser'));
    } catch (\Exception $e) {
        DB::rollBack();
        throw ValidationException::withMessages([
            'invalid' => __('Sorry, due to technical issues, registration failed. Please try again after sometimes or contact us.')
        ]);
    }
}

AuthService 中的 createUser 方法 #

public function createUser($data, $autoVerified = false) {
    $data['username'] = $this->generateUniqueUsername(first_word($data['name']));
    $data['status'] = ($autoVerified) ? UserStatus::ACTIVE : UserStatus::INACTIVE;
    $data['password'] = Hash::make($data['password']);
    $data['role'] = (isset($data['role'])) ? $data['role'] : Roles::USER;

    if ($data['role'] == Roles::USER && $this->referralService->getReferMeta()) {
        $data['refer'] = json_encode($this->referralService->getReferMeta());
    }

    $user = new User();
    $user->fill($data);
    $user->save();
    $this->saveDefaultUserMeta($user, $data['registration_method'] ?? null, $data);

    Membership::create([
        'user_id' => $user->id,
        'membership' => $data['membership_level'],
        'membership_no' => generate_unique_no(16),
        'status' => UserStatus::INACTIVE,
        'start_date' => Carbon::now(),
        'expiring_date' => Carbon::now()->addYear()
    ]);

    $verifyToken = $this->createVerifyToken($user);
    if ($autoVerified) {
        $verifyToken->verify = Carbon::now();
        $verifyToken->save();

        if (referral_system() && !empty($user->refer)) {
            $this->referralService->createReferral($user);
        }

        $this->saveEmailVerificationMeta($user);
    }

    return $user;
}

尝试的另一种插入方式 #

$membershipData = [
     'user_id' => $user->id,
     'membership' => $data['membership_level'],
     'membership_no' => generate_unique_no(16),
     'status' => UserStatus::INACTIVE,
     'start_date' => Carbon::now(),
     'expiring_date' => Carbon::now()->addYear()
 ];
 $membership = new Membership();
 $membership->fill($membershipData);
 $membership->save();

1. 检查唯一键冲突 #

• 查看membership表的迁移文件，确认user_id是否设置了唯一约束（因为User与Membership是一对一关系，user_id需唯一），避免重复插入。
• 校验generate_unique_no(16)函数的实现，确保生成的会员编号真正唯一，可添加循环校验逻辑：

  function generate_unique_no($length) {
      do {
          $no = str_random($length);
      } while (Membership::where('membership_no', $no)->exists());
      return $no;
  }
  

2. 字段类型匹配校验 #

• 确认数据库中status字段类型（如varchar/int）与UserStatus枚举的取值一致，例如枚举值为字符串时，数据库字段需设为varchar；枚举为整数时，字段设为int。
• 检查start_date、expiring_date字段是否为datetime类型，确保Carbon实例能正确写入。

3. 修正模型配置 #

• 若Membership模型未启用软删除，删除casts中的'deleted_at' => 'datetime'配置，避免模型处理不存在的字段；若需软删除，取消注释use SoftDeletes;。

4. 捕获具体异常定位问题 #

在会员信息插入代码块外包裹异常捕获，打印具体错误信息，明确是超时还是重复冲突：

try {
    Membership::create([
        'user_id' => $user->id,
        'membership' => $data['membership_level'],
        'membership_no' => generate_unique_no(16),
        'status' => UserStatus::INACTIVE,
        'start_date' => Carbon::now(),
        'expiring_date' => Carbon::now()->addYear()
    ]);
} catch (\Illuminate\Database\QueryException $e) {
    \Illuminate\Support\Facades\Log::error('会员信息插入失败: ' . $e->getMessage());
    throw $e;
}

5. 优化事务性能 #

• 若generate_unique_no函数存在频繁数据库查询，可优化逻辑减少查询次数，或把编号生成逻辑移至事务外（需保证唯一性）。
• 检查saveDefaultUserMeta方法是否存在耗时操作，避免事务执行时间过长导致超时。

内容的提问来源于stack exchange，提问作者adeguk Loggcity