background
Character set replacement method
Modify the default character set in the database configuration
[mysqld] character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci Modify the character set of the database
ALTER DATABASE ` database name ` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' Modify Datasheet Character Set
SELECT TABLE_SCHEMA 'database', TABLE_NAME 'table', TABLE_COLLATION 'Original collation', CONCAT ('ALTER TABLE ', TABLE_SCHEMA,'. ', TABLE_NAME,' DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ')' Modify SQL ' FROM information_schema.`TABLES` WHERE (TABLE_COLLATION='utf8 'or TABLE_COLLATION like'% general% ') AND TABLE_SCHEMA=' [database name] ';
Modify the character set of database table fields
SELECT c. TABLE_SCHEMA 'database', c. TABLE_NAME 'table', c. COLUMN_NAME 'field', c. COLUMN_DEFAULT 'Default value', c. IS_NULLABLE 'Is it empty', c. DATA_TYPE 'Field type', c. Character_set_name 'Original character set', c. Collation_name 'Original collation', CONCAT( 'ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' MODIFY COLUMN ', COLUMN_NAME, ' ', COLUMN_TYPE, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ', CASE WHEN c.is_nullable = 'NO' THEN 'NOT NULL' ELSE 'NULL' END, CASE WHEN c.COLUMN_DEFAULT = '' THEN ' DEFAULT ''''' WHEN c.COLUMN_KEY = 'PRI' THEN '' WHEN c.COLUMN_DEFAULT IS NULL THEN ( CASE WHEN c.is_nullable = 'NO' THEN '' ELSE ' DEFAULT NULL' END ) ELSE concat( ' DEFAULT ', '''', c.COLUMN_DEFAULT, '''' ) END, ' comment ', '''', c.COLUMN_COMMENT, '''', ';' ）'Fix SQL' FROM information_schema.`COLUMNS` c WHERE (CHARACTER_SET_NAME='utf8 'or COLLATION_NAME like'% general% ') AND TABLE_SCHEMA=' [database name] ' 
