如何更改数据库的默认排序规则?-Howtochangethedefaultcollationofadatabase?

297  

change database collation:

改变数据库排序规则:

ALTER DATABASE  CHARACTER SET utf8 COLLATE utf8_unicode_ci;

change table collation:

改变表排序:

ALTER TABLE  CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

change column collation:

改变列排序:

ALTER TABLE  MODIFY  VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;

More info:

更多信息:

• What are the diffrences between utf8_general_ci and utf8_unicode_ci?
• utf8_general_ci和utf8_unicode_ci之间的差异是什么?
• What's the difference between utf8_general_ci and utf8_unicode_ci
• utf8_general_ci和utf8_unicode_ci的区别是什么
• How to change collation of database, table, column?
• 如何更改数据库、表、列的排序?

30  

Heres how to change all databases/tables/columns. Run these queries and they will output all of the subsequent queries necessary to convert your entire schema to utf8. Hope this helps!

如何更改所有数据库/表/列。运行这些查询，它们将输出将整个模式转换为utf8所需的所有后续查询。希望这可以帮助!

-- Change DATABASE Default Collation

——更改数据库默认排序

SELECT DISTINCT concat('ALTER DATABASE `', TABLE_SCHEMA, '` CHARACTER SET utf8 COLLATE utf8_unicode_ci;')
from information_schema.tables
where TABLE_SCHEMA like  'database_name';

-- Change TABLE Collation / Char Set

——更改表排序/字符集

SELECT concat('ALTER TABLE `', TABLE_SCHEMA, '`.`', table_name, '` CHARACTER SET utf8 COLLATE utf8_unicode_ci;')
from information_schema.tables
where TABLE_SCHEMA like 'database_name';

-- Change COLUMN Collation / Char Set

——更改列排序/ Char集合

SELECT concat('ALTER TABLE `', t1.TABLE_SCHEMA, '`.`', t1.table_name, '` MODIFY `', t1.column_name, '` ', t1.data_type , '(' , t1.CHARACTER_MAXIMUM_LENGTH , ')' , ' CHARACTER SET utf8 COLLATE utf8_unicode_ci;')
from information_schema.columns t1
where t1.TABLE_SCHEMA like 'database_name' and t1.COLLATION_NAME = 'old_charset_name';

16  

Beware that in Mysql, the utf8 character set is only a subset of the real UTF8 character set. In order to save one byte of storage, the Mysql team decided to store only three bytes of a UTF8 characters instead of the full four-bytes. That means that some east asian language and emoji aren't fully supported. To make sure you can store all UTF8 characters, use the utf8mb4 data type, and utf8mb4_bin or utf8mb4_general_ci in Mysql.

注意，在Mysql中，utf8字符集只是真正的utf8字符集的一个子集。这意味着一些东亚语言和表情符号没有得到充分支持。要确保能够存储所有UTF8字符，请使用utf8mb4数据类型，以及使用utf8mb4_bin或utf8mb4_general_ci在Mysql中。

4  

Adding to what David Whittaker posted, I have created a query that generates the complete table and columns alter statement that will convert each table. It may be a good idea to run

除了David Whittaker所发布的内容之外，我还创建了一个查询，生成将转换每个表的完整表和列alter语句。跑步也许是个好主意

SET SESSION group_concat_max_len = 100000;

设置会话group_concat_max_len = 100000;

first to make sure your group concat doesn't go over the very small limit as seen here.

首先要确保你的组不会超过这里看到的很小的限制。

     SELECT a.table_name, concat('ALTER TABLE ', a.table_schema, '.', a.table_name, ' DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci, ',
        group_concat(distinct(concat(' MODIFY ',  column_name, ' ', column_type, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ', if (is_nullable = 'NO', ' NOT', ''), ' NULL ',
        if (COLUMN_DEFAULT is not null, CONCAT(' DEFAULT \'', COLUMN_DEFAULT, '\''), ''), if (EXTRA != '', CONCAT(' ', EXTRA), '')))), ';') as alter_statement
    FROM information_schema.columns a
    INNER JOIN INFORMATION_SCHEMA.TABLES b ON a.TABLE_CATALOG = b.TABLE_CATALOG
        AND a.TABLE_SCHEMA = b.TABLE_SCHEMA
        AND a.TABLE_NAME = b.TABLE_NAME
        AND b.table_type != 'view'
    WHERE a.table_schema = ? and (collation_name = 'latin1_swedish_ci' or collation_name = 'utf8mb4_general_ci')
    GROUP BY table_name;

A difference here between the previous answer is it was using utf8 instead of ut8mb4 and using t1.data_type with t1.CHARACTER_MAXIMUM_LENGTH didn't work for enums. Also, my query excludes views since those will have to altered separately.

前一个答案的不同之处在于它使用的是utf8而不是ut8mb4，使用的是t1。data_type t1。CHARACTER_MAXIMUM_LENGTH对枚举无效。此外，我的查询不包括视图，因为视图必须分别修改。

I simply used a Perl script to return all these alters as an array and iterated over them, fixed the columns that were too long (generally they were varchar(256) when the data generally only had 20 characters in them so that was an easy fix).

我简单地使用一个Perl脚本来将所有这些alters作为数组返回，并遍历它们，固定那些太长的列(通常它们是varchar(256)，因为数据通常只有20个字符，所以很容易修复)。

I found some data was corrupted when altering from latin1 -> utf8mb4. It appeared to be utf8 encoded latin1 characters in columns would get goofed in the conversion. I simply held data from the columns I knew was going to be an issue in memory from before and after the alter and compared them and generated update statements to fix the data.

我发现在改变latin1 -> utf8mb4时，一些数据被破坏。似乎是utf8编码的latin1列字符在转换过程中会出错。我简单地从我知道的列中获取数据，这将是在修改和比较它们之前和之后的内存中的一个问题，然后生成更新语句来修复数据。

3  

here describes the process well. However, some of the characters that didn't fit in latin space are gone forever. UTF-8 is a SUPERSET of latin1. Not the reverse. Most will fit in single byte space, but any undefined ones will not (check a list of latin1 - not all 256 characters are defined, depending on mysql's latin1 definition)

这里很好地描述了这个过程。然而，一些不适合拉丁文的字符却永远消失了。UTF-8是latin1的超集。而不是相反。大多数都适用于单个字节空间，但任何未定义的都不适用(查看latin1列表——根据mysql的latin1定义，并不是所有的256个字符都被定义)