随笔- 35 文章- 0 评论- 1 阅读- 17653

mysql的CHARSET

社区版：mysql5.7: Latin1/latin1_swedish_ci mysql8.0: utf8mb4/utf8mb4_0900_ai_ci
RDS默认的是utf8

MySQL 8.0 默认的是 utf8mb4_0900_ai_ci，属于 utf8mb4_unicode_ci 中的一种，具体含义如下：

1) uft8mb4 表示用 UTF-8 编码方案，每个字符最多占4个字节。

2) 0900 指的是 Unicode 校对算法版本。(Unicode归类算法是用于比较符合Unicode标准要求的两个Unicode字符串的方法)

3) ai指的是口音不敏感。也就是说，排序时e，è，é，ê和ë之间没有区别。

4) ci表示不区分大小写。也就是说，排序时p和P之间没有区别。

注：如果需要重音灵敏度和区分大小写，则可以使用 utf8mb4_0900_as_cs

utf8mb4_unicode_ci: 是基于标准的Unicode来排序和比较，能够在各种语言之间精确排序
utf8mb4_general_ci: 没有实现Unicode排序规则，在遇到某些特殊语言或字符是，排序结果可能不是所期望的。

#1.没主键的表
SELECT CONCAT(aa.TABLE_SCHEMA,'.',aa.TABLE_NAME) Table_names
FROM information_schema. TABLES aa
where aa.TABLE_SCHEMA not in('sys','information_schema','performance_schema','mysql')
   AND TABLE_NAME not in
   (SELECT table_name
    FROM information_schema.table_constraints t
         JOIN information_schema.key_column_usage k 
         USING (constraint_name,table_schema,table_name)
    WHERE t.constraint_type = 'PRIMARY KEY'
          AND t.table_schema not in('sys','information_schema','performance_schema','mysql')
   );

#2.有外键、check约束的表
SELECT table_name,k.COLUMN_NAME,k.CONSTRAINT_NAME
FROM information_schema.table_constraints t
     JOIN information_schema.key_column_usage k 
     USING (constraint_name,table_schema,table_name)
WHERE t.constraint_type in ('CHECK','FOREIGN KEY')
      AND t.table_schema not in('sys','information_schema','performance_schema','mysql');

#3.是否有字符字段排序字符集不是utf8mb4_unicode_ci的
select TABLE_SCHEMA,COLLATION_NAME,count(COLUMN_NAME) COL_NUM
from information_schema.`COLUMNS` bb
where TABLE_SCHEMA not in ('information_schema','mysql','performance_schema','sys','test')
      and COLLATION_NAME is not null
group by TABLE_SCHEMA,COLLATION_NAME
order by TABLE_SCHEMA,COLLATION_NAME;

#3.0存在从ecs库同步到RDS，会把utf8改变成utf8mb3
select * from information_schema.TABLES t 
where TABLE_SCHEMA ='park' and TABLE_COLLATION ='utf8_general_ci'

#3.1查看是否会被截断，如果修改排序字符集
select table_name,data_type,character_maximum_length
from information_schema.`COLUMNS` bb
where TABLE_SCHEMA not in ('information_schema','mysql','performance_schema','sys','test')
      and COLLATION_NAME is not null and COLLATION_NAME='utf8mb4_0900_ai_ci'
group by TABLE_SCHEMA,TABLE_NAME,COLLATION_NAME 
order by table_name

#3.2查看存在的字段字符集
select DISTINCT COLLATION_NAME from (
select TABLE_SCHEMA,TABLE_NAME,COLLATION_NAME,count(COLLATION_NAME) COL_NUM
from information_schema.`COLUMNS` bb
where TABLE_SCHEMA not in ('information_schema','mysql','performance_schema','sys','test')
      and COLLATION_NAME is not null
group by TABLE_SCHEMA,TABLE_NAME,COLLATION_NAME      ) aa
order by TABLE_SCHEMA,TABLE_NAME,COLLATION_NAME;

#4.表排序字符集不是utf8mb4_unicode_ci的
select ee.TABLE_SCHEMA,ee.TABLE_COLLATION,COUNT(ee.TABLE_NAME) table_num
from information_schema.`TABLES` ee
where ee.TABLE_SCHEMA not in('sys','information_schema','performance_schema','mysql')
group by ee.TABLE_SCHEMA,ee.TABLE_COLLATION
order by ee.TABLE_SCHEMA,ee.TABLE_COLLATION;

#5.库的字符集和排序字符集设置
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME,cc.SCHEMA_NAME
FROM INFORMATION_SCHEMA.SCHEMATA cc
WHERE SCHEMA_NAME not in('sys','information_schema','performance_schema','mysql');

#6.主键不是bigint或int类型的
select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLLATION_NAME,bb.COLUMN_KEY,bb.COLUMN_TYPE,bb.DATA_TYPE
from information_schema.`COLUMNS` bb
where TABLE_SCHEMA not in ('information_schema','mysql','performance_schema','sys','test')
    AND bb.DATA_TYPE not in('bigint','int')
    and (bb.TABLE_SCHEMA,bb.TABLE_NAME,bb.COLUMN_NAME) in
    (SELECT t.TABLE_SCHEMA,t.TABLE_NAME,k.COLUMN_NAME
    FROM information_schema.table_constraints t
         JOIN information_schema.key_column_usage k 
         USING (constraint_name,table_schema,table_name)
    WHERE t.constraint_type = 'PRIMARY KEY'
          AND t.table_schema not in('sys','information_schema','performance_schema','mysql')
    )

#7.索引命名不规范
SELECT DISTINCT a.database_name,a.table_name,index_name 
FROM mysql.`innodb_index_stats` a 
WHERE a.database_name not in('information_schema','mysql','performance_schema','sys','test')
      and a.index_name not like 'PRIMARY%' and a.index_name not like 'uk_%'
      and a.index_name not like 'idx_%';

#8.表名和库名有没有大小写
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME != LOWER(TABLE_NAME) AND TABLE_TYPE = 'BASE TABLE';
SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME != LOWER(SCHEMA_NAME);

#9.确保没有表分区在共享表空间内
SELECT DISTINCT NAME, SPACE, SPACE_TYPE 
FROM INFORMATION_SCHEMA.INNODB_TABLES
WHERE NAME LIKE '%#P#%' AND SPACE_TYPE NOT LIKE 'Single';

#10.分区表是否使用了不支持本地分区的存储引擎
SELECT TABLE_SCHEMA, TABLE_NAME,CREATE_OPTIONS
FROM   INFORMATION_SCHEMA.TABLES
WHERE ENGINE NOT IN ('innodb', 'ndbcluster')
      AND CREATE_OPTIONS LIKE '%partitioned%';


#11.是否有分区表
SELECT *
FROM INFORMATION_SCHEMA.PARTITIONS
where PARTITION_NAME is not NULL;

#12.检查CREATE_OPTIONS
SELECT TABLE_SCHEMA, TABLE_NAME,CREATE_OPTIONS
FROM   INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA not in('information_schema','mysql','performance_schema','sys','test')
      AND CREATE_OPTIONS is not null AND CREATE_OPTIONS<>'';