如何在整个数据库中更改 CHARACTER SET(和 COLLATION)? [英] How to change the CHARACTER SET (and COLLATION) throughout a database?

问题描述

我们以前的程序员在表(Mysql)中设置了错误的排序规则.他用拉丁语排序设置它，当它应该是 UTF8 时，现在我有问题.有中日字的每张唱片都转为???字符.

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?

推荐答案

更改数据库排序规则:

ALTER DATABASE <database_name> CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

更改表格整理:

ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

更改列排序规则:

ALTER TABLE <table_name> MODIFY <column_name> VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

---

utf8mb4_0900_ai_ci 的各个部分是什么意思?

---

What do the parts of utf8mb4_0900_ai_ci mean?

3 bytes -- utf8
4 bytes -- utf8mb4 (new)

v4.0 --   _unicode_
v5.20 --  _unicode_520_
v9.0 --   _0900_ (new)

_bin      -- just compare the bits; don't consider case folding, accents, etc
_ci       -- explicitly case insensitive (A=a) and implicitly accent insensitive (a=á)
_ai_ci    -- explicitly case insensitive and accent insensitive
_as (etc) -- accent-sensitive (etc)

_bin         -- simple, fast
_general_ci  -- fails to compare multiletters; eg ss=ß, somewhat fast
...          -- slower
_0900_       -- (8.0) much faster because of a rewrite

更多信息:

• 有什么区别utf8_general_ci 和 utf8_unicode_ci?
• utf8_general_ci 和 utf8_unicode_ci 有什么区别?
• 如何更改数据库、表、列的排序规则?
• utf8_general_ci 和 utf8_unicode_ci 有什么区别?

这篇关于如何在整个数据库中更改 CHARACTER SET(和 COLLATION)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！