MySQL Modify character Set

First, modify the My.ini configuration file (MySQL configuration file)

Character_set_server = UTF8 #设置字符集

Restart MySQL database service

View the current database character set

Show VARIABLES like ' character% ';

Second, modify the database character set

ALTER DATABASE name character set UTF8;

PS: After modifying the database character set, the MySQL database needs to be restarted.

Third, modify the table character set

ALTER table name DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci

Generate all tables Modify Character Set statements:

SELECT table_name,concat (' ALTER TABLE ', table_name, ' DEFAULT CHARACTER SET ', A.default_character_set_name, ' COLLATE ', a . Default_collation_name, '; ') ExecuteSQL from INFORMATION_SCHEMA. Schemata A,information_schema. TABLES b
WHERE A.schema_name=b.table_schema
and a.default_collation_name!=b.table_collation
and b.table_schema= ' database name '

Iv. modifying the column character set

ALTER table table name change column name VARCHAR (+) CHARACTER SET UTF8 COLLATE utf8_general_ci null DEFAULT null;

Generate all columns modify character Set statements:

Select B.table_name,b.column_name,b.character_set_name,b.collation_name
, CONCAT (' ALTER TABLE ', B.table_name, ' MODIFY ', B.column_name, ', B.data_type, ' (', B.character_maximum_length, ') ', Case when B.column_default was NULL then ' ELSE CONCAT (' DEFAULT \ ', B.column_default, ' \ ') END, ' COMMENT \ ', b.column_com ment, ' \ '; ') ExecuteSQL
From INFORMATION_SCHEMA. TABLES A,information_schema. COLUMNS b where b.character_set_name is not NULL and A.table_schema=b.table_schema and A.table_name=b.table_name
and A.table_collation!=b.collation_name
and a.table_schema= ' database name '

MySQL Modify character Set