Laravel Where子句返回非预期结果的原因排查

我有两条App\Models\ModelName记录：

= Illuminate\Database\Eloquent\Collection {#4789
all: [
  App\Models\ModelName {#4790
    id: 2,
    reference: "3f716d49-48e5-46be-bb91-33b35a042b9f",
    user_id: 5,
    deliverer_id: null,
    amount: "5000.00",
    amountCharged: "5000.00",
    fees_applied: "[]",
    payment_method: "{\"wallet\": \"wallet\"}",
    delivery_address: "Location",
    region: null,
    lat: "xxxx",
    long: "xxxx",
    status: "pending",
    paymentstatus: "unpaid",
    delivered_at: null,
    created_at: "2023-03-12 13:33:20",
    updated_at: "2023-03-12 13:33:20",
    deleted_at: null,
  },
  App\Models\ModelName {#4791
    id: 9,
    reference: "2c101e9b-9bb8-4efe-9e12-3c94a19d658d",
    user_id: 2,
    deliverer_id: 6,
    amount: "5000.00",
    amountCharged: "5000.00",
    fees_applied: "[]",
    payment_method: "{\"card\": \"cardDetails\"}",
    delivery_address: "city",
    region: null,
    lat: "xxxxxxxxx",
    long: "xxxxxxxx",
    status: "accepted",
    paymentstatus: "paid",
    delivered_at: null,
    created_at: "2023-04-01 09:56:07",
    updated_at: "2023-04-02 00:01:34",
    deleted_at: null,
  },
],}

执行以下Tinker命令能正确获取目标数据：

App\Models\ModelName::where('reference', '2c101e9b-9bb8-4efe-9e12-3c94a19d658d')->get()
= Illuminate\Database\Eloquent\Collection {#4765
    all: [
      App\Models\ModelName {#4781
        id: 9,
        reference: "2c101e9b-9bb8-4efe-9e12-3c94a19d658d",
        user_id: 2,
        deliverer_id: 6,
        amount: "5000.00",
        amountCharged: "5000.00",
        fees_applied: "[]",
        payment_method: "{\"card\": \"cardDetails\"}",
        delivery_address: "city",
        region: null,
        lat: "xxxxxxx",
        long: "xxxxxx",
        status: "accepted",
        paymentstatus: "paid",
        delivered_at: null,
        created_at: "2023-04-01 09:56:07",
        updated_at: "2023-04-02 00:01:34",
        deleted_at: null,
      },
    ],
  }

但执行下面的命令本应返回空结果，却意外返回了id为2的记录：

App\Models\ModelName::Where('id', "2c101e9b-9bb8-4efe-9e12-3c94a19d658d")->get();
= Illuminate\Database\Eloquent\Collection {#4763
    all: [
      App\Models\ModelName {#4774
        id: 2,
        reference: "3f716d49-48e5-46be-bb91-33b35a042b9f",
        user_id: 5,
        deliverer_id: null,
        amount: "5000.00",
        amountCharged: "5000.00",
        fees_applied: "[]",
        payment_method: "{\"wallet\": \"wallet\"}",
        delivery_address: "city",
        region: null,
        lat: "xxxxxxxx",
        long: "xxxxxxxx",
        status: "pending",
        paymentstatus: "unpaid",
        delivered_at: null,
        created_at: "2023-03-12 13:33:20",
        updated_at: "2023-03-12 13:33:20",
        deleted_at: null,
      },
    ],
  }

我尝试过将参考值强转为字符串（比如(string)"2c101e9b-9bb8-4efe-9e12-3c94a19d658d"）、使用单引号包裹，结果都一样。进一步测试发现，如果把字符串开头的2去掉，查询就会返回空集合，符合预期。

已知reference字段是普通字符串类型，现在想知道这个现象的原因，无法直接访问MySQL数据库，不确定是PHP、Laravel还是MySQL导致的问题。

这个问题是MySQL的隐式类型转换导致的，和Laravel、PHP无关。

因为你的id字段是整数类型（从记录里的id值为数字可以判断），当你用字符串类型的UUID去匹配整数类型字段时，MySQL会自动把字符串转换成整数。转换规则是：读取字符串开头的连续数字字符，直到遇到第一个非数字字符为止，剩下的部分会被忽略。

比如你传入的"2c101e9b-9bb8-4efe-9e12-3c94a19d658d"，开头的数字是2，后面跟着非数字的c，所以转换后的值就是整数2，自然就匹配到了id为2的记录。

如果把开头的2去掉，比如传入"c101e9b-..."，转换后的整数是0，而你的表里没有id为0的记录，所以返回空集合，这也符合预期。

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