mysql - Alter table default character set 修改 MySQL 5.6 中的行
问题描述
我正在使用 MySQL 5.6，我想修改一个表的默认编码（从 latin1 到 utf8）而不修改现有的列和行。根据文档，我尝试了以下命令：
ALTER TABLE mytable DEFAULT CHARACTER SET utf8;
它修改了我的表的默认字符集编码并且没有修改列的排序规则，正如预期的那样，但我真的很惊讶地看到：
Query OK, 32141 rows affected (6.31 sec)
Records: 32141 Duplicates: 0 Warnings: 0
除了“受影响的 32141 行”之外，结果与您预期的一样，如下所示：
MySQL> select count(*) from mytable;
+----------+
| count(*) |
+----------+
| 32141 |
+----------+
1 row in set (0.01 sec)
MySQL> show table status like 'mytable';
+-----------------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-----------------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+---------+
| mytable | InnoDB | 10 | Compact | 16723 | 20798 | 347815936 | 0 | 21561344 | 15728640 | NULL | NULL | NULL | NULL | utf8_general_ci | NULL | partitioned | |
+-----------------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------------+----------+----------------+---------+
MySQL> show create table mytable;
CREATE TABLE `mytable` (
`ID` varchar(255) NOT NULL,
`COL1` double DEFAULT NULL,
`COL2` longtext CHARACTER SET latin1,
`COL3` datetime DEFAULT NULL,
`COL4` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
`COL5` int(11) DEFAULT NULL,
`COL6` datetime DEFAULT NULL,
`COL7` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
`COL8` datetime(3) NOT NULL,
`COL9` int(11) NOT NULL DEFAULT '-1',
`COL10` int(11) DEFAULT '0',
`COL11` double DEFAULT '0',
PRIMARY KEY (`ID`,`COL9`),
KEY `idx1` (`COL7`,`COL3`,`COL6`),
KEY `idx2` (`COL1`,`COL4`,`COL3`,`COL6`),
KEY `idx3` (`ID`,`COL3`,`COL6`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (`COL9`)
(PARTITION p0 VALUES LESS THAN (1) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (2) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (3) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (4) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (5) ENGINE = InnoDB,
PARTITION p5 VALUES LESS THAN (6) ENGINE = InnoDB,
PARTITION p6 VALUES LESS THAN (7) ENGINE = InnoDB,
PARTITION p7 VALUES LESS THAN (8) ENGINE = InnoDB,
PARTITION p8 VALUES LESS THAN (9) ENGINE = InnoDB,
PARTITION p9 VALUES LESS THAN (10) ENGINE = InnoDB,
PARTITION p10 VALUES LESS THAN (11) ENGINE = InnoDB,
PARTITION p11 VALUES LESS THAN (100) ENGINE = InnoDB,
PARTITION p12 VALUES LESS THAN (101) ENGINE = InnoDB,
PARTITION p13 VALUES LESS THAN (102) ENGINE = InnoDB,
PARTITION p14 VALUES LESS THAN (103) ENGINE = InnoDB,
PARTITION p15 VALUES LESS THAN (104) ENGINE = InnoDB,
PARTITION p16 VALUES LESS THAN (105) ENGINE = InnoDB,
PARTITION p17 VALUES LESS THAN (106) ENGINE = InnoDB,
PARTITION p18 VALUES LESS THAN (107) ENGINE = InnoDB,
PARTITION p19 VALUES LESS THAN (108) ENGINE = InnoDB,
PARTITION p20 VALUES LESS THAN (109) ENGINE = InnoDB,
PARTITION p21 VALUES LESS THAN (110) ENGINE = InnoDB,
PARTITION p22 VALUES LESS THAN (111) ENGINE = InnoDB,
PARTITION p23 VALUES LESS THAN (200) ENGINE = InnoDB,
PARTITION p24 VALUES LESS THAN (201) ENGINE = InnoDB,
PARTITION p25 VALUES LESS THAN (202) ENGINE = InnoDB,
PARTITION p26 VALUES LESS THAN (203) ENGINE = InnoDB,
PARTITION p27 VALUES LESS THAN (204) ENGINE = InnoDB,
PARTITION p28 VALUES LESS THAN (205) ENGINE = InnoDB,
PARTITION p29 VALUES LESS THAN (206) ENGINE = InnoDB,
PARTITION p30 VALUES LESS THAN (207) ENGINE = InnoDB,
PARTITION p31 VALUES LESS THAN (208) ENGINE = InnoDB,
PARTITION p32 VALUES LESS THAN (209) ENGINE = InnoDB,
PARTITION p33 VALUES LESS THAN (210) ENGINE = InnoDB,
PARTITION p34 VALUES LESS THAN (211) ENGINE = InnoDB,
PARTITION p35 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
MySQL> show full columns from mytable;
+--------------------------+--------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+--------------------------+--------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| ID | varchar(255) | latin1_swedish_ci | NO | PRI | NULL | | select,insert,update,references | |
| COL1 | double | NULL | YES | MUL | NULL | | select,insert,update,references | |
| COL2 | longtext | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | |
| COL3 | datetime | NULL | YES | | NULL | | select,insert,update,references | |
| COL4 | varchar(255) | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | |
| COL5 | int(11) | NULL | YES | | NULL | | select,insert,update,references | |
| COL6 | datetime | NULL | YES | | NULL | | select,insert,update,references | |
| COL7 | varchar(255) | latin1_swedish_ci | YES | MUL | NULL | | select,insert,update,references | |
| COL8 | datetime(3) | NULL | NO | | NULL | | select,insert,update,references | |
| COL9 | int(11) | NULL | NO | PRI | -1 | | select,insert,update,references | |
| COL10 | int(11) | NULL | YES | | 0 | | select,insert,update,references | |
| COL11 | double | NULL | YES | | 0 | | select,insert,update,references | |
+--------------------------+--------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
我的连接参数如下：
MySQL> show variables where variable_name like '%char%' or variable_name like '%collation%';
+--------------------------+--------------------------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| collation_connection | utf8mb4_general_ci |
| collation_database | utf8mb4_general_ci |
| collation_server | utf8mb4_general_ci |
+--------------------------+--------------------------------------------------+
注意：
- 数据是从 Java 应用程序创建的
- 在创建数据时，连接参数设置为 utf8
- 没有与此表链接的 FK
当我尝试使用一些新创建的表进行重现时，似乎没有修改行。见下文“0行受影响”：
MySQL> select count(*) from mytesttable;
+----------+
| count(*) |
+----------+
| 3 |
+----------+
3 row in set (0.10 sec)
MySQL> alter table mytesttable character set utf8;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
在数据创建过程中，我尝试将连接参数改回 latin1，但没有改变结果：仍然“0 行受影响”。
所以我的问题：
- 我对命令的理解正确吗？（它不应该修改行）
- 什么可以解释行在第一种情况下受到影响？
编辑
我刚刚发现如果我删除分区不会发生问题。
- 使用分区我得到“XXX 受影响的行”
- 没有分区，我得到“0 个受影响的行”
是预期的吗？
带有摘要的编辑 2
最初：
- 该表
latin1用作默认编码（列相同） - 连接被声明为
utf8
什么有效：
- 在任何
ALTER TABLE命令之前，像“é”这样的字符似乎被latin1编码（E9） - 运行命令
ALTER TABLE mytable CHARACTER SET utf8mb4;不修改数据（十六进制命令仍显示E9）该列仍被声明latin1。 - 运行命令
ALTER TABLE mytable MODIFY COL2 LONGTEXT CHARACTER SET utf8mb4将列更改为utf8mb4(C3A9)
到目前为止，一切都很好。
剩下的问题：
- 如何确保表中存在的所有数据都是
latin1? 我试过SELECT COL2 FROM mytable WHERE LENGTH(COL2) != CHAR_LENGTH(COL2) LIMIT 1了，我得到了 0 个结果。够了吗？ ALTER TABLE mytable CHARACTER SET utf8mb4;为什么当数据似乎没有被修改时，命令显示“32141 行受影响”？（当表在同一列上有分区和索引时会发生这种情况）- 按照上一点，更改表的默认编码是否安全（需要？）？还是我应该坚持修改列？
非常感谢你的帮助
解决方案
你搞得一团糟，这ALTER让情况变得更糟。
首先，声明了表列并声明latin1 了客户端正在使用的连接latin1（通过SET NAMES latin1）。é如果在客户端实际上是十六进制的，那会很好E9。但是客户端中的数据是UTF-8。这é两个字节C3A9也是作为 2 个 latin1 字符发送到数据库的。损坏并不明显，因为当您SELECTed.
后面的步骤通过将这些字节中的每一个都视为 latin1 并将它们转换为 utf8 来搞砸事情，因此是“双重”编码。
请参阅UTF-8 字符问题中的“Mojibake”和“双重编码” ；我看到的不是我存储的。如果您想尝试恢复数据，请参阅http://mysql.rjweb.org/doc.php/charcoll#fixes_for_various_cases中的相应案例
好吧，显然ALTER TABLE mytable DEFAULT CHARACTER SET utf8;不只是更改默认值，而是将表复制过来，并在这样做时引入了双重编码。
十多年来，我一直在研究 MySQL 字符集问题。这是我尚未观察到的新皱纹。
我很确定这character_set_system与您的问题无关。（但我可能是错的！）
错误的设置名称
测试用例：
CREATE TABLE mytest ( MYDATA longtext ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET NAMES latin1;
INSERT INTO mytest VALUES ( "é" );
SELECT MYDATA, HEX(MYDATA) FROM mytest;
运行该测试用例：
mysql> SET NAMES latin1;
mysql> SHOW CREATE TABLE mytest\G
*************************** 1. row ***************************
Table: mytest
Create Table: CREATE TABLE `mytest` (
`MYDATA` longtext
) ENGINE=InnoDB DEFAULT CHARSET=latin1
mysql> INSERT INTO mytest VALUES ( "é" );
mysql> SELECT MYDATA, HEX(MYDATA), LENGTH(MYDATA),
CHAR_LENGTH(MYDATA) FROM mytest;
+--------+-------------+----------------+---------------------+
| MYDATA | HEX(MYDATA) | LENGTH(MYDATA) | CHAR_LENGTH(MYDATA) |
+--------+-------------+----------------+---------------------+
| é | C3A9 | 2 | 2 |
+--------+-------------+----------------+---------------------+
角色看起来不错。但是 HEX 看起来像 UTF-8，而不是 latin1。这CHAR_LENGTH是“错误的”。
情况是： CHARACTER SET latin1，但其中包含 utf8 字节。在修复字符集时不理会字节：
然后在不更改字节的情况下转换列：
ALTER TABLE tbl MODIFY COLUMN MYDATA LONGBLOB;
ALTER TABLE tbl MODIFY COLUMN MYDATA LONGTEXT CHARACTER SET utf8mb4;
（确保拥有您最初拥有的所有属性，例如NOT NULL。）
这是“2-step ALTER”，如http://mysql.rjweb.org/doc.php/charcoll中所述。）（确保其他规范保持相同 - VARCHAR、NOT NULL 等）
分区测试用例：
DROP TABLE IF EXISTS ptest;
CREATE TABLE ptest (
nn INT NOT NULL,
ee LONGTEXT
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY RANGE (nn)
(PARTITION p0 VALUES LESS THAN (1),
PARTITION p1 VALUES LESS THAN MAXVALUE);
SET NAMES latin1;
INSERT INTO ptest (nn, ee) VALUES ( 0, "é" ), ( 1, "ü" );
SELECT nn, ee, HEX(ee), LENGTH(ee), CHAR_LENGTH(ee) FROM ptest;
ALTER TABLE ptest
DEFAULT CHARSET utf8;
SELECT nn, ee, HEX(ee), LENGTH(ee), CHAR_LENGTH(ee) FROM ptest;
SELECT @@version;
SHOW CREATE TABLE ptest\G
分区结果：
mysql> DROP TABLE IF EXISTS ptest;
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE ptest (
-> nn INT NOT NULL,
-> ee LONGTEXT
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
-> PARTITION BY RANGE (nn)
-> (PARTITION p0 VALUES LESS THAN (1),
-> PARTITION p1 VALUES LESS THAN MAXVALUE);
Query OK, 0 rows affected (0.03 sec)
mysql> SET NAMES latin1;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO ptest (nn, ee) VALUES ( 0, "é" ), ( 1, "ü" );
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT nn, ee, HEX(ee), LENGTH(ee), CHAR_LENGTH(ee) FROM ptest;
+----+------+---------+------------+-----------------+
| nn | ee | HEX(ee) | LENGTH(ee) | CHAR_LENGTH(ee) |
+----+------+---------+------------+-----------------+
| 0 | é | C3A9 | 2 | 2 |
| 1 | ü | C3BC | 2 | 2 |
+----+------+---------+------------+-----------------+
2 rows in set (0.00 sec)
mysql> ALTER TABLE ptest
-> DEFAULT CHARSET utf8;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT nn, ee, HEX(ee), LENGTH(ee), CHAR_LENGTH(ee) FROM ptest;
+----+------+---------+------------+-----------------+
| nn | ee | HEX(ee) | LENGTH(ee) | CHAR_LENGTH(ee) |
+----+------+---------+------------+-----------------+
| 0 | é | C3A9 | 2 | 2 |
| 1 | ü | C3BC | 2 | 2 |
+----+------+---------+------------+-----------------+
2 rows in set (0.00 sec)
mysql> SELECT @@version;
+-----------------+
| @@version |
+-----------------+
| 5.6.22-71.0-log |
+-----------------+
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE ptest\G
*************************** 1. row ***************************
Table: ptest
Create Table: CREATE TABLE `ptest` (
`nn` int(11) NOT NULL,
`ee` longtext CHARACTER SET latin1
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (nn)
(PARTITION p0 VALUES LESS THAN (1) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1 row in set (0.00 sec)
嗯...我没有看到 ALTER 问题。你用的是什么版本？你看到这个测试用例的问题了吗？
推荐阅读
- vba - 在 PowerPoint 中访问任务完成状态
- javascript - Nativescript 水平滚动效果很好，但不适用于项目模板
- autodesk-forge - 将 AutoDesk Forge 查看器环境从本地切换到 AutodeskProduction
- ios - 如果用户的信息不正确，如何阻止用户通过登录页面 IOS
- python-3.x - ValueError：尝试在python中对列表进行排序时无法将列转换为布尔值
- authentication - 我的 nuxt-auth 令牌配置正确，但令牌从未存储
- c++ - 如何为 c/c++ 程序生成没有崩溃/段错误的核心转储文件
- android - ArrayList$Itr.next 抛出错误 concurrentModificationException
- python-3.x - Python：在类调用中使用另一个类的函数
- c# - 如何在 C# 中制作移动、平滑的图像背景（Visual Studio 2019）