Framework: Support for MySQL character set and collation at table and column level

Created on 23 May 2015 · 15Comments · Source: laravel/framework

I saw a closed PR that addresses this #879, but it appears to be outdated.

Request
It would be great to define mysql character set and collate at the table and column level to override the configuration when building models.

I would love to help out and any guidance on how to approach this would be much appreciated!

Use Case
In my localization project, I have a string_key column that needs to be case sensitive; so, I would set the character set to utf8 and collate utf8_bin.
For the translated text, I want to support full utf-8 character set and would want to use utf8mb4 and utf8mb4_collate_ci per https://mathiasbynens.be/notes/mysql-utf8mb4

I would like to generate the following schema in MySQL:

drop table if exists strings;
create table strings (
    string_id int unsigned auto_increment
    , string_key varchar(255) character set utf8 collate utf8_bin
    , string_text text character set utf8mb4 collate utf8mb4_unicode_ci
    , primary key(string_id)
    , unique key(string_key)
) character set utf8mb4 collate utf8mb4_unicode_ci;

Tweaking @bluematt's example...

class Create_Mixed_Charset_Table {

    /**
     * Make changes to the database.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('strings', function($table) {
            $table->charset('utf8mb4');
            $table->collate('utf8mb4_unicode_ci'); 

            $table->increments('string_id');

            // set the character set and collate at the column level
            $table->string('string_key')->charset('utf8')->collate('utf8_bin');

             // set the character set and collate at the column level
            $table->text('string_value')->charset('utf8mb4')->collate('utf8mb4_unicode_ci');
        });
    }

    /**
     * Revert the changes to the database.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('my_mixed_table');
    }

}

Source

abepark01

👍6

Most helpful comment

Guys solution found. I do not know which of laravel versions are you using, but mine is 5.3.
The Illuminate\Database\Schema\Blueprint::class, has not a Illuminate\Database\Schema\Blueprint::collation() neither Illuminate\Database\Schema\Blueprint::collate() or Illuminate\Database\Schema\Blueprint::charset(). Instead in Illuminate\Database\Schema\Blueprint::class you can access an public properties collation and charset. Try like this:

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

class CreateMyTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('my_table', function (Blueprint $table) {
            $table->collation = 'utf8_general_ci';
            $table->charset = 'utf8';
            $table->increments('id');
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('my_table');
    }
}

todstoychev on 13 Apr 2017

👍6 ❤2

All 15 comments

There is a charset modifier for the columns. Is this not what you're after?

jbrooksuk on 3 Jun 2015

I could be wrong, but I did not find references to setting charset and collate at the column level in the laravel/framework codebase, only the database level.
I did not find anything in the documentation either

abepark01 on 3 Jun 2015

eidng8 on 13 Jun 2015

any updates on this?

d-a-n on 8 Aug 2015

any updates on this?

I can't see it documented anywhere (source here), but I'm using code like this to create columns with specific collations:

$table->char('uuid', 36)->collate('ascii_bin');

fisharebest on 1 Sep 2015

👍2

Small note as this is one of the first results when searching for laravel collate: the function is in fact collation not collate as mentioned above.

mrm on 24 Nov 2015

👍2

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

class CreateMyTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('my_table', function (Blueprint $table) {
            $table->collation = 'utf8_general_ci';
            $table->charset = 'utf8';
            $table->increments('id');
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('my_table');
    }
}

todstoychev on 13 Apr 2017

👍6 ❤2

@todstoychev Is there a way to do something like that for a single column?

richRemer on 20 Jun 2017

@richRemer I think MySQL supports different collation for different columns, but I am not sure how can this be achieved in Laravel 5. You should try to find it. And the the only way is to dig in the code, because Laravel has no proper documentation for that case and it will be hard because of all the magic that is going on around.

todstoychev on 20 Jun 2017

If ->collate() did not work then use $column->collation, see https://github.com/laravel/framework/issues/3666#issuecomment-170334454

$column = $table->string('identifier')->unique();
$column->collation = 'utf8_bin';

tranghaviet on 2 Dec 2017

👍2

I think you should try utf8_general_ci

todstoychev on 2 Dec 2017

@todstoychev this is just a example.

tranghaviet on 3 Dec 2017

👍2

Heads up: Invoking $table->collation('foo')->change() doesn't change anything under 5.6/5.3. Maybe Laravel should support this as well? Or should I make another issue and reference this?