blocks|key|691565|text|here很好地描述了这个过程。然而，一些不适合拉丁语空间的字符已经一去不复返了。UTF-8是latin1的超集。而不是相反。大多数字符可以放入单字节空间，但任何未定义的字符都不能(请查看latin1列表-根据mysql的latin1定义，并不是所有的256个字符都已定义)|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|691566|entityMap|0|LINK|mutability|MUTABLE|url|http://web.archive.org/web/20130427040458/http://en.gentoo-wiki.com/wiki/Convert_latin1_to_UTF-8_in_MySQL^0|0|4|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

<a href="http://web.archive.org/web/20130427040458/http://en.gentoo-wiki.com/wiki/Convert_latin1_to_UTF-8_in_MySQL" rel="nofollow">here</a> 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)

blocks|key|691624|text|注意，在Mysql中，utf8字符集只是实际UTF8字符集的一个子集。为了节省一个字节的存储空间，Mysql团队决定只存储三个字节的UTF8字符，而不是完整的四个字节。这意味着一些东亚语言和表情符号并不完全受支持。为了确保可以存储所有UTF8字符，请使用utf8mb4数据类型，并在Mysql中使用utf8mb4_bin或utf8mb4_general_ci。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|691625|entityMap^0|B|4|3J|7|45|B|4H|I|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]|$9|K|A|L|B|C]|$9|M|A|N|B|C]|$9|O|A|P|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|G|$]]

Beware that in Mysql, the <code>utf8</code> 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 <code>utf8mb4</code> data type, and <code>utf8mb4_bin</code> or <code>utf8mb4_general_ci</code> in Mysql.

blocks|key|691710|text|下面是如何更改所有数据库/表/列的方法。运行这些查询，它们将输出将整个模式转换为utf8所需的所有后续查询。希望这能有所帮助！|type|unstyled|depth|inlineStyleRanges|entityRanges|data|691711|--更改数据库默认排序规则|691712|SELECT+DISTINCT+concat('ALTER+DATABASE+`',+TABLE_SCHEMA,+'`+CHARACTER+SET+utf8+COLLATE+utf8_unicode_ci;')
from+information_schema.tables
where+TABLE_SCHEMA+like++'database_name';|code-block|syntax|javascript|691713|--更改表排序规则/字符集|691714|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';|691715|--更改列排序规则/字符集|691716|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';|691717|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|V|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|W|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|P|5|F|7|Y|8|@]|9|@]|A|$G|H]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

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!

-- Change DATABASE Default Collation

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

-- Change TABLE Collation / Char Set

<pre><code>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';
</code></pre>

-- Change COLUMN Collation / Char Set

<pre><code>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';
</code></pre>

blocks|key|907835|text|除了David+Whittaker发布的内容之外，我还创建了一个查询，该查询生成完整的表和列的alter语句，这些语句将转换每个表。运行以下命令可能是个好主意|type|unstyled|depth|inlineStyleRanges|entityRanges|data|907836|设置会话group_concat_max_len+=+100000；|907837|首先，要确保您的组concat不会超过here所示的非常小的限制。|offset|length|907838|+++++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;|code-block|syntax|javascript|907839|前一个答案的不同之处在于它使用了utf8而不是ut8mb4，并且将t1.data_type与t1.CHARACTER_MAXIMUM_LENGTH结合使用不适用于枚举。此外，我的查询不包括视图，因为这些视图必须单独更改。|907840|我简单地使用Perl脚本将所有这些更改作为一个数组返回，并迭代它们，修复了太长的列(通常它们是varchar(256)，而数据通常只有20个字符，所以这是一个简单的修复方法)。|907841|从latin1+->+utf8mb4更改时，我发现一些数据已损坏。列中的latin1字符似乎是utf8编码的，在转换过程中可能会出错。我只是将alter之前和之后的列中的数据保存在内存中，并对它们进行比较，然后生成update语句来修复数据。|907842|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/20910166/mysql-concat-max-length^0|0|0|J|4|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|11|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|12|8|@]|9|@$F|13|G|14|1|15]]|A|$]]|$1|H|3|I|5|J|7|16|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|17|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|18|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|19|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|1A|8|@]|9|@]|A|$]]]|T|$U|$5|V|W|X|A|$Y|Z]]]]

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

SET SESSION group_concat_max_len = 100000;

first to make sure your group concat doesn't go over the very small limit as seen <a href="https://stackoverflow.com/questions/20910166/mysql-concat-max-length">here</a>.

<pre><code> 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;
</code></pre>

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.

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).

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.

Our previous programmer set the wrong collation in a table (Mysql). He set it up with Latin collation, when it should be UTF8, and now I have issues. Every record with Chinese and Japan character turn to ??? character.

Is possible to change collation and get back the detail of character?

How to change the CHARACTER SET (and COLLATION) throughout a database?

数据库

Perl

我们之前的程序员在表(Mysql)中设置了错误的排序规则。他用拉丁文校对来设置它，当它应该是UTF8的时候，现在我有问题了。每一张带有中文和日文字符的记录都会变成？性格。是否可以更改排序规则并恢复字符的详细信息？

问如何在整个数据库中更改字符集(和排序规则)？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在整个数据库中更改字符集(和排序规则)？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在整个数据库中更改字符集(和排序规则)？
EN