MySQL Chinese garbled processing _ Character Set conversion processing

--Chinese garbled repair

--View mysql service parameter settings
Mysql> Show variables like '%character% ';
+--------------------------+----------------------------------+
| variable_name | Value |
+--------------------------+----------------------------------+
| character_set_client | UTF8 |
| character_set_connection | UTF8 |
| Character_set_database | UTF8 |
| Character_set_filesystem | binary |
| Character_set_results | UTF8 |
| Character_set_server | UTF8 |
| Character_set_system | UTF8 |
| Character_sets_dir | /usr/local/mysql/share/charsets/|
+--------------------------+----------------------------------+
8 rows in Set (0.03 sec)

--View the default character set for the build library
Show create DATABASE test;

--View the default character set for the built table
Show CREATE TABLE yjdb;

--Fixed to UTF8 character set
ALTER DATABASE db_name DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci;
ALTER TABLE tb_name DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci;

--root user executes the query, executes the result, unifies the non-uniform library and the table and the field character set as UTF8
--Modify the library default character set in the library
Select ' ALTER DATABASE ' | | db| | ' DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci; ' from mysql.db where DB is not in (' Information_schema ', ' MySQL ', ' Test ', ' p Erformance_schema ');
Select Concat (' ALTER DATABASE ', db, ' DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; ') from mysql.db where DB not in ( ' Information_schema ', ' MySQL ', ' Test ', ' Performance_schema ');

--Modify the default character set for all tables in the database
Select ' ALTER TABLE ' | | table_schema| | '. ' | | table_name| | ' DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci; ' as Alter_sql from INFORMATION_SCHEMA. TABLES where Table_schema not in (' Information_schema ', ' MySQL ', ' Test ', ' Performance_schema ') and table_collation! = ' Utf8_general_ci ';
Select Concat (' Alter TABLE ', Table_schema, '. ', table_name, ' DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; ') as ALTER _sql from INFORMATION_SCHEMA. TABLES where Table_schema not in (' Information_schema ', ' MySQL ', ' Test ', ' Performance_schema ') and table_collation! = ' Utf8_general_ci ';

--Modify the column property of the table in the whole library to the Latin1 character set as the default, please confirm after execution.
--select * from INFORMATION_SCHEMA. COLUMNS where table_schema= ' TSS ';
Select ' ALTER TABLE ' | | table_schema| | '. ' | | table_name| | ' Change ' | | column_name| | ' ' | | column_name| | ' ' | | column_type| | ' Default ' | | ' | | column_default| | "' | | ' comment ' | | column_comment| | "' | | '; ' as Alter_sql from INFORMATION_SCHEMA. COLUMNS where Table_schema not in (' Information_schema ', ' MySQL ', ' Test ', ' Performance_schema ') and Character_set_name= ' Latin1 ' and is_nullable= ' yes ' and column_default is not NULL
UNION ALL
Select ' ALTER TABLE ' | | table_schema| | '. ' | | table_name| | ' Change ' | | column_name| | ' ' | | column_name| | ' ' | | column_type| | ' comment ' | | ' | | column_comment| | "' | | '; ' as Alter_sql from INFORMATION_SCHEMA. COLUMNS where Table_schema not in (' Information_schema ', ' MySQL ', ' Test ', ' Performance_schema ') and Character_set_name= ' Latin1 ' and is_nullable= ' yes ' and column_default is null
UNION ALL
Select ' ALTER TABLE ' | | table_schema| | '. ' | | table_name| | ' Change ' | | column_name| | ' ' | | column_name| | ' ' | | column_type| | ' NOT null default ' | | ' ' | | column_default| | "' | | ' comment ' | | column_comment| | "' | | '; ' as Alter_sql from INFORMATION_SCHEMA. COLUMNS where Table_schema not in (' Information_schema ', ' MySQL ', ' Test ', ' Performance_schema ') and Character_set_name= ' Latin1 ' and is_nullable= ' no ' and column_default is not NULL
UNION ALL
Select ' ALTER TABLE ' | | table_schema| | '. ' | | table_name| | ' Change ' | | column_name| | ' ' | | column_name| | ' ' | | column_type| | ' NOT NULL ' | | ' comment ' | | ' ' | | column_comment| | "' | | '; ' as Alter_sql from INFORMATION_SCHEMA. COLUMNS where Table_schema not in (' Information_schema ', ' MySQL ', ' Test ', ' Performance_schema ') and Character_set_name= ' Latin1 ' and is_nullable= ' no ' and column_default is null;

--to avoid the impact of errors in different environments, you can specify the character set when building libraries and tables

--Modify the encoding of the library
Select Concat (' ALTER DATABASE ', db, ' DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; ') from Mysql.db_view where db = ' Xjk_bbs ';

--Modify the default character set for all tables in the database
Select Concat (' ALTER TABLE ', Table_schema, '. ', table_name, ' DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; ') As Alter_sql from INFORMATION_SCHEMA. TABLES where table_schema= ' Xjk_bbs ';

--Modify the column property of the table in the whole library to the Latin1 character set as the default, please confirm after execution.
--select * from INFORMATION_SCHEMA. COLUMNS where table_schema= ' TSS ';

Select Concat (' ALTER TABLE ', Table_schema, '. ', table_name, ' MODIFY COLUMN ', ' ', column_name, ' ', Column_type, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ', ' Default ', ' ', ' column_default, ' ', ' comment ', ' ', column_comment, ' ', '; ') As Alter_sql from INFORMATION_SCHEMA. COLUMNS where table_schema= ' Xjk_bbs ' and table_name= ' aws_question ' and column_type don't like '%int% ' and is_nullable= ' yes ' And Column_default is not NULL
UNION ALL
Select Concat (' ALTER TABLE ', Table_schema, '. ', table_name, ' MODIFY COLUMN ', ' ', column_name, ' ', Column_type, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ', ' comment ', ' ', ' column_comment, ' ', '; ') As Alter_sql from INFORMATION_SCHEMA. COLUMNS where table_schema= ' Xjk_bbs ' and table_name= ' aws_question ' and column_type don't like '%int% ' and is_nullable= ' yes ' and Column_default is null
UNION ALL
Select Concat (' ALTER TABLE ', Table_schema, '. ', table_name, ' MODIFY COLUMN ', ' ', column_name, ' ', Column_type, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ', ' NOT null default ', ' ', ' column_default, ' ', ' comment ', ' ', Column_comment, ' ‘‘‘,‘;‘) As Alter_sql from INFORMATION_SCHEMA. COLUMNS where table_schema= ' Xjk_bbs ' and table_name= ' aws_question ' and column_type not like '%int% ' and is_nullable= ' no ' a nd column_default is not null
UNION ALL
Select Concat (' ALTER TABLE ', Table_schema, '. ', table_name, ' MODIFY COLUMN ', ' ', column_name, ' ', Column_type, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ', ' NOT null ', ' comment ', ' ', column_comment, ' ', '; ') As Alter_sql from INFORMATION_SCHEMA. COLUMNS where table_schema= ' Xjk_bbs ' and table_name= ' aws_question ' and column_type not like '%int% ' and is_nullable= ' no ' a nd column_default is null;

MySQL Chinese garbled processing _ Character Set conversion processing