Hive元数据库在MySQL中的字符编码修改为UTF8解决中文乱码问题
一、问题浮现
二、问题定位
在初始化Hive元数据时，已经指定了连接mysql的字符集为UTF8，但还是出现了乱码问题。
jdbc:mysql://127.0.0.1:3306/hive_metastore?createDatabaseIfNotExist=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false
我们去MySQL中查看表的字符编码和表中字段的字符编码，发现并非UFT8，故我们需要修改Hive MySQL元数据库中的表、字段的字符编码，修改为UFT8
三、修改Hive MySQL元数据库字符编码
1、在Window新建一个xlsx文件
2、登录mysql查询Hive元数据表的字符编码
select table_name,table_collation
from information_schema.tables
where table_schema = 'hive_metastore';
3、复制表名至xlsx文件中，示例如下：
4、xlsx文件中编写规则
=CONCAT("ALTER TABLE ",A240," charset=utf8mb4 collate=utf8mb4_0900_ai_ci;")
5、完成之后复制修改表的字符编码的命令，在MySQL中执行即可
ALTER TABLE AUX_TABLE charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE BUCKETING_COLS charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE CDS charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE COLUMNS_V2 charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE COMPACTION_QUEUE charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE COMPLETED_COMPACTIONS charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE COMPLETED_TXN_COMPONENTS charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE CTLGS charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE DATABASE_PARAMS charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE DBS charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE DB_PRIVS charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE DELEGATION_TOKENS charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE FUNCS charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE FUNC_RU charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE GLOBAL_PRIVS charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE HIVE_LOCKS charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE IDXS charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE INDEX_PARAMS charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE I_SCHEMA charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE KEY_CONSTRAINTS charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE MASTER_KEYS charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE MATERIALIZATION_REBUILD_LOCKS charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE METASTORE_DB_PROPERTIES charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE MIN_HISTORY_LEVEL charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE MV_CREATION_METADATA charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE MV_TABLES_USED charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE NEXT_COMPACTION_QUEUE_ID charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE NEXT_LOCK_ID charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE NEXT_TXN_ID charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE NEXT_WRITE_ID charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE NOTIFICATION_LOG charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE NOTIFICATION_SEQUENCE charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE NUCLEUS_TABLES charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE PARTITIONS charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE PARTITION_EVENTS charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE PARTITION_KEYS charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE PARTITION_KEY_VALS charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE PARTITION_PARAMS charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE PART_COL_PRIVS charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE PART_COL_STATS charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE PART_PRIVS charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE REPL_TXN_MAP charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE ROLES charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE ROLE_MAP charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE RUNTIME_STATS charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE SCHEMA_VERSION charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE SDS charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE SD_PARAMS charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE SEQUENCE_TABLE charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE SERDES charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE SERDE_PARAMS charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE SKEWED_COL_NAMES charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE SKEWED_COL_VALUE_LOC_MAP charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE SKEWED_STRING_LIST charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE SKEWED_STRING_LIST_VALUES charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE SKEWED_VALUES charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE SORT_COLS charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE TABLE_PARAMS charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE TAB_COL_STATS charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE TBLS charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE TBL_COL_PRIVS charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE TBL_PRIVS charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE TXNS charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE TXN_COMPONENTS charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE TXN_TO_WRITE_ID charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE TYPES charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE TYPE_FIELDS charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE VERSION charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE WM_MAPPING charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE WM_POOL charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE WM_POOL_TO_TRIGGER charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE WM_RESOURCEPLAN charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE WM_TRIGGER charset=utf8mb4 collate=utf8mb4_0900_ai_ci; ALTER TABLE WRITE_SET charset=utf8mb4 collate=utf8mb4_0900_ai_ci;
6、表中所有字段编码查询
select table_name,COLUMN_NAME,COLUMN_TYPE
from information_schema.columns
where table_schema = 'hive_metastore' and data_type like '%char%';
7、将表名，字段名，字段类型复制到xlsx文件中，并编写规则
=CONCAT("ALTER TABLE ",A12," MODIFY COLUMN ",B12," ",C12," CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;")
8、完成之后复制修改表中字段的字符编码的命令，在MySQL中执行即可（由于部分字段修改时出现Specified key was too long; max key length is 3072 bytes，故一下语句中未放入报错的SQL）
ALTER TABLE AUX_TABLE MODIFY COLUMN MT_COMMENT varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE AUX_TABLE MODIFY COLUMN MT_KEY1 varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE BUCKETING_COLS MODIFY COLUMN BUCKET_COL_NAME varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE COLUMNS_V2 MODIFY COLUMN COMMENT varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE COMPACTION_QUEUE MODIFY COLUMN CQ_DATABASE varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE COMPACTION_QUEUE MODIFY COLUMN CQ_HADOOP_JOB_ID varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE COMPACTION_QUEUE MODIFY COLUMN CQ_PARTITION varchar(767) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE COMPACTION_QUEUE MODIFY COLUMN CQ_RUN_AS varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE COMPACTION_QUEUE MODIFY COLUMN CQ_STATE char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE COMPACTION_QUEUE MODIFY COLUMN CQ_TABLE varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE COMPACTION_QUEUE MODIFY COLUMN CQ_TBLPROPERTIES varchar(2048) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE COMPACTION_QUEUE MODIFY COLUMN CQ_TYPE char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE COMPACTION_QUEUE MODIFY COLUMN CQ_WORKER_ID varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE COMPLETED_COMPACTIONS MODIFY COLUMN CC_DATABASE varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE COMPLETED_COMPACTIONS MODIFY COLUMN CC_HADOOP_JOB_ID varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE COMPLETED_COMPACTIONS MODIFY COLUMN CC_PARTITION varchar(767) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE COMPLETED_COMPACTIONS MODIFY COLUMN CC_RUN_AS varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE COMPLETED_COMPACTIONS MODIFY COLUMN CC_STATE char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE COMPLETED_COMPACTIONS MODIFY COLUMN CC_TABLE varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE COMPLETED_COMPACTIONS MODIFY COLUMN CC_TBLPROPERTIES varchar(2048) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE COMPLETED_COMPACTIONS MODIFY COLUMN CC_TYPE char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE COMPLETED_COMPACTIONS MODIFY COLUMN CC_WORKER_ID varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE COMPLETED_TXN_COMPONENTS MODIFY COLUMN CTC_DATABASE varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE COMPLETED_TXN_COMPONENTS MODIFY COLUMN CTC_PARTITION varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE COMPLETED_TXN_COMPONENTS MODIFY COLUMN CTC_TABLE varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE COMPLETED_TXN_COMPONENTS MODIFY COLUMN CTC_UPDATE_DELETE char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE CTLGS MODIFY COLUMN LOCATION_URI varchar(4000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE DATABASE_PARAMS MODIFY COLUMN PARAM_KEY varchar(180) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE DATABASE_PARAMS MODIFY COLUMN PARAM_VALUE varchar(4000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE DBS MODIFY COLUMN DB_LOCATION_URI varchar(4000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE DBS MODIFY COLUMN NAME varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE DBS MODIFY COLUMN OWNER_NAME varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE DBS MODIFY COLUMN OWNER_TYPE varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE DB_PRIVS MODIFY COLUMN AUTHORIZER varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE DB_PRIVS MODIFY COLUMN DB_PRIV varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE DB_PRIVS MODIFY COLUMN GRANTOR varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE DB_PRIVS MODIFY COLUMN GRANTOR_TYPE varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE DB_PRIVS MODIFY COLUMN PRINCIPAL_NAME varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE DELEGATION_TOKENS MODIFY COLUMN TOKEN varchar(767) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE DELEGATION_TOKENS MODIFY COLUMN TOKEN_IDENT varchar(767) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE FUNCS MODIFY COLUMN CLASS_NAME varchar(4000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE FUNCS MODIFY COLUMN FUNC_NAME varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE FUNCS MODIFY COLUMN OWNER_NAME varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE FUNCS MODIFY COLUMN OWNER_TYPE varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE FUNC_RU MODIFY COLUMN RESOURCE_URI varchar(4000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE GLOBAL_PRIVS MODIFY COLUMN AUTHORIZER varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE GLOBAL_PRIVS MODIFY COLUMN GRANTOR varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE GLOBAL_PRIVS MODIFY COLUMN GRANTOR_TYPE varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE GLOBAL_PRIVS MODIFY COLUMN PRINCIPAL_NAME varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE GLOBAL_PRIVS MODIFY COLUMN PRINCIPAL_TYPE varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE GLOBAL_PRIVS MODIFY COLUMN USER_PRIV varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE HIVE_LOCKS MODIFY COLUMN HL_AGENT_INFO varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE HIVE_LOCKS MODIFY COLUMN HL_DB varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE HIVE_LOCKS MODIFY COLUMN HL_HOST varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE HIVE_LOCKS MODIFY COLUMN HL_LOCK_STATE char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE HIVE_LOCKS MODIFY COLUMN HL_LOCK_TYPE char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE HIVE_LOCKS MODIFY COLUMN HL_PARTITION varchar(767) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE HIVE_LOCKS MODIFY COLUMN HL_TABLE varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE HIVE_LOCKS MODIFY COLUMN HL_USER varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE IDXS MODIFY COLUMN INDEX_HANDLER_CLASS varchar(4000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE IDXS MODIFY COLUMN INDEX_NAME varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE INDEX_PARAMS MODIFY COLUMN PARAM_KEY varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE INDEX_PARAMS MODIFY COLUMN PARAM_VALUE varchar(4000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE I_SCHEMA MODIFY COLUMN DESCRIPTION varchar(4000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE I_SCHEMA MODIFY COLUMN NAME varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE I_SCHEMA MODIFY COLUMN SCHEMA_GROUP varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE KEY_CONSTRAINTS MODIFY COLUMN CONSTRAINT_NAME varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE KEY_CONSTRAINTS MODIFY COLUMN DEFAULT_VALUE varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE MASTER_KEYS MODIFY COLUMN MASTER_KEY varchar(767) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE MATERIALIZATION_REBUILD_LOCKS MODIFY COLUMN MRL_DB_NAME varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE MATERIALIZATION_REBUILD_LOCKS MODIFY COLUMN MRL_TBL_NAME varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE METASTORE_DB_PROPERTIES MODIFY COLUMN DESCRIPTION varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE METASTORE_DB_PROPERTIES MODIFY COLUMN PROPERTY_KEY varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE METASTORE_DB_PROPERTIES MODIFY COLUMN PROPERTY_VALUE varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE MV_CREATION_METADATA MODIFY COLUMN CAT_NAME varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE MV_CREATION_METADATA MODIFY COLUMN DB_NAME varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE MV_CREATION_METADATA MODIFY COLUMN TBL_NAME varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE NEXT_WRITE_ID MODIFY COLUMN NWI_DATABASE varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE NEXT_WRITE_ID MODIFY COLUMN NWI_TABLE varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE NOTIFICATION_LOG MODIFY COLUMN CAT_NAME varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE NOTIFICATION_LOG MODIFY COLUMN DB_NAME varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE NOTIFICATION_LOG MODIFY COLUMN EVENT_TYPE varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE NOTIFICATION_LOG MODIFY COLUMN MESSAGE_FORMAT varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE NOTIFICATION_LOG MODIFY COLUMN TBL_NAME varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE NUCLEUS_TABLES MODIFY COLUMN CLASS_NAME varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE NUCLEUS_TABLES MODIFY COLUMN INTERFACE_NAME varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE NUCLEUS_TABLES MODIFY COLUMN OWNER varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE NUCLEUS_TABLES MODIFY COLUMN TABLE_NAME varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE NUCLEUS_TABLES MODIFY COLUMN TYPE varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE NUCLEUS_TABLES MODIFY COLUMN VERSION varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE PARTITION_EVENTS MODIFY COLUMN CAT_NAME varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE PARTITION_EVENTS MODIFY COLUMN DB_NAME varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE PARTITION_EVENTS MODIFY COLUMN PARTITION_NAME varchar(767) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE PARTITION_EVENTS MODIFY COLUMN TBL_NAME varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE PARTITION_KEYS MODIFY COLUMN PKEY_COMMENT varchar(4000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE PARTITION_KEYS MODIFY COLUMN PKEY_NAME varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE PARTITION_KEYS MODIFY COLUMN PKEY_TYPE varchar(767) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE PARTITION_KEY_VALS MODIFY COLUMN PART_KEY_VAL varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE PARTITION_PARAMS MODIFY COLUMN PARAM_KEY varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE PARTITION_PARAMS MODIFY COLUMN PARAM_VALUE varchar(4000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE PART_COL_PRIVS MODIFY COLUMN AUTHORIZER varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE PART_COL_PRIVS MODIFY COLUMN GRANTOR varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE PART_COL_PRIVS MODIFY COLUMN GRANTOR_TYPE varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE PART_COL_STATS MODIFY COLUMN BIG_DECIMAL_HIGH_VALUE varchar(4000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE PART_COL_STATS MODIFY COLUMN BIG_DECIMAL_LOW_VALUE varchar(4000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE PART_COL_STATS MODIFY COLUMN CAT_NAME varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE PART_COL_STATS MODIFY COLUMN COLUMN_TYPE varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE PART_PRIVS MODIFY COLUMN AUTHORIZER varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE PART_PRIVS MODIFY COLUMN GRANTOR varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE PART_PRIVS MODIFY COLUMN GRANTOR_TYPE varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE PART_PRIVS MODIFY COLUMN PART_PRIV varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE PART_PRIVS MODIFY COLUMN PRINCIPAL_NAME varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE REPL_TXN_MAP MODIFY COLUMN RTM_REPL_POLICY varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE ROLES MODIFY COLUMN OWNER_NAME varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE ROLES MODIFY COLUMN ROLE_NAME varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE ROLE_MAP MODIFY COLUMN GRANTOR varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE ROLE_MAP MODIFY COLUMN GRANTOR_TYPE varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE ROLE_MAP MODIFY COLUMN PRINCIPAL_NAME varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE ROLE_MAP MODIFY COLUMN PRINCIPAL_TYPE varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE SCHEMA_VERSION MODIFY COLUMN DESCRIPTION varchar(4000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE SCHEMA_VERSION MODIFY COLUMN FINGERPRINT varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE SCHEMA_VERSION MODIFY COLUMN SCHEMA_VERSION_NAME varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE SDS MODIFY COLUMN INPUT_FORMAT varchar(4000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE SDS MODIFY COLUMN LOCATION varchar(4000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE SDS MODIFY COLUMN OUTPUT_FORMAT varchar(4000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE SD_PARAMS MODIFY COLUMN PARAM_KEY varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE SEQUENCE_TABLE MODIFY COLUMN SEQUENCE_NAME varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE SERDES MODIFY COLUMN DESCRIPTION varchar(4000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE SERDES MODIFY COLUMN DESERIALIZER_CLASS varchar(4000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE SERDES MODIFY COLUMN NAME varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE SERDES MODIFY COLUMN SERIALIZER_CLASS varchar(4000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE SERDES MODIFY COLUMN SLIB varchar(4000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE SERDE_PARAMS MODIFY COLUMN PARAM_KEY varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE SKEWED_COL_NAMES MODIFY COLUMN SKEWED_COL_NAME varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE SKEWED_COL_VALUE_LOC_MAP MODIFY COLUMN LOCATION varchar(4000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE SKEWED_STRING_LIST_VALUES MODIFY COLUMN STRING_LIST_VALUE varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE SORT_COLS MODIFY COLUMN COLUMN_NAME varchar(767) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE TABLE_PARAMS MODIFY COLUMN PARAM_KEY varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE TABLE_PARAMS MODIFY COLUMN PARAM_VALUE varchar(4000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE TAB_COL_STATS MODIFY COLUMN BIG_DECIMAL_HIGH_VALUE varchar(4000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE TAB_COL_STATS MODIFY COLUMN BIG_DECIMAL_LOW_VALUE varchar(4000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE TAB_COL_STATS MODIFY COLUMN CAT_NAME varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE TAB_COL_STATS MODIFY COLUMN COLUMN_TYPE varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE TAB_COL_STATS MODIFY COLUMN DB_NAME varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE TBLS MODIFY COLUMN OWNER varchar(767) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE TBLS MODIFY COLUMN OWNER_TYPE varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE TBLS MODIFY COLUMN TBL_NAME varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE TBLS MODIFY COLUMN TBL_TYPE varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE TBL_COL_PRIVS MODIFY COLUMN AUTHORIZER varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE TBL_COL_PRIVS MODIFY COLUMN GRANTOR varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE TBL_COL_PRIVS MODIFY COLUMN GRANTOR_TYPE varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE TBL_PRIVS MODIFY COLUMN AUTHORIZER varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE TBL_PRIVS MODIFY COLUMN GRANTOR varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE TBL_PRIVS MODIFY COLUMN GRANTOR_TYPE varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE TBL_PRIVS MODIFY COLUMN PRINCIPAL_NAME varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE TBL_PRIVS MODIFY COLUMN PRINCIPAL_TYPE varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE TXNS MODIFY COLUMN TXN_AGENT_INFO varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE TXNS MODIFY COLUMN TXN_HOST varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE TXNS MODIFY COLUMN TXN_META_INFO varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE TXNS MODIFY COLUMN TXN_STATE char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE TXNS MODIFY COLUMN TXN_USER varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE TXN_COMPONENTS MODIFY COLUMN TC_DATABASE varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE TXN_COMPONENTS MODIFY COLUMN TC_OPERATION_TYPE char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE TXN_COMPONENTS MODIFY COLUMN TC_PARTITION varchar(767) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE TXN_COMPONENTS MODIFY COLUMN TC_TABLE varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE TXN_TO_WRITE_ID MODIFY COLUMN T2W_DATABASE varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE TXN_TO_WRITE_ID MODIFY COLUMN T2W_TABLE varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE TYPES MODIFY COLUMN TYPE1 varchar(767) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE TYPES MODIFY COLUMN TYPE2 varchar(767) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE TYPES MODIFY COLUMN TYPE_NAME varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE TYPE_FIELDS MODIFY COLUMN COMMENT varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE TYPE_FIELDS MODIFY COLUMN FIELD_NAME varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE TYPE_FIELDS MODIFY COLUMN FIELD_TYPE varchar(767) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE VERSION MODIFY COLUMN SCHEMA_VERSION varchar(127) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE VERSION MODIFY COLUMN VERSION_COMMENT varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE WM_MAPPING MODIFY COLUMN ENTITY_NAME varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE WM_MAPPING MODIFY COLUMN ENTITY_TYPE varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE WM_POOL MODIFY COLUMN SCHEDULING_POLICY varchar(767) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE WM_RESOURCEPLAN MODIFY COLUMN NAME varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE WM_RESOURCEPLAN MODIFY COLUMN STATUS varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE WM_TRIGGER MODIFY COLUMN ACTION_EXPRESSION varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE WM_TRIGGER MODIFY COLUMN NAME varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE WM_TRIGGER MODIFY COLUMN TRIGGER_EXPRESSION varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE WRITE_SET MODIFY COLUMN WS_DATABASE varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE WRITE_SET MODIFY COLUMN WS_OPERATION_TYPE char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE WRITE_SET MODIFY COLUMN WS_PARTITION varchar(767) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE WRITE_SET MODIFY COLUMN WS_TABLE varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
四、测试Hive中文乱码问题
