← Back to blog

MySQL Generated Columns in Laravel

| Laravel

Imagine you have a users table with first_name and last_name columns. You constantly find yourself concatenating them:

$user->first_name . ' ' . $user->last_name;

Or using an Eloquent accessor:

// App\Models\User
    
use Illuminate\Database\Eloquent\Casts\Attribute;

protected function fullName(): Attribute
{
    return Attribute::make(
        get: fn () => $this->first_name . ' ' . $this->last_name,
    );
}

This works fine for display purposes, but what if you need to search by full name? You would end up doing something like this:

User::query()
    ->whereRaw("CONCAT(first_name, ' ', last_name) LIKE ?", ["%{$term}%"])
    ->get();

This query cannot use any index, which means a full table scan every time. With a large dataset, this becomes a real performance problem.

If you are not familiar with the LIKE operator and how it affects performance, check out my post on Eloquent Performance: Faster LIKE searching.

MySQL Generated Columns

MySQL supports generated columns, columns whose values are automatically computed from an expression. There are two types:

  • Virtual: computed on the fly when queried, not physically stored.
  • Stored: computed when a row is inserted or updated, and physically stored on disk.

The key advantage of stored generated columns is that you can index them, which makes searching significantly faster.

Here is the raw SQL:

ALTER TABLE users
ADD full_name VARCHAR(255)
GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) STORED
AFTER last_name;

Now full_name is always in sync with first_name and last_name. You never need to manually update it.

Laravel Migration

Create a migration:

php artisan make:migration add_full_name_to_users_table

Laravel provides the storedAs modifier for exactly this purpose:

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::table('users', function (Blueprint $table) {
            $table->string('full_name')
                ->storedAs("CONCAT(first_name, ' ', last_name)")
                ->after('last_name');
        });
    }

    public function down(): void
    {
        Schema::table('users', function (Blueprint $table) {
            $table->dropColumn('full_name');
        });
    }
};

The storedAs method tells Laravel to create a stored generated column. If you prefer a virtual column (not physically stored), use virtualAs instead:

$table->string('full_name')
    ->virtualAs("CONCAT(first_name, ' ', last_name)")
    ->after('last_name');

Virtual columns cannot be indexed in MySQL. Use storedAs if you plan to search or sort by the generated column.

Indexing the Generated Column

Since we are using a stored column, we can add an index to make searching fast:

Schema::table('users', function (Blueprint $table) {
    $table->string('full_name')
        ->storedAs("CONCAT(first_name, ' ', last_name)")
        ->after('last_name');

    $table->index('full_name');
});

Now you can search by full name efficiently:

User::query()
    ->where('full_name', 'like', $term.'%')
    ->get();

This query uses the full_name index because the wildcard is only at the end. Remember, a leading % wildcard prevents index usage.

Eloquent Considerations

Generated columns are read-only. You cannot assign a value to them:

// This will throw a MySQL error
$user->full_name = 'John Doe';
$user->save();

MySQL will reject any attempt to write to a generated column. To prevent accidental writes, you can add full_name to the $guarded array on your model, or exclude it from $fillable.

You also do not need an accessor anymore. Since full_name is a real column, Eloquent hydrates it automatically:

$user = User::find(1);

echo $user->full_name; // "Ahmad Mayahi"

Watch Out for replicate()

If you use replicate() to clone a model that has generated columns, MySQL will throw an error. The replicate() method copies all attributes — including the generated column — and tries to insert them. Since generated columns are read-only, the insert fails.

You need to exclude generated columns when calling replicate():

$clone = $user->replicate(except: ['full_name']);
$clone->save();

If you use generated columns heavily, override replicate() on your model so you do not have to remember this every time:

public function replicate(?array $except = null): static
{
    $except = array_merge($except ?? [], ['full_name']);

    return parent::replicate($except);
}

This way, $user->replicate() always excludes the generated column automatically, and you can still pass additional columns to exclude when needed.

Thanks for reading!

Share
← Back to blog