Laravel/PHP中如何从MySQL数组字段查询单个值

Hey there! Let's figure out how to query that columnA field which stores values wrapped in square brackets (like [1,2,3]) for a specific value. The key here is avoiding false matches—we don't want to pull up a row with 11 when we're looking for 1, right?

Method 1: Use FIND_IN_SET with string trimming #

MySQL's FIND_IN_SET function is perfect for checking if a value exists in a comma-separated string, but first we need to strip off those square brackets from columnA. We can do this with the TRIM function to remove both [ and ] from the start and end of the field.

Here's the Laravel code for this approach:

$targetValue = '1';
// Replace YourModel with your actual model class name
$matchingRows = YourModel::whereRaw("FIND_IN_SET(?, TRIM(BOTH '[]' FROM columnA))", [$targetValue])->get();

This works because TRIM(BOTH '[]' FROM columnA) converts [1,2,3] to 1,2,3, then FIND_IN_SET checks if your target value is present in that cleaned-up list. It's clean, concise, and avoids partial value matches.

Method 2: Use LIKE with pattern matching #

If you prefer to stick with Laravel's query builder methods without raw SQL, you can use LIKE with specific patterns to cover all possible positions of your target value:

• At the start of the array: [1,...
• In the middle of the array: ...,1,...
• At the end of the array: ...,1]

Here's how that looks in code:

$targetValue = '1';
$matchingRows = YourModel::where(function ($query) use ($targetValue) {
    $query->where('columnA', 'like', "[{$targetValue},%")
          ->orWhere('columnA', 'like', "%,{$targetValue},%")
          ->orWhere('columnA', 'like', "%,{$targetValue}]");
})->get();

This covers all edge cases, so you won't get false positives from values like 11 or 21.

A quick note on database design #

Just a heads-up: storing comma-separated values in a single column isn't ideal for relational databases. It violates normalization rules, makes queries less efficient (especially with large datasets), and can complicate updates. If you have the option, consider refactoring this into a separate related table with one row per value—this will make your database operations cleaner and faster in the long run.

内容的提问来源于stack exchange，提问作者Shahnaouz Razu Sharif