批量修改数据库表格编码格式

最新推荐文章于 2023-07-25 23:44:41 发布

志存高远，责任为先

最新推荐文章于 2023-07-25 23:44:41 发布

阅读量280

点赞数

文章标签：数据库 sql mysql

本文链接：https://blog.csdn.net/qq_35491593/article/details/128251691

版权

执行SQL

SELECT
CONCAT(“ALTER TABLE ", TABLE_NAME," CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;”)
AS target_tables
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA=“archives” # 修改为需要更改的数据库名称
AND TABLE_TYPE=“BASE TABLE”

生成的结果sql执行就行

ALTER TABLE archives_classify CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE archives_info CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE archives_info_detail CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE archives_log CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE barcode_printing CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE client_unit CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE district CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE file_img CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE qrtz_blob_triggers CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE qrtz_calendars CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE qrtz_cron_triggers CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE qrtz_fired_triggers CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE qrtz_job_details CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE qrtz_locks CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE qrtz_paused_trigger_grps CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE qrtz_scheduler_state CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE qrtz_simple_triggers CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE qrtz_simprop_triggers CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE qrtz_triggers CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE region CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE robot_job CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE robot_log CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE schedule_job CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE schedule_job_log CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE social_security CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE sys_captcha CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE sys_config CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE sys_dept CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE sys_dict CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE sys_dict_type CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE sys_file CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE sys_log CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE sys_menu CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE sys_msg CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE sys_oss CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE sys_role CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE sys_role_menu CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE sys_user CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE sys_user_dept CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE sys_user_role CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE sys_user_token CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE talent CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE talent_transfer_in CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE talent_transfer_out CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE tb_user CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE unit_information CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;