一个全栈攻城狮的纯技术干货分享 ~
搜索到
2
篇与
的结果
-
批量将 discuz 数据库编码从 utf8 转为 utf8mb4 为获得emoji等特殊字符支持，需要将discuz的数据库编码转换为utf8mb4。确认数据库版本支持utf8mb4和utf8mb4_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 $$;
