mysql修改字符集utf8_to_utf8mb4

官方文档：

https://dev.mysql.com/doc/refman/5.7/en/charset-unicode-conversion.html

修改mysql utf8mb3字符集为utf8mb4。

检查点

序号	检查点	检查方式
1	varchar字段定义总长度超过65535	select table_schema,table_name,sum(character_maximum_length)*4 from information_schema.columns where data_type = ‘varchar’ and table_schema not in (‘sys’,‘performance_schema’,‘information_schema’,‘mysql’,‘bakdata’) group by table_schema,table_name having sum(character_maximum_length) * 4 > 65530 ;
2	index字段定义总长度超过3072	select s.table_schema,s.table_name,s.index_schema,s.index_name,s.column_name,sum(c.character_maximum_length) * 4 from information_schema.statistics s left join information_schema.columns c on s.table_schema = c.table_schema and s.table_name = c.table_name and s.column_name = c.column_name where s.table_schema not in (‘sys’,‘performance_schema’,‘information_schema’,‘mysql’,‘bakdata’) group by s.table_schema,s.table_name,s.index_schema,s.index_name having sum(c.character_maximum_length) * 4 > 3072;
3	tinytext/text/mediutext/longtext字段	select concat(‘select ceil(max(length(ifnull(’,column_name,‘,0)))*4/3) from ‘,table_schema,’.’,table_name,‘;’) from information_schema.columns where data_type in (‘text’) and table_schema not in (‘sys’,‘performance_schema’,‘information_schema’,‘mysql’,‘bakdata’);
4	blob字段	select distinct data_typefrom information_schema.columnswhere table_schema not in (‘sys’,‘performance_schema’,‘information_schema’,‘mysql’,‘bakdata’)
5	字段级别字符集/collation设置	select table_schema,table_name,column_name,character_set_name,collation_name from information_schema.columns where ((character_set_name is not null and character_set_name <> ‘utf8’) or (collation_name is not null and collation_name<> ‘utf8_general_ci’)) and table_schema not in (‘sys’,‘performance_schema’,‘information_schema’,‘mysql’,‘bakdata’) ;

1、varchar字段/index长度/text类字段长度检查。

长度限制说明：https://dev.mysql.com/doc/refman/5.7/en/innodb-limits.html

index:

based on the limit of 3072 bytes for a 16KB page size. That is, the maximum index key length is 1536 bytes when the page size is 8KB, and 768 bytes when the page size is 4KB.

row:

The internal representation of a MySQL table has a maximum row size limit of 65,535 bytes

TEXT:

https://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html

TINYTEXT 256

TEXT 65536

MEDIUMTEXT 16777216

LONGTEXT 4294967296

修改方式：

1、修改字符集使用的语句，alter table convert/modify/defult 区别。

https://dev.mysql.com/doc/refman/5.7/en/alter-table.html

ALTER TABLE *tbl_name* CONVERT TO CHARACTER SET *charset_name*;

convert ：同时修改表级别默认设置和数据

modify default：之修改表级别默认设置，不修改数据。

2、加锁情况，线上执行的业务影响

排他锁，阻塞DML。

3、使用ghost方式遇到超长问题默认处理策略？

ghost使用insert ignore， 超长会自动截取后