转换数据库格式,你可以参考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;