一、 修改数据库编码及字符集

修改数据库编码及字符集比较简单：

<span class="token keyword">ALTER</span> <span class="token keyword">DATABASE</span> db_name <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> <span class="token operator">=</span> utf8mb4 <span class="token keyword">COLLATE</span> <span class="token operator">=</span> utf8mb4_general_ci<span class="token punctuation">;</span>

• db_name： 数据库名称
• set =： 后面跟着编码
• COLLATE =： 后面跟着字符集

二、 修改数据表与表中字段的编码及字符集

注：这里需要分两步处理！

1. 利用语句，生成所有实际执行的语句：

<span class="token keyword">SELECT</span> 
CONCAT<span class="token punctuation">(</span><span class="token string">"ALTER TABLE `"</span><span class="token punctuation">,</span> TABLE_NAME<span class="token punctuation">,</span><span class="token string">"` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;"</span><span class="token punctuation">)</span> 
<span class="token keyword">AS</span> target_tables
<span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span><span class="token keyword">TABLES</span>
<span class="token keyword">WHERE</span> TABLE_SCHEMA<span class="token operator">=</span><span class="token string">"db_name"</span> <span class="token comment"># 修改为需要更改的数据库名称</span>
<span class="token operator">AND</span> TABLE_TYPE<span class="token operator">=</span><span class="token string">"BASE TABLE"</span>

• SET \ COLLATE 设置为需要修改为的编码 \ 字符集
• TABLE_SCHEMA="db_name"：修改为数据库名称

此语句会基于 MySQL 的元数据表，得到一组可直接执行的 SQL 列表，如下：

<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <span class="token punctuation">`</span>table1<span class="token punctuation">`</span> <span class="token keyword">CONVERT</span> <span class="token keyword">TO</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> utf8mb4 <span class="token keyword">COLLATE</span> utf8mb4_general_ci<span class="token punctuation">;</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <span class="token punctuation">`</span>table2<span class="token punctuation">`</span> <span class="token keyword">CONVERT</span> <span class="token keyword">TO</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> utf8mb4 <span class="token keyword">COLLATE</span> utf8mb4_general_ci<span class="token punctuation">;</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <span class="token punctuation">`</span>table3<span class="token punctuation">`</span> <span class="token keyword">CONVERT</span> <span class="token keyword">TO</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> utf8mb4 <span class="token keyword">COLLATE</span> utf8mb4_general_ci<span class="token punctuation">;</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <span class="token punctuation">`</span>table4<span class="token punctuation">`</span> <span class="token keyword">CONVERT</span> <span class="token keyword">TO</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> utf8mb4 <span class="token keyword">COLLATE</span> utf8mb4_general_ci<span class="token punctuation">;</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <span class="token punctuation">`</span>table5<span class="token punctuation">`</span> <span class="token keyword">CONVERT</span> <span class="token keyword">TO</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> utf8mb4 <span class="token keyword">COLLATE</span> utf8mb4_general_ci<span class="token punctuation">;</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <span class="token punctuation">`</span>table6<span class="token punctuation">`</span> <span class="token keyword">CONVERT</span> <span class="token keyword">TO</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> utf8mb4 <span class="token keyword">COLLATE</span> utf8mb4_general_ci<span class="token punctuation">;</span>

<span class="token comment">// ...</span>

其中，table1 到 table6 即为数据库中的所有数据表。

2. 直接将语句粘贴并执行即可。

• 注意，这里使用 CONVERT TO 而非 DEFAULT，是因为后者不会修改表中字段的编码和字符集。
• 此外，对于数据表比较多的数据库，可以先将第一步的执行结果导出到 .sql 文件，再通过该 SQL 文件执行即可。