Shulou(Shulou.com)06/01 Report--

-- Chinese garbled code 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 of the library

Show create database test

-- View the default character set for creating a 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 users execute queries, execute the results, and unify the character sets of inconsistent libraries and tables and fields into utf8

-- modify the default character set for building a database in the whole library

Select 'ALTER DATABASE' | | db | | 'DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;' from mysql.db where db not in (' information_schema','mysql','test','performance_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 of the building table in the whole library

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 character set whose column attribute of the table in the whole library is latin1 to default. Please confirm it and execute it.

-- 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 |

In order to avoid the impact of errors in different environments, you can specify a special character set when creating libraries and tables.

-- modify the coding 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 of the building table in the whole library

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 character set whose column attribute of the table in the whole library is latin1 to default. Please confirm it and execute it.

-- 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 not 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 not 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' 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', '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' and column_default is null

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.