方案一

1、修改database

select * from SCHEMATA;  #查看库的校验码

 

mysql> help alter database;

Name: 'ALTER DATABASE'

Description:

Syntax:

ALTER {DATABASE | SCHEMA} [db_name]

alter_option ...

ALTER {DATABASE | SCHEMA} db_name

UPGRADE DATA DIRECTORY NAME

alter_option: {

[DEFAULT] CHARACTER SET [=] charset_name

| [DEFAULT] COLLATE [=] collation_name

}

 

mysql> show create database lyg2; +----------+------------------------------------------------------------------+ | Database | Create Database                                                  | +----------+------------------------------------------------------------------+ | lyg2     | CREATE DATABASE lyg2 /*!40100 DEFAULT CHARACTER SET utf8mb4 */ | +----------+------------------------------------------------------------------+ mysql> alter database lyg2 CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; Query OK, 1 row affected (0.00 sec) mysql> show create database lyg2; +----------+--------------------------------------------------------------------------------------+ | Database | Create Database                                                                      | +----------+--------------------------------------------------------------------------------------+ | lyg2     | CREATE DATABASE lyg2 /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */ | +----------+--------------------------------------------------------------------------------------+

2、修改table

#查看表的校验码

select * from information_schema.tables where TABLE_SCHEMA='lyg'

#修改表的校验码

mysql> alter table t3 CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

Query OK, 0 rows affected (0.01 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

3、修改字段

#查看字段校验码

select * from information_schema.COLUMNS where TABLE_SCHEMA='lyg' and TABLE_NAME='t3'\G

mysql> show create table t3\G *************************** 1. row *************************** Table: t3 Create Table: CREATE TABLE t3 ( id int(11) DEFAULT NULL, name varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin 1 row in set (0.00 sec) #时间较长 mysql> alter table t3 change name name varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL; Query OK, 2 rows affected (0.04 sec) Records: 2  Duplicates: 0  Warnings: 0

 

方案二：

1、修改database，见方案一

 

2、导出表结构mysqldump带-d

mysqldump --max-allowed-packet=1073741824 -h 10.200.1.151 -uroot -p  -P8002  --single-transaction  --set-gtid-purged=OFF  --master-data=2  -d -q -B lyg3 lyg4 >   lyg3_20220629.sql

 

如果包含database，直接替换CHARACTER SET utf8mb4--》CHARACTER SET utf8mb4 COLLATE utf8mb4_bin

:%s/CHARACTER SET utf8mb4/CHARACTER SET utf8mb4 COLLATE utf8mb4_bin/g

表结构直接替换CHARSET=utf8mb4--》CHARSET=utf8mb4 COLLATE=utf8mb4_bin

:%s/CHARSET=utf8mb4/CHARSET=utf8mb4 COLLATE=utf8mb4_bin/g

 

 

 

CREATE TABLE t2 (

id int(11) DEFAULT NULL,

NUM int(11) DEFAULT NULL,

name varchar(10) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_bin;

3、导出数据 mysqldump带-t

mysqldump --max-allowed-packet=1073741824 -h 10.200.1.151 -uroot -p  -P8002  --single-transaction  --set-gtid-purged=OFF  --master-data=2  -t -q -B lyg3 lyg4 >   lyg3_20220629.sql

4、修改配置文件校验码

collation_server=utf8mb4_bin

4、导入表结构

drop database lyg3;

drop database lyg4;

mysql> source lyg3_20220629.sql;

#检查库、表、字段校验码

select * from information_schema.SCHEMATA;

select TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,TABLE_COLLATION from  information_schema.tables where  TABLE_SCHEMA in ('lyg3','lyg4');

select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE,CHARACTER_SET_NAME,COLLATION_NAME,COLUMN_TYPE from information_schema.COLUMNS where TABLE_SCHEMA in ('lyg3','lyg4');

 

select TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,TABLE_COLLATION from  information_schema.tables where  TABLE_SCHEMA in ('lyg3','lyg4');

 

 

6、导入数据