MySQL 字符编码更改。是否保留了数据完整性？

Question

我必须将数据库编码从 latin-1 转换为 utf-8。

我知道转换数据库是通过命令完成的

ALTER DATABASE db_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name]

Source并通过以下命令转换现有表:

ALTER TABLE tbl_name
    [[DEFAULT] CHARACTER SET charset_name]
    [COLLATE collation_name]

Source .

但是，该数据库已经存在，并且涉及敏感信息。我的问题是我已有的数据是否会改变。这个问题的目的是我在做改变之前必须给出一个估计。

Answer 1

每个(字符串类型)列都有其自己的字符集和排序规则元数据。

如果指定列的数据类型时(即上次创建或更改时)，没有显式给出字符集/排序规则，则表的默认字符集和排序规则将用于列。

如果在指定表时没有显式给出默认字符集/排序规则，则数据库的默认字符集和排序规则将用作表的默认字符集和排序规则。

您在问题中引用的命令仅分别更改数据库和表的默认字符集/排序规则。换句话说，它们只会影响此后创建的表和列，而不会影响现有列(或数据)。

要更新现有数据，您应该首先阅读Changing the Character Set手册页的 ALTER TABLE 部分:

Changing the Character Set

To change the table default character set and all character columns (CHAR, VARCHAR, TEXT) to a new character set, use a statement like this:

ALTER TABLE <em>tbl_name</em> CONVERT TO CHARACTER SET <em>charset_name</em>;

The statement also changes the collation of all character columns. If you specify no COLLATE clause to indicate which collation to use, the statement uses default collation for the character set. If this collation is inappropriate for the intended table use (for example, if it would change from a case-sensitive collation to a case-insensitive collation), specify a collation explicitly.

For a column that has a data type of VARCHAR or one of the TEXT types, CONVERT TO CHARACTER SET changes the data type as necessary to ensure that the new column is long enough to store as many characters as the original column. For example, a TEXT column has two length bytes, which store the byte-length of values in the column, up to a maximum of 65,535. For a latin1 TEXT column, each character requires a single byte, so the column can store up to 65,535 characters. If the column is converted to utf8, each character might require up to three bytes, for a maximum possible length of 3 × 65,535 = 196,605 bytes. That length does not fit in a TEXT column's length bytes, so MySQL converts the data type to MEDIUMTEXT, which is the smallest string type for which the length bytes can record a value of 196,605. Similarly, a VARCHAR column might be converted to MEDIUMTEXT.

To avoid data type changes of the type just described, do not use CONVERT TO CHARACTER SET. Instead, use MODIFY to change individual columns. For example:

ALTER TABLE t MODIFY latin1_text_col TEXT CHARACTER SET utf8;
ALTER TABLE t MODIFY latin1_varchar_col VARCHAR(<em>M</em>) CHARACTER SET utf8;

If you specify CONVERT TO CHARACTER SET binary, the CHAR, VARCHAR, and TEXT columns are converted to their corresponding binary string types (BINARY, VARBINARY, BLOB). This means that the columns no longer will have a character set attribute and a subsequent CONVERT TO operation will not apply to them.

If charset_name is DEFAULT in a CONVERT TO CHARACTER SET operation, the character set named by the character_set_database system variable is used.

 Warning

The CONVERT TO operation converts column values between the original and named character sets. This is not what you want if you have a column in one character set (like latin1) but the stored values actually use some other, incompatible character set (like utf8). In this case, you have to do the following for each such column:

ALTER TABLE t1 CHANGE c1 c1 BLOB;
ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;

The reason this works is that there is no conversion when you convert to or from BLOB columns.

要仅更改表的默认字符集，请使用以下语句:

ALTER TABLE <em>tbl_name</em> DEFAULT CHARACTER SET <em>charset_name</em>;

这个词DEFAULT是可选的。默认字符集是在您未指定稍后添加到表中的列的字符集时使用的字符集(例如，使用 ALTER TABLE ... ADD column )。

当 foreign_key_checks 启用系统变量(这是默认设置)，在包含外键约束中使用的字符串列的表上不允许进行字符集转换。解决方法是禁用 foreign_key_checks 在执行字符集转换之前。在重新启用 foreign_key_checks 之前，您必须对涉及外键约束的两个表执行转换。 。如果您重新启用 foreign_key_checks 仅转换其中一个表后，ON DELETE CASCADE或ON UPDATE CASCADE由于这些操作期间发生隐式转换，操作可能会损坏引用表中的数据(Bug #45290、Bug #74816)。