MySQL中文乱码处理_字符集转换处理
-- 中文乱码修复
-- 查看MySQL服务参数设置
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)
-- 查看建库的默认字符集
show create database test;
-- 查看建表的默认字符集
show create table yjdb;
-- 修复为utf8字符集
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用户执行查询,把结果执行,把不统一的库和表及字段的字符集统一为utf8
-- 修改全库中建库默认字符集
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');
-- 修改全库中建表默认字符集
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';
-- 修改全库中表的列属性为latin1的字符集为默认,请确认后执行。
-- 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;
-- 为了避免不同环境下出现误差造成影响,可以在建库和表的时候特殊指定字符集
-- 修改库的编码
select concat('ALTER DATABASE ',db,' DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;') from mysql.db_view where db ='xjk_bbs';
-- 修改全库中建表默认字符集
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';
-- 修改全库中表的列属性为latin1的字符集为默认,请确认后执行。
-- 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;
免责声明:
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341