How to set charset to particular column in migration of Yii2

Question

I have a migration in Yii2, where I try create a table. I set charset for table, but I don't know how to set charset for particular column.

For example:

$this->createTable('some_table', [
            'column_1' => $this->string(64)->notNull(),
            'column_2' => $this->integer()->notNull(),
            'column_3' => $this->integer(),
        ], 'CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE=InnoDB');

In above code I want to set charset "utf8-unicode-ci" for column_1. How to do that?

Bilbo

Answer 1

Use append().

$this->createTable('some_table', [
    'column_1' => $this->string(64)->notNull()->append('CHARACTER SET utf8 COLLATE utf8_unicode_ci'),
    'column_2' => $this->integer()->notNull(),
    'column_3' => $this->integer(),
], 'CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE=InnoDB');

Is it just an example? Because you don't have to set charset for a single column when it's the same as for the whole table.

Hevesy

Append is okay, when you have no other specifications for this col. But if you have e.g. a NOT NULL, the append wont workt to add charset, because in sql syntaxt this must be after the Type/size definition, but append comes always as last. – Teresita

Answer 2

For MySQL you can use the following trick:

$schema = $this->getDb()->getSchema();
$columnBase = $schema->createColumnSchemaBuilder($schema::TYPE_STRING, 255);
$columnExtension = $schema->createColumnSchemaBuilder('CHARACTER SET utf8 COLLATE utf8_bin');
$columnExtension->notNull();

$this->createTable('{{%table1}}', [
    'column1' => $columnBase . ' ' . $columnExtension,
]);

Hillary

Looks a bit complicated, but it works and it keeps the compatibility to other db types. – Teresita

Answer 3

In my case I added made an overwrite for the string-method like this:

/**
 * {@inheritDoc}
 *
 * Overwrites the string function to add a way to define a charset
 * It creates an anonym class to overwrite buildCompleteString()
 * and append the charset after the '{length}' placeholder
 *
 * @see ColumnSchemaBuilder::buildCompleteString()
 */
public function string($length = null, $charset = null) {
    $type = new class(Schema::TYPE_STRING, $length, $this->getDb()) extends ColumnSchemaBuilder {
        public $charset = null;

        function buildCompleteString($format) {
            $charsetSql = $this->charset !== null ? ' CHARACTER SET ' . $this->charset: '';

            return parent::buildCompleteString(
                strtr($format, ['{length}' => "{length}{$charsetSql}"])
            );
        }
    };

    $type->charset = $charset;
    return $type;
}

This method creates an anonym class to modify format in the buildCompleteString() method of the SchemaBuilder. Because charset and colation must be after the col type/length, I replace the {lenght} placeholder and add the charset after this.

The good point is, when you once have this method (e.g. in your own migration class which inherits from the basic migration class) you can use this method on each col to specify even different charsets for each col. E.g. like this

$this->createTable('some_table', [
    'column_1' => $this->string(64, 'utf8 COLLATE utf8_unicode_ci')->notNull(),
    'column_2' => $this->integer()->notNull(),
    'column_3' => $this->integer(),
    'column_4' => $this->string(64, 'binary')->notNull(),
]);

The only possible downside of this could be, when you try to use this code for multiple database types that maybe have a different syntax for defining charsets and collation.

Teresita