将 Laravel MYSQL 更改为 utf8mb4 以支持现有数据库中的表情符号

Question

提问by Neel

I am using Laravel 5.3and I have already set-up my production server. All DB Migrations were already created using the following database config:

我正在使用Laravel 5.3并且我已经设置了我的生产服务器。所有数据库迁移都已使用以下数据库配置创建：

'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', 'localhost'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => null,
        ],

But now, some of my users had reported that they get an error when they try to save a form that has emoji icons in them. After searching I found out that I need to set the mysql charset to utf8mb4for this to work so my config should have been something like this instead:

但是现在，我的一些用户报告说，当他们尝试保存包含表情符号图标的表单时出现错误。搜索后我发现我需要将 mysql 字符集设置utf8mb4为使其工作，所以我的配置应该是这样的：

'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', 'localhost'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => null,
        ],

Since this is in a production server, I cannot do migrate:refresh. So my questions are:

由于这是在生产服务器中，因此我无法执行migrate:refresh. 所以我的问题是：

How can I change my existing database I created using laravel migration to use utf8mb4instead of utf8and also update laravel on the same? Is there an easier way to do this?
If the above is possible, am I better off setting utf8mb4for all tables or only use that for the 2 table columns where I will really be using emoji.

如何更改我使用 laravel 迁移创建的现有数据库以使用utf8mb4而不是utf8同时更新 laravel？有没有更简单的方法来做到这一点？
如果以上是可能的，我最好utf8mb4为所有表格设置还是只将它用于我将真正使用表情符号的 2 个表格列。

Thank you for your help.

感谢您的帮助。

Answer 1

采纳答案by Saumini Navaratnam

Use raw mysql query to write the update table migration script and run php artisan migratecommand

use Illuminate\Database\Migrations\Migration;

class UpdateTableCharset extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up() {
            DB::unprepared('ALTER TABLE `table_name` CONVERT TO CHARACTER SET utf8mb4');
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down() {
            DB::unprepared('ALTER TABLE `table_name` CONVERT TO CHARACTER SET utf8');
    }
}

My personal preference, update table. I don't have proof to say it is better

使用原始mysql查询编写更新表迁移脚本并运行php artisan migrate命令

use Illuminate\Database\Migrations\Migration;

class UpdateTableCharset extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up() {
            DB::unprepared('ALTER TABLE `table_name` CONVERT TO CHARACTER SET utf8mb4');
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down() {
            DB::unprepared('ALTER TABLE `table_name` CONVERT TO CHARACTER SET utf8');
    }
}

我的个人喜好，更新表。我没有证据说它更好

Note : You still have to keep the database config to utf8mb4.

注意：您仍然必须将数据库配置保留为utf8mb4.

Hope this will help you

希望能帮到你

Answer 2

回答by Patrick Breen

Based on @insomniak-dev answer, but will only shrink varchar when they exceed limits AND are used in an index. Otherwise, they are converted, but size is left as is. If a column will be shrunk, it then checks if any data will be truncated.

基于@insomniak-dev 答案，但只会在超出限制并在索引中使用时缩小 varchar。否则，它们会被转换，但大小保持不变。如果列将被收缩，则它会检查是否有任何数据将被截断。

This also handles all text types and batches all conversions for each table into a single statement for speed.

这也处理所有文本类型并将每个表的所有转换批处理为单个语句以提高速度。

Dryrun flag outputs usable sql instead of applying directly.

Dryrun 标志输出可用的 sql 而不是直接应用。

/**
 * Run the migrations.
 *
 * @return void
 */
public function up()
{
    $dryRun = true;
    $this->convertDb('mysql', 'utf8mb4', 'utf8mb4_unicode_ci', $dryRun);
    $this->convertDb('archive', 'utf8mb4', 'utf8mb4_unicode_ci', $dryRun);
}

/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    $dryRun = true;
    $this->convertDb('archive', 'utf8', 'utf8_unicode_ci', $dryRun);
    $this->convertDb('mysql', 'utf8', 'utf8_unicode_ci', $dryRun);
}

private function convertDb($connection, $charset, $collate, $dryRun)
{
    $dbName = config("database.connections.{$connection}.database");

    $varchars = \DB::connection($connection)
        ->select(\DB::raw("select * from INFORMATION_SCHEMA.COLUMNS where DATA_TYPE = 'varchar' and (CHARACTER_SET_NAME != '{$charset}' or COLLATION_NAME != '{$collate}') AND TABLE_SCHEMA = '{$dbName}'"));
    // Check if shrinking field size will truncate!
    $skip = [];  // List of table.column that will be handled manually
    $indexed = [];
    if ($charset == 'utf8mb4') {
        $error = false;
        foreach($varchars as $t) {
            if ($t->CHARACTER_MAXIMUM_LENGTH > 191) {
                $key = "{$t->TABLE_NAME}.{$t->COLUMN_NAME}";

                // Check if column is indexed
                $index = \DB::connection($connection)
                    ->select(\DB::raw("SHOW INDEX FROM `{$t->TABLE_NAME}` where column_name = '{$t->COLUMN_NAME}'"));
                $indexed[$key] = count($index) ? true : false;

                if (count($index)) {
                    $result = \DB::connection($connection)
                        ->select(\DB::raw("select count(*) as `count` from `{$t->TABLE_NAME}` where length(`{$t->COLUMN_NAME}`) > 191"));
                    if ($result[0]->count > 0) {
                        echo "-- DATA TRUNCATION: {$t->TABLE_NAME}.{$t->COLUMN_NAME}({$t->CHARACTER_MAXIMUM_LENGTH}) => {$result[0]->count}" . PHP_EOL;
                        if (!in_array($key, $skip)) {
                            $error = true;
                        }
                    }
                }
            }
        }
        if ($error) {
            throw new \Exception('Aborting due to data truncation');
        }
    }

    $query = "SET FOREIGN_KEY_CHECKS = 0";
    $this->dbExec($query, $dryRun, $connection);

    $query = "ALTER SCHEMA {$dbName} DEFAULT CHARACTER SET {$charset} DEFAULT COLLATE {$collate}";
    $this->dbExec($query, $dryRun, $connection);

    $tableChanges = [];
    foreach($varchars as $t) {
        $key = "{$t->TABLE_NAME}.{$t->COLUMN_NAME}";
        if (!in_array($key, $skip)) {
            if ($charset == 'utf8mb4' && $t->CHARACTER_MAXIMUM_LENGTH > 191 && $indexed["{$t->TABLE_NAME}.{$t->COLUMN_NAME}"]) {
                $tableChanges["{$t->TABLE_NAME}"][] = "CHANGE `{$t->COLUMN_NAME}` `{$t->COLUMN_NAME}` VARCHAR(191) CHARACTER SET {$charset} COLLATE {$collate}";
                echo "-- Shrinking: {$t->TABLE_NAME}.{$t->COLUMN_NAME}({$t->CHARACTER_MAXIMUM_LENGTH})" . PHP_EOL;
            } else if ($charset == 'utf8' && $t->CHARACTER_MAXIMUM_LENGTH == 191) {
                $tableChanges["{$t->TABLE_NAME}"][] = "CHANGE `{$t->COLUMN_NAME}` `{$t->COLUMN_NAME}` VARCHAR(255) CHARACTER SET {$charset} COLLATE {$collate}";
                echo "-- Expanding: {$t->TABLE_NAME}.{$t->COLUMN_NAME}({$t->CHARACTER_MAXIMUM_LENGTH})";
            } else {
                $tableChanges["{$t->TABLE_NAME}"][] = "CHANGE `{$t->COLUMN_NAME}` `{$t->COLUMN_NAME}` VARCHAR({$t->CHARACTER_MAXIMUM_LENGTH}) CHARACTER SET {$charset} COLLATE {$collate}";
            }
        }
    }

    $texts = \DB::connection($connection)
        ->select(\DB::raw("select * from INFORMATION_SCHEMA.COLUMNS where DATA_TYPE like '%text%' and (CHARACTER_SET_NAME != '{$charset}' or COLLATION_NAME != '{$collate}') AND TABLE_SCHEMA = '{$dbName}'"));
    foreach($texts as $t) {
        $tableChanges["{$t->TABLE_NAME}"][] = "CHANGE `{$t->COLUMN_NAME}` `{$t->COLUMN_NAME}` {$t->DATA_TYPE} CHARACTER SET {$charset} COLLATE {$collate}";
    }

    $tables = \DB::connection($connection)
        ->select(\DB::raw("select * from INFORMATION_SCHEMA.TABLES where TABLE_COLLATION != '{$collate}' and TABLE_SCHEMA = '{$dbName}';"));
    foreach($tables as $t) {
        $tableChanges["{$t->TABLE_NAME}"][] = "CONVERT TO CHARACTER SET {$charset} COLLATE {$collate}";
        $tableChanges["{$t->TABLE_NAME}"][] = "DEFAULT CHARACTER SET={$charset} COLLATE={$collate}";
    }

    foreach ($tableChanges as $table => $changes) {
        $query = "ALTER TABLE `{$table}` ".implode(",\n", $changes);
        $this->dbExec($query, $dryRun, $connection);
    }

    $query = "SET FOREIGN_KEY_CHECKS = 1";
    $this->dbExec($query, $dryRun, $connection);

    echo "-- {$dbName} CONVERTED TO {$charset}-{$collate}" . PHP_EOL;
}

private function dbExec($query, $dryRun, $connection)
{
    if ($dryRun) {
        echo $query . ';' . PHP_EOL;
    } else {
        \DB::connection($connection)->getPdo()->exec($query);
    }
}

Answer 3

回答by Insomniak Dev

for anyone willing to achieve this on the whole database i found myself needing such a script :

对于任何愿意在整个数据库上实现这一点的人，我发现自己需要这样一个脚本：

<?php

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

class ChangeDbCharset extends Migration
{

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        $charset = "utf8mb4";
        $collate = $charset."_unicode_ci";
        $dbName = Config::get('database.connections.'.Config::get('database.default').'.database');
        $query = "ALTER SCHEMA $dbName DEFAULT CHARACTER SET $charset DEFAULT COLLATE $collate;\n"; 
        DB::connection()->getPdo()->exec($query);

        $dbName = Config::get('database.connections.'.Config::get('database.default').'.database');
        $result = DB::select(DB::raw('show tables'));
        $test = DB::select(DB::raw("select * from INFORMATION_SCHEMA.COLUMNS where DATA_TYPE = 'varchar' AND TABLE_SCHEMA = '$dbName';"));
        //var_dump($test);
        foreach($test as $t)
        {
            $query = "ALTER TABLE $t->TABLE_NAME CHANGE $t->COLUMN_NAME $t->COLUMN_NAME VARCHAR(191) CHARACTER SET $charset COLLATE $collate; \n";
            echo $query;
            DB::connection()->getPdo()->exec($query);
        }
        $test = DB::select(DB::raw("select * from INFORMATION_SCHEMA.COLUMNS where DATA_TYPE = 'text' AND TABLE_SCHEMA = '$dbName';"));
        foreach($test as $t)
        {
            $query = "ALTER TABLE $t->TABLE_NAME CHANGE $t->COLUMN_NAME $t->COLUMN_NAME TEXT CHARACTER SET $charset COLLATE $collate; \n";
            echo $query;
            DB::connection()->getPdo()->exec($query);
        }


        $result = DB::select(DB::raw('show tables'));
        foreach($result as $r)
        {
            foreach($r as $k => $t)
            {
                $query = "ALTER TABLE `$t` CONVERT TO CHARACTER SET $charset COLLATE $collate; \n";
                echo $query;
                DB::connection()->getPdo()->exec($query);
            }
        }
        echo "DB CHARSET set to $charset , $collate";
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        $charset = "utf8";
        $collate = $charset."_unicode_ci";
        $dbName = Config::get('database.connections.'.Config::get('database.default').'.database');
        $query = "ALTER SCHEMA $dbName DEFAULT CHARACTER SET $charset DEFAULT COLLATE $collate;\n"; 
        DB::connection()->getPdo()->exec($query);

        $dbName = Config::get('database.connections.'.Config::get('database.default').'.database');
        $result = DB::select(DB::raw('show tables'));
        $test = DB::select(DB::raw("select * from INFORMATION_SCHEMA.COLUMNS where DATA_TYPE = 'varchar' AND TABLE_SCHEMA = '$dbName';"));
        //var_dump($test);
        foreach($test as $t)
        {
            $query = "ALTER TABLE $t->TABLE_NAME CHANGE $t->COLUMN_NAME $t->COLUMN_NAME VARCHAR(255) CHARACTER SET $charset COLLATE $collate; \n";
            echo $query;
            DB::connection()->getPdo()->exec($query);
        }
        $test = DB::select(DB::raw("select * from INFORMATION_SCHEMA.COLUMNS where DATA_TYPE = 'text' AND TABLE_SCHEMA = '$dbName';"));
        foreach($test as $t)
        {
            $query = "ALTER TABLE $t->TABLE_NAME CHANGE $t->COLUMN_NAME $t->COLUMN_NAME TEXT CHARACTER SET $charset COLLATE $collate; \n";
            echo $query;
            DB::connection()->getPdo()->exec($query);
        }


        $result = DB::select(DB::raw('show tables'));
        foreach($result as $r)
        {
            foreach($r as $k => $t)
            {
                $query = "ALTER TABLE `$t` CONVERT TO CHARACTER SET $charset COLLATE $collate; \n";
                echo $query;
                DB::connection()->getPdo()->exec($query);
            }
        }
        echo "DB CHARSET set to $charset , $collate";
    }

}

将 Laravel MYSQL 更改为 utf8mb4 以支持现有数据库中的表情符号

提问by Neel

采纳答案by Saumini Navaratnam

回答by Patrick Breen

回答by Insomniak Dev

相关推荐

最近更新

标签

将 Laravel MYSQL 更改为 utf8mb4 以支持现有数据库中的表情符号

提问by Neel

采纳答案by Saumini Navaratnam

回答by Patrick Breen

回答by Insomniak Dev

相关推荐

laravel 未找到列：1054“字段列表”中的未知列“remember_token”？

Laravel 5.4.6 中的动态引导选项卡

如何在 Laravel 中捕获 PostTooLargeException？

laravel 找不到类“App\Http\Controllers\Session”

相关推荐

最近更新

标签