如何仅用MySQL命令转换整库字符集修复latin1配置导致的存储乱码

SQL文件源码 #

-- phpMyAdmin SQL Dump
-- version 4.0.4.1
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Oct 20, 2021 at 07:03 PM
-- Server version: 5.5.32
-- PHP Version: 5.4.16

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `test`
--

-- --------------------------------------------------------

--
-- Table structure for table `test_table`
--

CREATE TABLE IF NOT EXISTS `test_table` (
  `id` int(11) NOT NULL,
  `title` text CHARACTER SET utf8
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `test_table`
--

INSERT INTO `test_table` (`id`, `title`) VALUES
(2, 'یاشیل قطره'),
(3, 'توسعه صنعت Ùرآیند باختر'),
(4, 'الوند مکش'),
(5, 'نهاده گستر'),
(6, 'پاکان بذر');

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

问题描述 #

我们通常使用以下两个命令：

ALTER DATABASE
ALTER TABLE
示例如下：

ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

可以将整个MySQL数据库的字符集和排序规则转换为UTF-8，解决文本存储错误的问题。但实际使用时发现，ALTER TABLE tablename CHARACTER SET utf8仅会为表设置默认字符集，仅作用于后续新建的列，不会转换已有列的现有字符集。请问如何仅通过MySQL命令完成转换，无需导出数据库为文件？
注意：该问题的触发原因是服务器的字符集配置未正确设置为utf8，仍保留MySQL默认的latin1设置。
补充说明：已补充SQL源码，无需跳转链接查看

单表单库操作步骤 #

1. 先修改数据库全局默认字符集，后续新建的表会自动继承该配置

ALTER DATABASE 替换为你的库名 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

推荐优先使用utf8mb4而非MySQL原生的utf8，后者最多仅支持3字节字符，无法存储emoji等特殊4字节UTF-8字符，utf8mb4是完整的UTF-8实现。
2. 转换单张表所有已有列的字符集
你之前用到的ALTER TABLE 表名 CHARACTER SET确实只会修改表的默认配置，不会改动已有列，要同步转换所有现有列的字符集，需要添加CONVERT TO参数：

ALTER TABLE 替换为你的表名 CONVERT TO CHARACTER SET utf8mb4 COLLATE = utf8mb4_unicode_ci;

这条命令会自动遍历表内所有字符类型字段（CHAR、VARCHAR、TEXT等），全部转换为指定的字符集和排序规则，同时更新表的默认字符集配置。

批量处理多表的简化方法 #

如果库内表数量较多，不想手动逐个编写转换命令，可以执行以下SQL直接生成所有表的转换语句，复制生成的结果直接执行即可：

SELECT CONCAT('ALTER TABLE `', table_name, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') AS alter_command
FROM information_schema.tables 
WHERE table_schema = '替换为你的库名';

你提供的示例中使用的是MyISAM引擎，如果只需要转换对应引擎的表，可以在WHERE条件后追加AND ENGINE = 'MyISAM'过滤。

注意事项 #

• 操作前务必先做全库备份，避免转换过程中出现异常导致数据损坏
• 大表执行转换时会锁表，建议选择业务低峰期操作
• 转换完成后可以执行以下语句验证配置是否生效：

-- 查看当前库的字符集配置
SELECT default_character_set_name, default_collation_name FROM information_schema.SCHEMATA WHERE schema_name = '替换为你的库名';
-- 查看指定表字段的字符集配置
SELECT c.character_set_name, c.collation_name FROM information_schema.`COLUMNS` c WHERE c.table_schema = '替换为你的库名' AND c.table_name = '替换为你的表名' AND c.column_name = 'title';

内容的提问来源于stack exchange，提问作者webrefer