游戏服java genDB的时候生成的数据库默认格式是utf8,结果启动后名字一些特殊字符表情无法录入数据库,需要把数据库 表 字段全改为utf8mb4
utf8mb4是utf8的超集,支持常见的 Unicode 字符 + 部分罕用汉字 + emoji表情 + 新增的 Unicode 字符等

修改修改数据库字符集

db_name为要修改的数据库名

ALTER DATABASE db_name为要修改的数据库名 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

生成修改所有表 字段的sql

执行下面sql,复制生成的拼接sql进行执行

-- 批量修改所有表的默认字符集
SELECT
    CONCAT( 'ALTER TABLE ', table_name, ' DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;' ) AS _sql 
FROM
    information_schema.`TABLES` 
WHERE
    TABLE_SCHEMA = 'db_name';

-- 批量修改所有字段的字符集
SELECT
    CONCAT(
        'ALTER TABLE ',
        table_name,
        ' MODIFY ',
        column_name,
        ' ',
        data_type,
    CASE

            WHEN data_type IN ( 'text', 'blob' ) THEN
            '' ELSE CONCAT( '(', character_maximum_length, ') ' ) 
        END,
        'CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ',
        ( CASE WHEN is_nullable = 'NO' THEN ' NOT NULL' ELSE '' END ),
        ';' 
) AS _sql 
FROM
    information_schema.`COLUMNS` 
WHERE
    TABLE_SCHEMA = 'db_name' 
    AND character_maximum_length IS NOT NULL 
    AND character_set_name <> 'utf8mb4' 
    AND collation_name <> 'utf8mb4_general_ci';

这边附录一个自用容器启动检查数据库格式脚本

#!/bin/bash
if [ -f /g123/utf8mb4.txt ];then
echo "此服数据库 表 字段格式已初始化为utf8mb4"

else
echo "此服数据库 表 字段格式正在初始化为utf8mb4"
for i in  $(find /gldata1/server/ -type f  -name "game_server.conf");
do
mysqladr=$(grep "db.url=jdbc:mysql" ${i}|sed -n "1p"|awk -F "/|?" '{print $3}')
echo $mysqladr
mysqldbb=$(grep "db.schema=" ${i}|awk -F "=" '{print $2}')
echo $mysqldbb
mysql_pass=$(grep "db.pwd=" ${i}|awk -F "=" '{print $2}')
echo  $mysql_pass

mysql -h"$mysqladr" -ulv2 -p"$mysql_pass" -P 3306 -e "ALTER DATABASE "${mysqldbb}" DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;"

mysql -h"$mysqladr" -ulv2 -p"$mysql_pass" -P 3306 -e "use "${mysqldbb}";ALTER TABLE activitylist_global DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE arena_rank_10v10 DEFAULT CHARACTER SET  COLLATE utf8mb4_general_ci;
--省略几百行变更表格式为utf8mb4 sql

ALTER TABLE arena_rank_10v10 MODIFY sn varchar(255)    CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci  NOT NULL;
--省略几百行变更表字段格式为utf8mb4 sql
"  
echo "此服数据库 表 字段格式已初始化为utf8mb4"
echo "1" >/g123/utf8mb4.txt
done

fi