要将二进制或非二进制字符串列转换为使用特定字符集，请使用 ALTER TABLE。要成功转换，必须满足以下任一条件：To convert a binary or nonbinary string column to use a particular character set, use ALTER TABLE. For successful conversion to occur, one of the following conditions must apply:

假设有一个表 t，其二进制列名为 col1，定义为 VARBINARY(50)。假设该列中的信息使用单一字符集编码，则可以将其转换为具有该字符集的非二进制列。例如，如果 col1 包含表示 greek 字符集字符的二进制数据，可以按如下方式转换：Suppose that a table t has a binary column named col1 defined as VARBINARY(50). Assuming that the information in the column is encoded using a single character set, you can convert it to a nonbinary column that has that character set. For example, if col1 contains binary data representing characters in the greek character set, you can convert it as follows:

ALTER TABLE t MODIFY col1 VARCHAR(50) CHARACTER SET greek;

如果你的原始列类型为 BINARY(50)，可以将其转换为 CHAR(50)，但转换后值的末尾会填充 0x00 字节，这可能不是你想要的。要移除这些字节，请使用 TRIM() 函数：If your original column has a type of BINARY(50), you could convert it to CHAR(50), but the resulting values are padded with 0x00 bytes at the end, which may be undesirable. To remove these bytes, use the TRIM() function:

UPDATE t SET col1 = TRIM(TRAILING 0x00 FROM col1);

假设表 t 有一个名为 col1 的非二进制列，定义为 CHAR(50) CHARACTER SET latin1，但你希望将其转换为使用 utf8mb4，以便可以存储多种语言的值。以下语句可以实现这一点：Suppose that table t has a nonbinary column named col1 defined as CHAR(50) CHARACTER SET latin1 but you want to convert it to use utf8mb4 so that you can store values from many languages. The following statement accomplishes this:

ALTER TABLE t MODIFY col1 CHAR(50) CHARACTER SET utf8mb4;

如果列包含不在两个字符集中的字符，转换可能会有损失。Conversion may be lossy if the column contains characters that are not in both character sets.

如果你有来自 MySQL 4.1 之前的旧表，并且非二进制列包含实际上使用不同于服务器默认字符集编码的值，则会出现一种特殊情况。例如，一个应用程序可能在某个列中存储了 sjis 值，即使 MySQL 的默认字符集不同。可以将该列转换为使用正确的字符集，但需要额外的步骤。假设服务器的默认字符集是 latin1，并且 col1 被定义为 CHAR(50)，但其内容为 sjis 值。第一步是将该列转换为二进制数据类型，这会去掉现有的字符集信息而不进行任何字符转换：A special case occurs if you have old tables from before MySQL 4.1 where a nonbinary column contains values that actually are encoded in a character set different from the server's default character set. For example, an application might have stored sjis values in a column, even though MySQL's default character set was different. It is possible to convert the column to use the proper character set but an additional step is required. Suppose that the server's default character set was latin1 and col1 is defined as CHAR(50) but its contents are sjis values. The first step is to convert the column to a binary data type, which removes the existing character set information without performing any character conversion:

ALTER TABLE t MODIFY col1 BLOB;

下一步是将该列转换为具有适当字符集的非二进制数据类型：The next step is to convert the column to a nonbinary data type with the proper character set:

ALTER TABLE t MODIFY col1 CHAR(50) CHARACTER SET sjis;

此过程要求表在升级到 MySQL 4.1 或更高版本后，未通过诸如 INSERT 或 UPDATE 等语句进行修改。在这种情况下，MySQL 会使用 latin1 存储列中的新值，而该列将包含 sjis 和 latin1 值的混合，无法正确转换。This procedure requires that the table not have been modified already with statements such as INSERT or UPDATE after an upgrade to MySQL 4.1 or higher. In that case, MySQL would store new values in the column using latin1, and the column would contain a mix of sjis and latin1 values and cannot be converted properly.

如果在最初创建列时指定了属性，那么在使用 ALTER TABLE 修改表时，也应指定这些属性。例如，如果您指定了 NOT NULL 和显式的 DEFAULT 值，那么在 ALTER TABLE 语句中也应提供它们。否则，得到的列定义将不包括这些属性。If you specified attributes when creating a column initially, you should also specify them when altering the table with ALTER TABLE. For example, if you specified NOT NULL and an explicit DEFAULT value, you should also provide them in the ALTER TABLE statement. Otherwise, the resulting column definition does not include those attributes.

要转换表中的所有字符列，可以使用 ALTER TABLE ... CONVERT TO CHARACTER SET charset 语句。请参见 第 15.1.9 节，“ALTER TABLE 语句”。To convert all character columns in a table, the ALTER TABLE ... CONVERT TO CHARACTER SET charset statement may be useful. See Section 15.1.9, “ALTER TABLE Statement”.