如何将整个MySQL数据库字符集和排序转换为UTF-8?

How can I convert entire MySQL database character-set to UTF-8 and collation to UTF-8?

如何将整个MySQL数据库字符集转换为UTF-8，并对UTF-8进行排序?

15 个解决方案

#1

Use the ALTER DATABASE and ALTER TABLE commands.

使用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;

Or if you're still on MySQL 5.5.2 or older which didn't support 4-byte UTF-8, use utf8 instead of utf8mb4:

或者，如果你仍然在MySQL 5.5.2或更老的版本中，它不支持4字节的UTF-8，那么使用utf8而不是utf8mb4:

ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

#2

1. Make a backup!

   进行备份!

2. Then you need to set the default char sets on the database. This does not convert existing tables, it only sets the default for newly created tables.

   然后需要在数据库上设置默认的char集。这不会转换现有的表，它只设置新创建的表的默认值。

   ALTER DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;
   

3. Then, you will need to convert the char set on all existing tables and their columns. This assumes that your current data is actually in the current char set. If your columns are set to one char set but your data is really stored in another then you will need to check the MySQL manual on how to handle this.

   然后，您需要转换所有现有表及其列上的char。这假设您当前的数据实际上是在当前的char集中，如果您的列被设置为一个char集，但是您的数据确实存储在另一个字符中，那么您将需要检查MySQL手册来处理这个问题。

   ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
   

#3

On the commandline shell

If you're one the commandline shell, you can do this very quickly. Just fill in "dbname" :D

如果你是一个命令行shell，你可以非常快地完成这个任务。只需填写“dbname”:D。

DB="dbname"
(
    echo 'ALTER DATABASE `'"$DB"'` CHARACTER SET utf8 COLLATE utf8_general_ci;'
    mysql "$DB" -e "SHOW TABLES" --batch --skip-column-names \
    | xargs -I{} echo 'ALTER TABLE `'{}'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;'
) \
| mysql "$DB"

One-liner for simple copy/paste

DB="dbname"; ( echo 'ALTER DATABASE `'"$DB"'` CHARACTER SET utf8 COLLATE utf8_general_ci;'; mysql "$DB" -e "SHOW TABLES" --batch --skip-column-names | xargs -I{} echo 'ALTER TABLE `'{}'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;' ) | mysql "$DB"

#4

You can create the sql to update all tables with:

您可以创建sql来更新所有表:

SELECT CONCAT("ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CHARACTER SET utf8 COLLATE utf8_general_ci;   ",
    "ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;  ") 
    AS alter_sql
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = your_database_name;

Capture the output and run it.

捕获输出并运行它。

Arnold Daniels' answer above is more elegant.

阿诺德·丹尼尔斯的回答更优雅。

#5

Before proceeding, ensure that you: Have completed a full database backup!

在继续之前，确保您:已经完成了完整的数据库备份!

Step 1: Database Level Changes

步骤1:数据库级别的更改。

• Identifying the Collation and Character set of your database

  识别数据库的排序和字符集。

  SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM 
  information_schema.SCHEMATA S
  WHERE schema_name = 'your_database_name'
  AND
  (DEFAULT_CHARACTER_SET_NAME != 'utf8'
      OR
   DEFAULT_COLLATION_NAME not like 'utf8%');
  

• Fixing the collation for the database

  修复数据库的排序。

  ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;
  

Step 2: Table Level Changes

步骤2:表级别更改。

• Identifying Database Tables with the incorrect character set or collation

  使用不正确的字符集或排序来标识数据库表。

  SELECT CONCAT(
  'ALTER TABLE ',  table_name, ' CHARACTER SET utf8 COLLATE utf8_general_ci;  ', 
  'ALTER TABLE ',  table_name, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;  ')
  FROM information_schema.TABLES AS T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` AS C
  WHERE C.collation_name = T.table_collation
  AND T.table_schema = 'your_database_name'
  AND
  (C.CHARACTER_SET_NAME != 'utf8'
      OR
   C.COLLATION_NAME not like 'utf8%')
  

• Adjusting table columns' collation and character set

  调整表列的整理和字符集。

Capture upper sql output and run it. (like following)

捕获上层sql输出并运行它。(如后)

ALTER TABLE rma CHARACTER SET utf8 COLLATE utf8_general_ci;ALTER TABLE rma CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;  
ALTER TABLE rma_history CHARACTER SET utf8 COLLATE utf8_general_ci;ALTER TABLE rma_history CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;  
ALTER TABLE rma_products CHARACTER SET utf8 COLLATE utf8_general_ci;ALTER TABLE rma_products CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;  
ALTER TABLE rma_report_period CHARACTER SET utf8 COLLATE utf8_general_ci;ALTER TABLE rma_report_period CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;  
ALTER TABLE rma_reservation CHARACTER SET utf8 COLLATE utf8_general_ci;ALTER TABLE rma_reservation CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;  
ALTER TABLE rma_supplier_return CHARACTER SET utf8 COLLATE utf8_general_ci;ALTER TABLE rma_supplier_return CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;  
ALTER TABLE rma_supplier_return_history CHARACTER SET utf8 COLLATE utf8_general_ci;ALTER TABLE rma_supplier_return_history CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;  
ALTER TABLE rma_supplier_return_product CHARACTER SET utf8 COLLATE utf8_general_ci;ALTER TABLE rma_supplier_return_product CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; 

refer to: https://confluence.atlassian.com/display/CONFKB/How+to+Fix+the+Collation+and+Character+Set+of+a+MySQL+Database

查阅:https://confluence.atlassian.com/display/CONFKB/How + +修复+ +校对+和+品质+ + + + MySQL数据库

#6

Use HeidiSQL. Its free and a very good db tool.

使用HeidiSQL。它的免费和一个非常好的数据库工具。

From tools menu, enter Bulk table editor

从工具菜单，输入批量表编辑器。

Select the complete database or pick tables to convert,

选择完整的数据库或选择要转换的表，

• tick Change default collation: utf8mb4_general_ci
• 勾选更改默认排序:utf8mb4_general_ci。
• tick Convert to charset: utf8
• 标记转换到charset: utf8。

Execute

执行

This converts complete database from latin to utf8 in just a few seconds.

这将在几秒钟内将完整的数据库从拉丁语转换为utf8。

Works like a charm :)

像魅力一样的工作:)

HeidiSQL connects by default as utf8 so any special characters should now be seen as the character (æ ø å) and not as encoded when inspecting the table data.

HeidiSQL连接默认为utf8所以现在应该被视为任何特殊字符(æø)而不是编码时检查表数据。

The real pitfall when moving from latin to utf8 is to make sure pdo connects with utf8 charset. If not you will get rubbish data inserted to the utf8 table and question marks all over the place on your web page, making you think the table data is not utf8...

从拉丁语转到utf8时，真正的陷阱是确保pdo连接utf8字符集。如果不是，您将会将垃圾数据插入到utf8表中，并且在您的web页面上到处都有问号，使您认为表数据不是utf8……

#7

Inspired by @sdfor comment, here is a bash script that does the job

受@sdfor注释的启发，这里是一个执行任务的bash脚本。

#!/bin/bash

printf "### Converting MySQL character set ###\n\n"

printf "Enter the encoding you want to set: "
read -r CHARSET

# Get the MySQL username
printf "Enter mysql username: "
read -r USERNAME

# Get the MySQL password
printf "Enter mysql password for user %s:" "$USERNAME"
read -rs PASSWORD

DBLIST=( mydatabase1 mydatabase2 )

printf "\n"


for DB in "${DBLIST[@]}"
do
(
    echo 'ALTER DATABASE `'"$DB"'` CHARACTER SET utf8 COLLATE `'"$CHARSET"'`;'
    mysql "$DB" -u"$USERNAME" -p"$PASSWORD" -e "SHOW TABLES" --batch --skip-column-names \
    | xargs -I{} echo 'ALTER TABLE `'{}'` CONVERT TO CHARACTER SET utf8 COLLATE `'"$CHARSET"'`;'
) \
| mysql "$DB" -u"$USERNAME" -p"$PASSWORD"

echo "$DB database done..."
done

echo "### DONE ###"
exit

#8

For databases that have a high number of tables you can use a simple php script to update the charset of the database and all of the tables using the following:

对于拥有大量表的数据库，您可以使用简单的php脚本来更新数据库的charset和所有表，使用以下方法:

$conn = mysqli_connect($host, $username, $password, $database);

if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$alter_database_charset_sql = "ALTER DATABASE ".$database." CHARACTER SET utf8 COLLATE utf8_unicode_ci";
mysqli_query($conn, $alter_database_charset_sql);

$show_tables_result = mysqli_query($conn, "SHOW TABLES");
$tables  = mysqli_fetch_all($show_tables_result);

foreach ($tables as $index => $table) {
  $alter_table_sql = "ALTER TABLE ".$table[0]." CONVERT TO CHARACTER SET utf8  COLLATE utf8_unicode_ci";
  $alter_table_result = mysqli_query($conn, $alter_table_sql);
  echo "<pre>";
  var_dump($alter_table_result);
  echo "</pre>";
}

#9

In case the data is not in the same character set you might consider this snippet from http://dev.mysql.com/doc/refman/5.0/en/charset-conversion.html

如果数据不是相同的字符集，您可以从http://dev.mysql.com/doc/refman/5.0/en/charset-conversion.html中考虑这段代码。

If the column has a nonbinary data type (CHAR, VARCHAR, TEXT), its contents should be encoded in the column character set, not some other character set. If the contents are encoded in a different character set, you can convert the column to use a binary data type first, and then to a nonbinary column with the desired character set.

如果列有一个二进制数据类型(CHAR、VARCHAR、文本),其内容应该在列编码字符集,不是其他编码字符集。如果内容在不同的字符集,您可以转换列使用二进制数据类型,然后与一个非列所需的字符集。

Here is an example:

这是一个例子:

 ALTER TABLE t1 CHANGE c1 c1 BLOB;
 ALTER TABLE t1 CHANGE c1 c1 VARCHAR(100) CHARACTER SET utf8;

Make sure to choose the right collation, or you might get unique key conflicts. e.g. Éleanore and Eleanore might be considered the same in some collations.

确保选择正确的排序规则，否则可能会出现独特的关键冲突。在某些排序中，Eleanore和Eleanore可能被认为是相同的。

Aside:

旁白:

I had a situation where certain characters "broke" in emails even though they were stored as UTF-8 in the database. If you are sending emails using utf8 data, you might want to also convert your emails to send in UTF8.

我的情况是，某些字符在电子邮件中“断开”，尽管它们在数据库中存储为UTF-8。如果您使用utf8数据发送电子邮件，您可能还想转换您的电子邮件发送到utf8。

In PHPMailer, just update this line: public $CharSet = 'utf-8';

在PHPMailer中，只需更新这一行:public $CharSet = 'utf-8';

#10

mysqldump -uusername -ppassword -c -e --default-character-set=utf8 --single-transaction --skip-set-charset --add-drop-database -B dbname > dump.sql
cp dump.sql dump-fixed.sql
vim dump-fixed.sql


:%s/DEFAULT CHARACTER SET latin1/DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci/
:%s/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8/
:wq

mysql -uusername -ppassword < dump-fixed.sql

#11

If you cannot get your tables to convert or your table is always set to some non-utf8 character set, but you want utf8, your best bet might be to wipe it out and start over again and explicitly specify:

如果您无法让您的表转换或您的表总是被设置为一些非utf8字符集，但是您想要utf8，您最好的方法可能是将其擦除并重新启动，并显式指定:

create database database_name character set utf8;

#12

The only solution that worked for me: http://docs.moodle.org/23/en/Converting_your_MySQL_database_to_UTF8

为我工作的唯一解决方案是:http://docs.moodle.org/23/en/Converting_your_MySQL_database_to_UTF8。

Converting a database containing tables

mysqldump -uusername -ppassword -c -e --default-character-set=utf8 --single-transaction --skip-set-charset --add-drop-database -B dbname > dump.sql

cp dump.sql dump-fixed.sql
vim dump-fixed.sql

:%s/DEFAULT CHARACTER SET latin1/DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci/
:%s/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8/
:wq

mysql -uusername -ppassword < dump-fixed.sql

#13

alter table table_name charset = 'utf8';

alter table table_name charset = 'utf8';

This is a simple query i was able to use for my case, you can change the table_name as per your requirement(s).

这是一个简单的查询，我可以为我的情况使用，您可以根据您的需求更改table_name。

#14

To change the character set encoding to UTF-8 for the database itself, type the following command at the mysql> prompt. Replace DBNAME with the database name:

要更改数据库本身的UTF-8字符集编码，请在mysql>提示符下输入以下命令。用数据库名替换DBNAME:

ALTER DATABASE DBNAME CHARACTER SET utf8 COLLATE utf8_general_ci;

#15

You can also DB tool Navicat, which does it more easier.

您还可以使用DB工具Navicat，这样更容易。

• Siva.
• 湿婆。

Right Click Your Database & select DB Properties & Change as you desired in Drop Down

右键单击你的数据库，选择DB属性，然后按你想要的方式进行修改。