BoobooWei

---

title: rdsfor_mysql_5.6修改字符编码

命令准备

修改库的字符编码和校验码，RDS控制台刷新后自动变更

ALTER DATABASE db1 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

修改表的字符集和校验码

ALTER TABLE t1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

修改表后，字段自动变更

查看指定数据库字符集明细

SELECT table_name, CHARACTER_SET_NAME,COLLATION_NAME
FROM information_schema.TABLES AS T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` AS C
WHERE C.collation_name = T.table_collation
AND T.table_schema = 'yourdb'
AND
(
C.CHARACTER_SET_NAME != 'utf8mb4'
OR
C.COLLATION_NAME != 'utf8mb4_unicode_ci'
);

SELECT 
    table_schema,
    table_name,
    COLUMN_NAME,
    CHARACTER_SET_NAME,
    COLLATION_NAME
FROM
    information_schema.columns
WHERE
    table_schema = 'yourdb'
        AND (CHARACTER_SET_NAME != 'utf8mb4'
        OR COLLATION_NAME != 'utf8mb4_unicode_ci');

生成修改字符集的命令

SELECT CONCAT('ALTER TABLE ', table_name, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;')
FROM information_schema.TABLES AS T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` AS C
WHERE C.collation_name = T.table_collation
AND T.table_schema = 'ecshoptest'
AND
(
 C.CHARACTER_SET_NAME != 'utf8mb4'
 OR
 C.COLLATION_NAME != 'utf8mb4_unicode_ci'
);

参考

confluence业务

测试

-- 查看指定列的字符集和排序规则
select table_schema,table_name,COLUMN_NAME,CHARACTER_SET_NAME,COLLATION_NAME from information_schema.columns where table_schema='booboo' and table_name='t2';
-- 变更表和列的字符集和排序规则
alter table t2  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

验证明细

root@MySQL-01 10:00:  [booboo]> select table_name,CHARACTER_SET_NAME,COLLATION_NAME from information_schema.columns where table_schema='booboo' and table_name='t2';
+------------+--------------------+--------------------+
| table_name | CHARACTER_SET_NAME | COLLATION_NAME     |
+------------+--------------------+--------------------+
| t2         | NULL               | NULL               |
| t2         | utf8mb4            | utf8mb4_unicode_ci |
+------------+--------------------+--------------------+
2 rows in set (0.00 sec)

root@MySQL-01 10:01:  [booboo]> select table_name,CHARACTER_SET_NAME,COLLATION_NAME from information_schema.columns where table_schema='booboo' and table_name='t2';
+------------+--------------------+--------------------+
| table_name | CHARACTER_SET_NAME | COLLATION_NAME     |
+------------+--------------------+--------------------+
| t2         | NULL               | NULL               |
| t2         | utf8mb4            | utf8mb4_unicode_ci |
+------------+--------------------+--------------------+
2 rows in set (0.01 sec)

root@MySQL-01 10:01:  [booboo]> select table_schema,table_name,COLUMN_NAME,CHARACTER_SET_NAME,COLLATION_NAME from information_schema.columns where table_schema='booboo' and table_name='t2';
+--------------+------------+-------------+--------------------+--------------------+
| table_schema | table_name | COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME     |
+--------------+------------+-------------+--------------------+--------------------+
| booboo       | t2         | id          | NULL               | NULL               |
| booboo       | t2         | name        | utf8mb4            | utf8mb4_unicode_ci |
+--------------+------------+-------------+--------------------+--------------------+
2 rows in set (0.00 sec)

root@MySQL-01 10:02:  [booboo]> alter table t2  CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@MySQL-01 10:02:  [booboo]> select table_schema,table_name,COLUMN_NAME,CHARACTER_SET_NAME,COLLATION_NAME from information_schema.columns where table_schema='booboo' and table_name='t2';
+--------------+------------+-------------+--------------------+-----------------+
| table_schema | table_name | COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME  |
+--------------+------------+-------------+--------------------+-----------------+
| booboo       | t2         | id          | NULL               | NULL            |
| booboo       | t2         | name        | utf8               | utf8_unicode_ci |
+--------------+------------+-------------+--------------------+-----------------+
2 rows in set (0.00 sec)

root@MySQL-01 10:02:  [booboo]> show create table t2\G;
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `name` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)

ERROR:
No query specified

root@MySQL-01 10:03:  [booboo]> alter table t2  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@MySQL-01 10:03:  [booboo]> show create table t2\G;
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `name` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

ERROR:
No query specified

root@MySQL-01 10:03:  [booboo]> select table_schema,table_name,COLUMN_NAME,CHARACTER_SET_NAME,COLLATION_NAME from information_schema.columns where table_schema='booboo' and table_name='t2';
+--------------+------------+-------------+--------------------+--------------------+
| table_schema | table_name | COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME     |
+--------------+------------+-------------+--------------------+--------------------+
| booboo       | t2         | id          | NULL               | NULL               |
| booboo       | t2         | name        | utf8mb4            | utf8mb4_unicode_ci |
+--------------+------------+-------------+--------------------+--------------------+
2 rows in set (0.00 sec)