批量将discuz数据库编码从utf8转为utf8mb4

为获得emoji等特殊字符支持,需要将discuz的数据库编码转换为utf8mb4

  • 确认数据库版本支持utf8mb4utf8mb4_0900_ai_ci
  • 由于用户表中username是不重复索引,且区分大小写和声调,字符集需设置为utf8mb4_general_ci
  • 手动执行下面的SELECT CONCAT语句,然后使用执行结果替换该语句,保存为一个 sql 文件
  • 为避免 php 运行超时,请使用 cli 工具导入保存的 sql 文件
USE `discuz_databese`;

-- 更改主库字符集

ALTER DATABASE `discuz_databese` COLLATE utf8mb4_0900_ai_ci;

-- 请使用下列语句的执行结果替换该语句

SELECT CONCAT(
  'ALTER TABLE ', `TABLE_NAME`, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;'
) FROM `information_schema`.`TABLES`
  WHERE `TABLE_SCHEMA` = 'discuz_databese'
    AND `TABLE_NAME` <> 'pre_common_member'
    AND `TABLE_NAME` <> 'pre_common_member_archive'
    AND `TABLE_NAME` <> 'pre_ucenter_members';

-- 单独修改用户表(`username`字符集为`utf8mb4_general_ci`)

ALTER TABLE `pre_common_member` COLLATE utf8mb4_0900_ai_ci,
  MODIFY `email` char(40) NOT NULL DEFAULT '',
  MODIFY `username` char(15) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  MODIFY `password` char(32) NOT NULL DEFAULT '',
  MODIFY `extgroupids` char(20) NOT NULL DEFAULT '',
  MODIFY `timeoffset` char(4) NOT NULL DEFAULT '';

ALTER TABLE `pre_common_member_archive` COLLATE utf8mb4_0900_ai_ci,
  MODIFY `email` char(40) NOT NULL DEFAULT '',
  MODIFY `username` char(15) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  MODIFY `password` char(32) NOT NULL DEFAULT '',
  MODIFY `extgroupids` char(20) NOT NULL DEFAULT '',
  MODIFY `timeoffset` char(4) NOT NULL DEFAULT '';

ALTER TABLE `pre_ucenter_members` COLLATE utf8mb4_0900_ai_ci,
  MODIFY `username` char(15) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
  MODIFY `password` char(32) NOT NULL DEFAULT '',
  MODIFY `email` char(32) NOT NULL DEFAULT '',
  MODIFY `myid` char(30) NOT NULL DEFAULT '',
  MODIFY `myidkey` char(16) NOT NULL DEFAULT '',
  MODIFY `regip` char(15) NOT NULL DEFAULT '',
  MODIFY `salt` char(6) NOT NULL,
  MODIFY `secques` char(8) NOT NULL DEFAULT '';

下面是写给强迫症患者的,你可能并不需要 !!

-- 创建临时库

CREATE DATABASE `tmp123`;

-- 从原始数据库复制表(会丢弃索引)

USE `discuz_database`;

CREATE TABLE `tmp123`.`pre_common_member` AS SELECT * FROM `pre_common_member`;
CREATE TABLE `tmp123`.`pre_common_member_archive` AS SELECT * FROM `pre_common_member_archive`;

CREATE TABLE `tmp123`.`pre_ucenter_members` AS SELECT * FROM `pre_ucenter_members`;

-- 进入临时库

USE `tmp123`;

-- 转换数据库编码和字符集(已无索引,可以转换)

ALTER TABLE `pre_common_member` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE `pre_common_member_archive` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

ALTER TABLE `pre_ucenter_members` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- 获取重复的用户统计到临时表

CREATE TABLE `pre_ucenter_members_having_count` AS
  SELECT `username`, count(username) as `count` FROM `pre_ucenter_members`
    GROUP BY `username` HAVING `count` > 1;

-- 获取重复的用户详情到临时表

CREATE TABLE `pre_ucenter_members_having` AS
  SELECT * FROM `pre_ucenter_members` WHERE `username` IN (
      SELECT `username` FROM `pre_ucenter_members_having_count`
  );

PostgreSQL 更改库及所有表的所有者(OWNER)

  • database, public,schema1 为你需要修改的库名
  • username 为更改后的所有者用户名
\c database;

DO $$
DECLARE
    r record;
    i int;
    v_schema text[] := '{public,schema1}';
    v_new_owner varchar := 'username';
BEGIN
    FOR r IN
        SELECT 'ALTER TABLE "' || table_schema || '"."' || table_name || '" OWNER TO ' || v_new_owner || ';' AS a FROM information_schema.tables WHERE table_schema = ANY (v_schema)
        UNION ALL
        SELECT 'ALTER TABLE "' || sequence_schema || '"."' || sequence_name || '" OWNER TO ' || v_new_owner || ';' AS a FROM information_schema.sequences WHERE sequence_schema = ANY (v_schema)
        UNION ALL
        SELECT 'ALTER TABLE "' || table_schema || '"."' || table_name || '" OWNER TO ' || v_new_owner || ';' AS a FROM information_schema.views WHERE table_schema = ANY (v_schema)
        UNION ALL
        SELECT 'ALTER FUNCTION "' || nsp.nspname || '"."' || p.proname || '"(' || pg_get_function_identity_arguments(p.oid) || ') OWNER TO ' || v_new_owner || ';' AS a FROM pg_proc p JOIN pg_namespace nsp ON p.pronamespace = nsp.oid WHERE nsp.nspname = ANY (v_schema)
        UNION ALL
        SELECT 'ALTER DATABASE "' || current_database() || '" OWNER TO ' || v_new_owner
    LOOP
        EXECUTE r.a;
    END LOOP;
    FOR i IN array_lower(v_schema, 1)..array_upper(v_schema, 1)
    LOOP
        EXECUTE 'ALTER SCHEMA "' || v_schema[i] || '" OWNER TO ' || v_new_owner;
    END LOOP;
END
$$;