修改myslq配置

wcps8Server\mysql57\my.ini

  my.ini.txt
下载 详情 

重启wcp的mysql服务

注意：mysql访问时要使用支持utf8mb4的客户端进行访问

--查询字符集(验证字符集修改成果)

SHOW VARIABLES LIKE 'character%';

更新表和字段编码

1.更新库编码

ALTER DATABASE wcp CHARACTER SET = utf8mb4

2.批量删除外键

SELECT
	CONCAT(
		"alter table ",
		TABLE_NAME,
		" drop foreign key ",
		CONSTRAINT_NAME,
		"; "
	) AS runMe
FROM
	information_schema.key_column_usage
WHERE
	TABLE_SCHEMA = 'wcp'
AND CONSTRAINT_NAME <> 'PRIMARY';

2.批量修改数据表编码

批量生成修改表编码的SQL

SELECT
	CONCAT(
		'ALTER TABLE ',
		TABLE_NAME,
		' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;'
	) as run_sql
FROM
	information_schema.`TABLES`
WHERE
	TABLE_SCHEMA = 'wcp'; 

修改wcp数据库连接配置文件

character_set_server=utf8mb4

重启wcp服务

------------------------------------可选--------------------------------------

**批量生成索引**

SELECT
    CONCAT(
        "create index IA_",
        TABLE_NAME,"_",column_name,
        " on ",
        TABLE_NAME,"(",column_name,")"
        "; "
    ) AS runMe
FROM
    information_schema.key_column_usage
WHERE
    TABLE_SCHEMA = 'wcp'
AND CONSTRAINT_NAME <> 'PRIMARY';