转换数据库格式,你可以参考DataCreate的convert_table_to_utf8mb4方法,进行调整。

首先输出的是关于表默认编码的调整,执行之后再次访问,会输出关于表结构的需要调整的字段,第二次处理后会输出如下结构。

建议方案:先导出表结构,然后用结构操作新表,此时只对新表进行处理,执行该方法即可。


-- 忽略外键约束

SET FOREIGN_KEY_CHECKS = 0;

-- 调整表默认编码:
-- ====================================

ALTER TABLE `comment_meta`
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `comments`
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `feed`
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `gallery`
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `gallery_has_comments`
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `gallery_has_pictures`
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `gallery_has_tags`
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `gallery_meta`
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `gallery_outside_pictures`
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `gallery_views`
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `message`
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `options`
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `pictures`
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `pictures_has_comments`
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `pictures_has_tags`
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `pictures_views`
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `posts`
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `posts_has_comments`
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `posts_views`
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `queue`
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `server`
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `tags`
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `user_count`
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `user_meta`
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `users`
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `users_follow_gallery`
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `users_follow_users`
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `users_like_comments`
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `users_like_gallery`
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `users_like_pictures`
DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



-- 调整表comment_meta编码:

ALTER TABLE `comment_meta`
MODIFY COLUMN `meta_key` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

ALTER TABLE `comment_meta`
MODIFY COLUMN `meta_value` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

-- 调整表comments编码:

ALTER TABLE `comments`
MODIFY COLUMN `comment_content` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

ALTER TABLE `comments`
MODIFY COLUMN `comment_agent` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

-- 调整表feed编码:

ALTER TABLE `feed`
MODIFY COLUMN `action` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

ALTER TABLE `feed`
MODIFY COLUMN `content` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

ALTER TABLE `feed`
MODIFY COLUMN `sid` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL;

-- 调整表gallery编码:

ALTER TABLE `gallery`
MODIFY COLUMN `gallery_title` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

ALTER TABLE `gallery`
MODIFY COLUMN `gallery_description` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

-- 调整表gallery_has_tags编码:

ALTER TABLE `gallery_has_tags`
MODIFY COLUMN `tags_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

-- 调整表gallery_meta编码:

ALTER TABLE `gallery_meta`
MODIFY COLUMN `meta_key` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

ALTER TABLE `gallery_meta`
MODIFY COLUMN `meta_value` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

-- 调整表gallery_outside_pictures编码:

ALTER TABLE `gallery_outside_pictures`
MODIFY COLUMN `outside_description` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL;

-- 调整表message编码:

ALTER TABLE `message`
MODIFY COLUMN `msg_title` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL;

ALTER TABLE `message`
MODIFY COLUMN `msg_content` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

-- 调整表options编码:

ALTER TABLE `options`
MODIFY COLUMN `option_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

ALTER TABLE `options`
MODIFY COLUMN `option_value` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

-- 调整表pictures编码:

ALTER TABLE `pictures`
MODIFY COLUMN `server_name` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

ALTER TABLE `pictures`
MODIFY COLUMN `pic_name` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

ALTER TABLE `pictures`
MODIFY COLUMN `pic_path` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '原图路径';

ALTER TABLE `pictures`
MODIFY COLUMN `pic_description` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '图片的单张描述';

ALTER TABLE `pictures`
MODIFY COLUMN `pic_thumbnails_path` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

ALTER TABLE `pictures`
MODIFY COLUMN `pic_hd_path` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

ALTER TABLE `pictures`
MODIFY COLUMN `pic_display_path` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

-- 调整表pictures_has_tags编码:

ALTER TABLE `pictures_has_tags`
MODIFY COLUMN `tags_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

-- 调整表posts编码:

ALTER TABLE `posts`
MODIFY COLUMN `post_title` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

ALTER TABLE `posts`
MODIFY COLUMN `post_name` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

ALTER TABLE `posts`
MODIFY COLUMN `post_content` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

ALTER TABLE `posts`
MODIFY COLUMN `post_description` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL;

ALTER TABLE `posts`
MODIFY COLUMN `post_keyword` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL;

ALTER TABLE `posts`
MODIFY COLUMN `post_category` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

-- 调整表queue编码:

ALTER TABLE `queue`
MODIFY COLUMN `callback` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '队列的回调函数';

ALTER TABLE `queue`
MODIFY COLUMN `param` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '回调函数接受的参数';

ALTER TABLE `queue`
MODIFY COLUMN `library` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '回调函数需要的类库';

ALTER TABLE `queue`
MODIFY COLUMN `message` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '队列执行信息';

ALTER TABLE `queue`
MODIFY COLUMN `type` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL;

-- 调整表server编码:

ALTER TABLE `server`
MODIFY COLUMN `name` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

ALTER TABLE `server`
MODIFY COLUMN `url` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

-- 调整表tags编码:

ALTER TABLE `tags`
MODIFY COLUMN `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

-- 调整表user_meta编码:

ALTER TABLE `user_meta`
MODIFY COLUMN `meta_key` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

ALTER TABLE `user_meta`
MODIFY COLUMN `meta_value` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

-- 调整表users编码:

ALTER TABLE `users`
MODIFY COLUMN `user_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户唯一名称';

ALTER TABLE `users`
MODIFY COLUMN `user_aliases` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用户别名,用作显示';

ALTER TABLE `users`
MODIFY COLUMN `user_email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户唯一邮箱地址';

ALTER TABLE `users`
MODIFY COLUMN `user_url` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '用户个人主页';

ALTER TABLE `users`
MODIFY COLUMN `user_password` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户加密密码';

ALTER TABLE `users`
MODIFY COLUMN `user_salt` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户加密字符';

ALTER TABLE `users`
MODIFY COLUMN `user_cookie_salt` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户COOKIE加密字符';

ALTER TABLE `users`
MODIFY COLUMN `user_cookie_login` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL;

ALTER TABLE `users`
MODIFY COLUMN `user_avatar` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '{default}' COMMENT '用户头像地址';

-- 启用外键约束

SET FOREIGN_KEY_CHECKS = 1;