/etc/my.cnf
character_set_server = utf8mb4
2.更改库
def | information_schema | utf8 | utf8_general_ci | NULL |
| def | FlowCompute | latin1 | latin1_swedish_ci | NULL |
| def | SSO | utf8 | utf8_general_ci | NULL |
| def | jlp | utf8 | utf8_general_ci | NULL |
| def | mxj | utf8 | utf8_general_ci | NULL |
| def | mycat_node | utf8 | utf8_general_ci | NULL |
| def | mysql | utf8mb4 | utf8mb4_general_ci | NULL |
| def | optlog | utf8mb4 | utf8mb4_general_ci | NULL |
| def | performance_schema | utf8 | utf8_general_ci | NULL |
| def | sakila | utf8 | utf8_general_ci | NULL |
| def | test | utf8mb4 | utf8mb4_general_ci | NULL |
| def | wenb | utf8 | utf8_general_ci | NULL |
+--------------+--------------------+----------------------------+------------------------+----------+
information_schema
performance_schema
test
mysql
alter DATABASE FlowCompute default CHARACTER set utf8mb4 collate utf8mb4_general_ci ;
alter DATABASE SSO default CHARACTER set utf8mb4 collate utf8mb4_general_ci ;
alter DATABASE jlp default CHARACTER set utf8mb4 collate utf8mb4_general_ci ;
alter DATABASE mxj default CHARACTER set utf8mb4 collate utf8mb4_general_ci ;
alter DATABASE mycat_node default CHARACTER set utf8mb4 collate utf8mb4_general_ci ;
alter DATABASE optlog default CHARACTER set utf8mb4 collate utf8mb4_general_ci ;
alter DATABASE sakila default CHARACTER set utf8mb4 collate utf8mb4_general_ci ;
alter DATABASE wenb default CHARACTER set utf8mb4 collate utf8mb4_general_ci ;
3.更改表的字符集：
ALTER TABLE tbl_name
[[DEFAULT] CHARACTER SET charset_name]
[COLLATE collation_name]
select TABLE_SCHEMA,table_name ,ENGINE,TABLE_COLLATION from information_schema.TABLES where TABLE_SCHEMA in ( 'FlowCompute','SSO','jlp','mxj','mycat_node','optlog','sakila','wenb') order by 1
+--------------+----------------------------+--------+--------------------+
| TABLE_SCHEMA | table_name | ENGINE | TABLE_COLLATION |
+--------------+----------------------------+--------+--------------------+
| FlowCompute | WorkFlow | InnoDB | latin1_swedish_ci |
| FlowCompute | FlowSummary | InnoDB | latin1_swedish_ci |
| FlowCompute | FlowCommond | InnoDB | latin1_swedish_ci |
| FlowCompute | ExecFlowConfig | InnoDB | latin1_swedish_ci |
| FlowCompute | CallServices | InnoDB | latin1_swedish_ci |
| FlowCompute | CallModels | InnoDB | latin1_swedish_ci |
| jlp | Fd_Vehicle_Temp | InnoDB | utf8mb4_general_ci |
| jlp | Fd_Con_License_Temp | InnoDB | utf8mb4_general_ci |
| jlp | aaa | InnoDB | utf8_general_ci |
| jlp | Sys_CallModels | InnoDB | latin1_swedish_ci |
| jlp | Fd_Icons_Temp | InnoDB | utf8mb4_general_ci |
| jlp | Fd_Ssa_License_Temp | InnoDB | utf8mb4_general_ci |
| jlp | Fd_Vehicle_Brandmodel_Temp | InnoDB | utf8mb4_general_ci |
| jlp | Fd_Carrier_Vehicle_Temp | InnoDB | utf8mb4_general_ci |
| jlp | Sys_WorkFlow | InnoDB | latin1_swedish_ci |
| jlp | testgoods | InnoDB | utf8_general_ci |
| jlp | Sys_AuthorizationCfg | InnoDB | utf8_general_ci |
| jlp | Fd_Goods_Temp | InnoDB | utf8mb4_general_ci |
| jlp | Fd_Ssa_Cons_Temp | InnoDB | utf8mb4_general_ci |
| jlp | Rec_TransportP_Temp | InnoDB | utf8_general_ci |
| jlp | Sys_TokenCfg | InnoDB | utf8_general_ci |
| jlp | testa | InnoDB | utf8_general_ci |
| jlp | Fd_Transport_Goods_Temp | InnoDB | utf8mb4_general_ci |
| jlp | Fd_Carrier_Temp | InnoDB | utf8mb4_general_ci |
| jlp | Fd_Goods_License_Temp | InnoDB | utf8mb4_general_ci |
| jlp | Fd_Ssa_Bank_Temp | InnoDB | utf8mb4_general_ci |
| jlp | Rec_Takegds_Barcode | InnoDB | utf8_general_ci |
| jlp | test | InnoDB | utf8_general_ci |
| jlp | Fd_SupplySalesagency_Temp | InnoDB | utf8mb4_general_ci |
| jlp | Fd_Aging | InnoDB | utf8_general_ci |
| jlp | Sys_ServiceCfg | InnoDB | utf8_general_ci |
| jlp | Fd_Goods_Image_Temp | InnoDB | utf8mb4_general_ci |
| jlp | Fd_Ssa_Addr_Temp | InnoDB | utf8mb4_general_ci |
| jlp | Rec_Stock_Ledger | InnoDB | utf8_general_ci |
| jlp | stock_records | InnoDB | utf8_general_ci |
| jlp | Fd_Staff_Temp_copy | InnoDB | utf8mb4_general_ci |
| jlp | FdStaffCarrSave | InnoDB | utf8mb4_general_ci |
| jlp | Sys_ExecFlowConfig | InnoDB | latin1_swedish_ci |
| jlp | Fd_Goods_Cons_Temp_copy1 | InnoDB | utf8mb4_general_ci |
| jlp | Fd_Staff_Temp | InnoDB | utf8mb4_general_ci |
| jlp | Rec_State | InnoDB | utf8_general_ci |
| jlp | Fd_Route_Temp | InnoDB | utf8mb4_general_ci |
| jlp | hibernate_sequences | InnoDB | utf8_general_ci |
| jlp | Bill_TransportP_Hdr | InnoDB | utf8_general_ci |
| jlp | Sys_Districonfig | InnoDB | utf8_general_ci |
| jlp | Fd_Goods_Cons_Temp_copy | InnoDB | utf8mb4_general_ci |
| jlp | Fd_Staff_Ssa_Temp | InnoDB | utf8mb4_general_ci |
| jlp | Fd_Operator_Temp | InnoDB | utf8mb4_general_ci |
| jlp | Rec_FlowSummary | InnoDB | latin1_swedish_ci |
| jlp | goods_main | InnoDB | utf8_general_ci |
| jlp | Bill_TransportP_Dtl | InnoDB | utf8_general_ci |
| jlp | Sys_DeveloperCfg | InnoDB | utf8_general_ci |
| jlp | Fd_Goods_Cons_Temp | InnoDB | utf8mb4_general_ci |
| jlp | Fd_Staff_Ldc_Temp | InnoDB | utf8mb4_general_ci |
| jlp | Fd_LotNo_Stock | InnoDB | utf8mb4_general_ci |
| jlp | Rec_FlowCommand | InnoDB | latin1_swedish_ci |
| jlp | Bill_TransportA_Hdr | InnoDB | utf8_general_ci |
| jlp | Sys_DataCfg | InnoDB | utf8_general_ci |
| jlp | Fd_Logistics_Center_Temp | InnoDB | utf8mb4_general_ci |
| jlp | Fd_Staff_Corp_Temp | InnoDB | utf8mb4_general_ci |
| jlp | Fd_Field_Hdr | InnoDB | utf8mb4_general_ci |
| jlp | Rec_Error_Log | InnoDB | utf8_general_ci |
| jlp | Bill_TransportA_Dtl | InnoDB | utf8_general_ci |
| jlp | Sys_ConnCfg | InnoDB | utf8_general_ci |
| jlp | Fd_Log_Vehicle_Temp | InnoDB | utf8mb4_general_ci |
| jlp | Fd_Field_Dtl | InnoDB | utf8mb4_general_ci |
| jlp | Fd_Staff_Cons_Temp | InnoDB | utf8_general_ci |
| jlp | LMIS6S_RK_SC_BILL | InnoDB | utf8_general_ci |
| jlp | Sys_CallServices_Bak | InnoDB | latin1_swedish_ci |
| jlp | Fd_Log_Cons_Temp | InnoDB | utf8mb4_general_ci |
| jlp | Fd_Field_Config | InnoDB | utf8mb4_general_ci |
| jlp | Fd_Staff_Carr_Temp | InnoDB | utf8_general_ci |
| jlp | Inf_Fd_Goods | InnoDB | utf8_general_ci |
| jlp | Fd_Consignor_Temp | InnoDB | utf8mb4_general_ci |
| jlp | goods_id | InnoDB | utf8_general_ci |
| jlp | Fd_Ldc_App | InnoDB | utf8_general_ci |
| jlp | Sys_CallServices | InnoDB | latin1_swedish_ci |
| jlp | Fd_Ssa_Route_Temp | InnoDB | utf8mb4_general_ci |
| mxj | FD_GOODS | InnoDB | utf8mb4_general_ci |
| mycat_node | hotnews | InnoDB | utf8_general_ci |
| sakila | actor | InnoDB | utf8_general_ci |
| SSO | RolesTypeMenuConfig | InnoDB | utf8_general_ci |
| SSO | Applications | InnoDB | utf8_general_ci |
| SSO | UserOwenerConfig | InnoDB | utf8_general_ci |
| SSO | RolesType | InnoDB | utf8_general_ci |
| SSO | AppRolesConfig | InnoDB | utf8_general_ci |
| SSO | UserLogisticsConfig | InnoDB | utf8_general_ci |
| SSO | Roles | InnoDB | utf8_general_ci |
| SSO | AppMethods | InnoDB | utf8_general_ci |
| SSO | RoleMethodsConfig | InnoDB | utf8_general_ci |
| SSO | AppFunctions | InnoDB | utf8_general_ci |
| SSO | RoleFunctionsConfig | InnoDB | utf8_general_ci |
| SSO | Owners | InnoDB | utf8_general_ci |
| SSO | UserCustomerConfig | InnoDB | utf8_general_ci |
| SSO | Methods | InnoDB | utf8_general_ci |
| SSO | TransfromFlag | MyISAM | utf8_general_ci |
| SSO | DataDistributionConfig | InnoDB | utf8_general_ci |
| SSO | Ldcs | InnoDB | utf8_general_ci |
| SSO | SSO_Login_System | MyISAM | utf8_general_ci |
| SSO | Customers | InnoDB | utf8_general_ci |
| SSO | DeleteFlag | MyISAM | utf8_general_ci |
| SSO | SSO_Login_Log | MyISAM | utf8_general_ci |
| SSO | Corporations | InnoDB | utf8_general_ci |
| SSO | Users | InnoDB | utf8_general_ci |
| SSO | SSO_Login_List | MyISAM | utf8_general_ci |
| SSO | AppsConfig | InnoDB | utf8_general_ci |
| SSO | UserRolesConfig | InnoDB | utf8_general_ci |
| wenb | t_sample | InnoDB | utf8_general_ci |
+--------------+----------------------------+--------+--------------------+
alter table FlowCompute.WorkFlow character set utf8mb4 collate utf8mb4_general_ci ;
alter table FlowCompute.FlowSummary character set utf8mb4 collate utf8mb4_general_ci ;
alter table FlowCompute.FlowCommond character set utf8mb4 collate utf8mb4_general_ci ;
alter table FlowCompute.ExecFlowConfig character set utf8mb4 collate utf8mb4_general_ci ;
alter table FlowCompute.CallServices character set utf8mb4 collate utf8mb4_general_ci ;
alter table FlowCompute.CallModels character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Fd_Vehicle_Temp character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Fd_Con_License_Temp character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.aaa character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Sys_CallModels character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Fd_Icons_Temp character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Fd_Ssa_License_Temp character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Fd_Vehicle_Brandmodel_Temp character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Fd_Carrier_Vehicle_Temp character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Sys_WorkFlow character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.testgoods character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Sys_AuthorizationCfg character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Fd_Goods_Temp character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Fd_Ssa_Cons_Temp character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Rec_TransportP_Temp character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Sys_TokenCfg character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.testa character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Fd_Transport_Goods_Temp character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Fd_Carrier_Temp character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Fd_Goods_License_Temp character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Fd_Ssa_Bank_Temp character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Rec_Takegds_Barcode character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.test character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Fd_SupplySalesagency_Temp character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Fd_Aging character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Sys_ServiceCfg character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Fd_Goods_Image_Temp character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Fd_Ssa_Addr_Temp character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Rec_Stock_Ledger character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.stock_records character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Fd_Staff_Temp_copy character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.FdStaffCarrSave character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Sys_ExecFlowConfig character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Fd_Goods_Cons_Temp_copy1 character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Fd_Staff_Temp character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Rec_State character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Fd_Route_Temp character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.hibernate_sequences character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Bill_TransportP_Hdr character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Sys_Districonfig character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Fd_Goods_Cons_Temp_copy character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Fd_Staff_Ssa_Temp character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Fd_Operator_Temp character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Rec_FlowSummary character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.goods_main character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Bill_TransportP_Dtl character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Sys_DeveloperCfg character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Fd_Goods_Cons_Temp character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Fd_Staff_Ldc_Temp character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Fd_LotNo_Stock character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Rec_FlowCommand character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Bill_TransportA_Hdr character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Sys_DataCfg character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Fd_Logistics_Center_Temp character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Fd_Staff_Corp_Temp character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Fd_Field_Hdr character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Rec_Error_Log character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Bill_TransportA_Dtl character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Sys_ConnCfg character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Fd_Log_Vehicle_Temp character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Fd_Field_Dtl character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Fd_Staff_Cons_Temp character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.LMIS6S_RK_SC_BILL character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Sys_CallServices_Bak character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Fd_Log_Cons_Temp character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Fd_Field_Config character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Fd_Staff_Carr_Temp character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Inf_Fd_Goods character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Fd_Consignor_Temp character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.goods_id character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Fd_Ldc_App character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Sys_CallServices character set utf8mb4 collate utf8mb4_general_ci ;
alter table jlp.Fd_Ssa_Route_Temp character set utf8mb4 collate utf8mb4_general_ci ;
alter table mxj.FD_GOODS character set utf8mb4 collate utf8mb4_general_ci ;
alter table mycat_node.hotnews character set utf8mb4 collate utf8mb4_general_ci ;
alter table sakila.actor character set utf8mb4 collate utf8mb4_general_ci ;
alter table SSO.RolesTypeMenuConfig character set utf8mb4 collate utf8mb4_general_ci ;
alter table SSO.Applications character set utf8mb4 collate utf8mb4_general_ci ;
alter table SSO.UserOwenerConfig character set utf8mb4 collate utf8mb4_general_ci ;
alter table SSO.RolesType character set utf8mb4 collate utf8mb4_general_ci ;
alter table SSO.AppRolesConfig character set utf8mb4 collate utf8mb4_general_ci ;
alter table SSO.UserLogisticsConfig character set utf8mb4 collate utf8mb4_general_ci ;
alter table SSO.Roles character set utf8mb4 collate utf8mb4_general_ci ;
alter table SSO.AppMethods character set utf8mb4 collate utf8mb4_general_ci ;
alter table SSO.RoleMethodsConfig character set utf8mb4 collate utf8mb4_general_ci ;
alter table SSO.AppFunctions character set utf8mb4 collate utf8mb4_general_ci ;
alter table SSO.RoleFunctionsConfig character set utf8mb4 collate utf8mb4_general_ci ;
alter table SSO.Owners character set utf8mb4 collate utf8mb4_general_ci ;
alter table SSO.UserCustomerConfig character set utf8mb4 collate utf8mb4_general_ci ;
alter table SSO.Methods character set utf8mb4 collate utf8mb4_general_ci ;
alter table SSO.TransfromFlag character set utf8mb4 collate utf8mb4_general_ci ;
alter table SSO.DataDistributionConfig character set utf8mb4 collate utf8mb4_general_ci ;
alter table SSO.Ldcs character set utf8mb4 collate utf8mb4_general_ci ;
alter table SSO.SSO_Login_System character set utf8mb4 collate utf8mb4_general_ci ;
alter table SSO.Customers character set utf8mb4 collate utf8mb4_general_ci ;
alter table SSO.DeleteFlag character set utf8mb4 collate utf8mb4_general_ci ;
alter table SSO.SSO_Login_Log character set utf8mb4 collate utf8mb4_general_ci ;
alter table SSO.Corporations character set utf8mb4 collate utf8mb4_general_ci ;
alter table SSO.Users character set utf8mb4 collate utf8mb4_general_ci ;
alter table SSO.SSO_Login_List character set utf8mb4 collate utf8mb4_general_ci ;
alter table SSO.AppsConfig character set utf8mb4 collate utf8mb4_general_ci ;
alter table SSO.UserRolesConfig character set utf8mb4 collate utf8mb4_general_ci ;
alter table wenb.t_sample character set utf8mb4 collate utf8mb4_general_ci ;
4. 更改字段
select table_schema,table_name ,column_name ,COLUMN_DEFAULT,CHARACTER_SET_NAME,COLLATION_NAME ,COLUMN_TYPE from COLUMNS where table_schema in ( 'jlp' ) and CHARACTER_SET_NAME not in ( 'utf8mb4','NULL' ) ;
1207
select table_schema,table_name ,column_name ,COLUMN_TYPE ,CONCAT('comment ','''',COLUMN_COMMENT,'''') from COLUMNS where table_schema in ( 'jlp' ) and CHARACTER_SET_NAME not in ( 'utf8mb4'','NULL' ) and table_name = 'Bill_TransportA_Dtl'
select table_schema,table_name ,column_name ,COLUMN_TYPE ,CONCAT('comment ','''',COLUMN_COMMENT,'''') from information_schema.COLUMNS where table_schema in ( 'jlp' ) and CHARACTER_SET_NAME not in ( 'utf8mb4','NULL' ) and table_name = 'Bill_TransportA_Dtl' ;
alter table jlp.Bill_TransportA_Dtl modify Is_Receipt varchar(2) NOT NULL DEFAULT 'N' COMMENT '是否回单' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
alter table jlp.Bill_TransportA_Dtl modify Is_Receipt varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'N' COMMENT '是否回单'
select table_schema,table_name ,column_name from COLUMNS where table_schema in ( 'jlp' ) and CHARACTER_SET_NAME not in ( 'utf8mb4','NULL' ) ;
批量更改字段的sql
select CONCAT('alter table ',
table_schema,
'.',
table_name,
' modify ',
column_name,
' ',
column_type,
' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ' ) l,
case
when IS_NULLABLE = 'NO' then
' NOT NULL '
when IS_NULLABLE ='YES' and COLUMN_DEFAULT is not null then null
else
' DEFAULT NULL '
end,
case when COLUMN_DEFAULT is not null then CONCAT('DEFAULT ''',COLUMN_DEFAULT,''' ') end,
case when COLUMN_COMMENT != '' then CONCAT('comment ', '''', COLUMN_COMMENT, '''')
when COLUMN_COMMENT is not null and COLUMN_COMMENT != '' then CONCAT('comment ', '''', COLUMN_COMMENT, '''') end ,
';'
from information_schema.COLUMNS
where table_schema in ('FlowCompute')
and CHARACTER_SET_NAME not in ('utf8mb4', 'NULL') ;
-- and table_name = 'Bill_TransportA_Dtl';