Laravel 5.4升级，从utf8转换为utf4mb [英] Laravel 5.4 upgrade, converting to utf4mb from utf8
问题描述
我正忙于将我的网站之一从5.3升级到Laravel 5.4.我注意到在浏览当前的Github存储库时，默认字符集和排序规则已从utf8更改为utf8mb4以便为表情符号提供支持.
I'm busy upgrading one of my sites to Laravel 5.4 from 5.3. I noticed when going through the current Github repository that the default character set and collation has been changed from utf8 to utf8mb4 in order to provide support for emojis.
我当前的数据库(MariaDB 10.0.29)当前设置为使用utf8，但是我想将其升级为使用utf8mb4.不幸的是，我找不到有关此过程的任何文档.
My current database (MariaDB 10.0.29) is currently set to use utf8 but I would however like to upgrade it to use utf8mb4. Unfortunately, I haven't been able to find any documentation regarding this process.
也许我想得太多，但是我本以为更改数据库的字符集和排序规则将需要一些工作，至少需要运行一些ALTER TABLE命令.
Perhaps I'm overthinking it, but I would have thought that changing the character set and collation of a database would require some work, at very least running some ALTER TABLE commands.
- 是否有必要更改数据库和表上的字符集和排序规则?还是只更改我的
config/database.php文件中的设置就足够了吗? - 如果是这样，请记住保留现有数据是必不可少的.任何人都能够提供有关如何实现此目标的示例迁移(或一些MySQL代码).
- Is it necessary to go change the character set and collation on the database and tables? Or would it suffice to simply change the settings in my
config/database.phpfile? - If so, would anyone be able to provide an example migration (Or some MySQL code) on how to achieve this, bearing in mind that preserving existing data is essential.
谢谢
推荐答案
好吧，我已经编写了一个迁移程序来为自己的系统实现这一目标.
Alrighty then, I have written a migration to achieve this for my own system.
-
它允许您有选择地指定连接名称以引用默认值以外的其他连接.
It allows you to optionally specify a connection name to reference a connection other than the default.
它使用SHOW TABLES查询从连接数据库中获取表列表.
It gets the list of tables from the connection's database using a SHOW TABLES query.
然后循环遍历每个表，并将所有字符串/字符类型列更新为新的字符集和排序规则.
It then loops through each table and updates all string/character type columns to the new character set and collation.
我这样做了，所以必须提供一个回调以确定是否将列的长度更改为提供的新长度.在我的实现中，长度大于191的VARCHAR和CHAR列在向上迁移期间被更新为长度191，而长度恰好为191的VARCHAR和CHAR列在反向时更新为长度255.向下迁移.
I have made it so that a callback must be provided to determine whether or not a column should have it's length changed to the provided new length. In my implementation, VARCHAR and CHAR columns with lengths greater than 191 are updated to have length 191 during the up migration and VARCHAR and CHAR columns with length exactly 191 are updated to have length 255 on the reverse/down migration.
一旦所有字符串/字符列都已更新，将运行几个查询以更改表的字符集和排序规则，将所有剩余的排序规则转换为新的字符集，然后更改默认字符集和表的排序规则.
Once all the string/character columns have been updated, a couple of queries will be ran to change the charset and collation of the table, converting any remaining collations to the new one and then to change the default charset and collation of the table.
最后，将更改数据库的默认字符集和排序规则.
Finally, the database's default charset and collation will be changed.
-
最初，我试图将表简单地转换为新的编码，但是遇到列长度问题.在我的MySQL/MariaDB版本中使用InnoDB并更改表排序规则会导致错误时，
utf8mb4中的最大字符长度为191个字符.
Originally, I tried to simply convert the tables to the new encoding but ran into issues with column lengths. 191 characters is the maximum character length in
utf8mb4when using InnoDB in my version of MySQL/MariaDB and changing the table collation resulted in an error.
起初我只是想将长度更新为新长度，但我还想提供回滚功能，所以这不是一个选择，因为在反向方法中，我将一直设置utf8mb4到255的列，这太长了，所以我也选择更改排序规则.
I did at first only want to just update the lengths to the new length but I also wanted to provide a rollback feature, so this was not an option because in the reverse method I would have been setting the lengths of columns that were utf8mb4 to 255, which would have been too long, so I opted to change the collation too.
然后，我试图更改过长的varchar和char列的长度，字符集和排序规则，但是在我的系统中，当我使用包含以下内容的多列索引时，这会导致错误这样的专栏.显然，多列索引必须使用相同的排序规则.
I then tried to just change the length, charset and collation of varchar and char columns that were too long, but in my system, this resulted in errors when I had multi-column indexes that included such columns. Apparently, multi-column indexes must use the same collation.
重要提示，这是因为反向/向下迁移并不是每个人都100％完美的.我认为在迁移时不存储有关原始列的额外信息是不可能的.因此，我当前对反向/向下迁移的实现是假设长度为191的列最初为255.
An important note on this is that the reverse/down migration is not going to be 100% perfect for everyone. I don't think it would be possible to do so without storing extra information about the original columns when migrating. So my current implementation for the reverse/down migration is to assume that columns with length 191 were originally 255.
与此类似的重要说明是，这将盲目地将所有字符串/字符列的归类更改为新的归类，而与原始归类无关，因此，如果存在带有不同的排序规则，它们都会全部转换为新的排序规则，而相反的排序规则将执行相同的操作，而原始副本将不会保留.
A similarly important note on this is that this will blindly change the collations of all string/character columns to the new collation, regardless of the original collation, so if there are columns with different collations, they will all be converted to the new one and the reverse will do the same, the originals won't be preserved.
<?php
use Illuminate\Database\Migrations\Migration;
class UpgradeDatabaseToUtf8mb4 extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
$this->changeDatabaseCharacterSetAndCollation('utf8mb4', 'utf8mb4_unicode_ci', 191, function ($column) {
return $this->isStringTypeWithLength($column) && $column['type_brackets'] > 191;
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
$this->changeDatabaseCharacterSetAndCollation('utf8', 'utf8_unicode_ci', 255, function ($column) {
return $this->isStringTypeWithLength($column) && $column['type_brackets'] == 191;
});
}
/**
* Change the database referred to by the connection (null is the default connection) to the provided character set
* (e.g. utf8mb4) and collation (e.g. utf8mb4_unicode_ci). It may be necessary to change the length of some fixed
* length columns such as char and varchar to work with the new encoding. In which case the new length of such
* columns and a callback to determine whether or not that particular column should be altered may be provided. If a
* connection other than the default connection is to be changed, the string referring to the connection may be
* provided as the last parameter (This string will be passed to DB::connection(...) to retrieve an instance of that
* connection).
*
* @param string $charset
* @param string $collation
* @param null|int $newColumnLength
* @param Closure|null $columnLengthCallback
* @param string|null $connection
*/
protected function changeDatabaseCharacterSetAndCollation($charset, $collation, $newColumnLength = null, $columnLengthCallback = null, $connection = null)
{
$tables = $this->getTables($connection);
foreach ($tables as $table) {
$this->updateColumnsInTable($table, $charset, $collation, $newColumnLength, $columnLengthCallback, $connection);
$this->convertTableCharacterSetAndCollation($table, $charset, $collation, $connection);
}
$this->alterDatabaseCharacterSetAndCollation($charset, $collation, $connection);
}
/**
* Get an instance of the database connection provided with an optional string referring to the connection. This
* should be null if referring to the default connection.
*
* @param string|null $connection
*
* @return \Illuminate\Database\Connection
*/
protected function getDatabaseConnection($connection = null)
{
return DB::connection($connection);
}
/**
* Get a list of tables on the provided connection.
*
* @param null $connection
*
* @return array
*/
protected function getTables($connection = null)
{
$tables = [];
$results = $this->getDatabaseConnection($connection)->select('SHOW TABLES');
foreach ($results as $result) {
foreach ($result as $key => $value) {
$tables[] = $value;
break;
}
}
return $tables;
}
/**
* Given a stdClass representing the column, extract the required information in a more accessible format. The array
* returned will contain the field name, the type of field (Without the length), the length where applicable (or
* null), true/false indicating the column allowing null values and the default value.
*
* @param stdClass $column
*
* @return array
*/
protected function extractInformationFromColumn($column)
{
$type = $column->Type;
$typeBrackets = null;
$typeEnd = null;
if (preg_match('/^([a-z]+)(?:\\(([^\\)]+?)\\))?(.*)/i', $type, $matches)) {
$type = strtolower(trim($matches[1]));
if (isset($matches[2])) {
$typeBrackets = trim($matches[2]);
}
if (isset($matches[3])) {
$typeEnd = trim($matches[3]);
}
}
return [
'field' => $column->Field,
'type' => $type,
'type_brackets' => $typeBrackets,
'type_end' => $typeEnd,
'null' => strtolower($column->Null) == 'yes',
'default' => $column->Default,
'charset' => is_string($column->Collation) && ($pos = strpos($column->Collation, '_')) !== false ? substr($column->Collation, 0, $pos) : null,
'collation' => $column->Collation
];
}
/**
* Tell if the provided column is a string/character type and needs to have it's charset/collation changed.
*
* @param string $column
*
* @return bool
*/
protected function isStringType($column)
{
return in_array(strtolower($column['type']), ['char', 'varchar', 'tinytext', 'text', 'mediumtext', 'longtext', 'enum', 'set']);
}
/**
* Tell if the provided column is a string/character type with a length.
*
* @param string $column
*
* @return bool
*/
protected function isStringTypeWithLength($column)
{
return in_array(strtolower($column['type']), ['char', 'varchar']);
}
/**
* Update all of the string/character columns in the database to be the new collation. Additionally, modify the
* lengths of those columns that have them to be the newLength provided, when the shouldUpdateLength callback passed
* returns true.
*
* @param string $table
* @param string $charset
* @param string $collation
* @param int|null $newLength
* @param Closure|null $shouldUpdateLength
* @param string|null $connection
*/
protected function updateColumnsInTable($table, $charset, $collation, $newLength = null, Closure $shouldUpdateLength = null, $connection = null)
{
$columnsToChange = [];
foreach ($this->getColumnsFromTable($table, $connection) as $column) {
$column = $this->extractInformationFromColumn($column);
if ($this->isStringType($column)) {
$sql = "CHANGE `%field%` `%field%` %type%%brackets% CHARACTER SET %charset% COLLATE %collation% %null% %default%";
$search = ['%field%', '%type%', '%brackets%', '%charset%', '%collation%', '%null%', '%default%'];
$replace = [
$column['field'],
$column['type'],
$column['type_brackets'] ? '(' . $column['type_brackets'] . ')' : '',
$charset,
$collation,
$column['null'] ? 'NULL' : 'NOT NULL',
is_null($column['default']) ? ($column['null'] ? 'DEFAULT NULL' : '') : 'DEFAULT \'' . $column['default'] . '\''
];
if ($this->isStringTypeWithLength($column) && $shouldUpdateLength($column) && is_int($newLength) && $newLength > 0) {
$replace[2] = '(' . $newLength . ')';
}
$columnsToChange[] = trim(str_replace($search, $replace, $sql));
}
}
if (count($columnsToChange) > 0) {
$query = "ALTER TABLE `{$table}` " . implode(', ', $columnsToChange);
$this->getDatabaseConnection($connection)->update($query);
}
}
/**
* Get a list of all the columns for the provided table. Returns an array of stdClass objects.
*
* @param string $table
* @param string|null $connection
*
* @return array
*/
protected function getColumnsFromTable($table, $connection = null)
{
return $this->getDatabaseConnection($connection)->select('SHOW FULL COLUMNS FROM ' . $table);
}
/**
* Convert a table's character set and collation.
*
* @param string $table
* @param string $charset
* @param string $collation
* @param string|null $connection
*/
protected function convertTableCharacterSetAndCollation($table, $charset, $collation, $connection = null)
{
$query = "ALTER TABLE {$table} CONVERT TO CHARACTER SET {$charset} COLLATE {$collation}";
$this->getDatabaseConnection($connection)->update($query);
$query = "ALTER TABLE {$table} DEFAULT CHARACTER SET {$charset} COLLATE {$collation}";
$this->getDatabaseConnection($connection)->update($query);
}
/**
* Change the entire database's (The database represented by the connection) character set and collation.
*
* # Note: This must be done with the unprepared method, as PDO complains that the ALTER DATABASE command is not yet
* supported as a prepared statement.
*
* @param string $charset
* @param string $collation
* @param string|null $connection
*/
protected function alterDatabaseCharacterSetAndCollation($charset, $collation, $connection = null)
{
$database = $this->getDatabaseConnection($connection)->getDatabaseName();
$query = "ALTER DATABASE {$database} CHARACTER SET {$charset} COLLATE {$collation}";
$this->getDatabaseConnection($connection)->unprepared($query);
}
}
请，请先备份数据库，然后再运行此.使用后果自负！
这篇关于Laravel 5.4升级，从utf8转换为utf4mb的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
