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

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

581028 次浏览
小开
最佳答案

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

  1. 备份!< / >强

  2. 然后你需要设置数据库上的默认字符集。这不会转换现有的表，只会为新创建的表设置默认值。

    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.

    ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
小开

在命令行shell上

如果你是一个命令行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"

简单复制/粘贴的一行程序

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"
小开

你可以创建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";

捕获输出并运行它。

阿诺德•丹尼尔斯(Arnold Daniels)的回答更为优雅。

小开

如果你不能让你的表转换，或者你的表总是设置为一些非utf8字符集，但你想要utf8，你最好的办法可能是擦掉它，重新开始，并显式地指定:

create database database_name character set utf8;
小开

唯一适合我的解决方案是:http://docs.moodle.org/23/en/Converting_your_MySQL_database_to_UTF8

转换包含表的数据库

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
小开

如果数据不在同一个字符集中，你可以考虑http://dev.mysql.com/doc/refman/5.0/en/charset-conversion.html中的这个片段

如果列具有非二进制数据类型(CHAR, VARCHAR, TEXT)，则其 内容应该在列字符集中编码，而不是其他字符集 字符集。如果内容用不同的字符编码 设置后，您可以先将列转换为使用二进制数据类型，然后 然后转换到具有所需字符集的非二进制列

这里有一个例子:

 ALTER TABLE t1 CHANGE c1 c1 BLOB;
ALTER TABLE t1 CHANGE c1 c1 VARCHAR(100) CHARACTER SET utf8;
确保选择正确的排序规则，否则你可能会得到唯一的键冲突。如。 Éleanore和Eleanore在某些排序中可能被认为是相同的

旁白:

我曾遇到过这样的情况，电子邮件中的某些字符“坏了”，尽管它们在数据库中以UTF-8格式存储。如果您使用utf8数据发送电子邮件，您可能还需要将电子邮件转换为utf8发送。

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

小开

使用HeidiSQL。它是免费的，而且是一个非常好的数据库工具。

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

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

  • tick更改默认排序规则:utf8mb4_general_ci
  • tick转换为字符集:utf8

执行

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

工作就像一个魅力:)

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

从latin到utf8的真正陷阱是确保pdo与utf8字符集连接。如果不是，你会得到垃圾数据插入到utf8表和问号在你的网页上到处都是，让你认为表数据不是utf8…

小开

在继续之前，请确保您:已完成完全数据库备份!

步骤1:数据库级别更改

  • 标识数据库的排序规则和字符集

    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

  • 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)

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;

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

小开

对于有大量表的数据库，你可以使用一个简单的php脚本来更新数据库和所有表的字符集，使用以下方法:

$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>";
}
小开

Alter table table_name charset = 'utf8';

这是一个简单的查询，我能够使用我的情况下，你可以改变table_name根据您的需求(s)。

小开

受@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
小开 
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
小开

要更改数据库本身的字符集编码为UTF-8，在mysql>提示符处键入以下命令。将DBNAME替换为数据库名称。

ALTER DATABASE DBNAME CHARACTER SET utf8 COLLATE utf8_general_ci;
小开

你也可以使用数据库工具Navicat，这样做更容易。

  • 湿婆。

右键单击数据库&选择DB属性&在下拉菜单中更改您想要的内容

enter image description here

小开

最安全的方法是先将列修改为二进制类型，然后使用所需的字符集将其修改回二进制类型。

每种列类型都有其各自的二进制类型，如下所示:

  1. Char =>二进制
  2. Text => blob
  3. Tinytext => tinyblob
  4. Mediumtext => mediumblob
  5. Longtext => longblob
  6. Varchar => varbinary

如:

ALTER TABLE [TABLE_SCHEMA].[TABLE_NAME] MODIFY [COLUMN_NAME] VARBINARY;


ALTER TABLE [TABLE_SCHEMA].[TABLE_NAME] MODIFY [COLUMN_NAME] VARCHAR(140) CHARACTER SET utf8mb4;

我尝试了几个拉丁表，它保留了所有的变音符。

你可以为所有列提取这个查询:

SELECT
CONCAT('ALTER TABLE ', TABLE_SCHEMA,'.', TABLE_NAME,' MODIFY ', COLUMN_NAME,' VARBINARY;'),
CONCAT('ALTER TABLE ', TABLE_SCHEMA,'.', TABLE_NAME,' MODIFY ', COLUMN_NAME,' ', COLUMN_TYPE,' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;')
FROM information_schema.columns
WHERE TABLE_SCHEMA IN ('[TABLE_SCHEMA]')
AND COLUMN_TYPE LIKE 'varchar%'
AND (COLLATION_NAME IS NOT NULL AND COLLATION_NAME NOT LIKE 'utf%');

在所有列上执行此操作后，再对所有表执行此操作:

ALTER TABLE [TABLE_SCHEMA].[TABLE_NAME] CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

要为你所有的表生成这个查询，使用下面的查询:

SELECT
CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_COLLATION NOT LIKE 'utf8%'
and TABLE_SCHEMA in ('[TABLE_SCHEMA]');

现在你修改了所有的列和表，在数据库上做同样的事情:

ALTER DATABASE [DATA_BASE_NAME] CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci;
小开 
DELIMITER $$


CREATE PROCEDURE `databasename`.`update_char_set`()


BEGIN
DECLARE done INT DEFAULT 0;
DECLARE t_sql VARCHAR(256);
DECLARE tableName VARCHAR(128);
DECLARE lists CURSOR FOR SELECT table_name FROM `information_schema`.`TABLES` WHERE table_schema = 'databasename';
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN lists;
FETCH lists INTO tableName;
REPEAT
SET @t_sql = CONCAT('ALTER TABLE ', tableName, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci');
PREPARE stmt FROM @t_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
FETCH lists INTO tableName;
UNTIL done END REPEAT;
CLOSE lists;
END$$


DELIMITER ;


CALL databasename.update_char_set();
小开

命令行解决方案和排除视图

我只是完成@Jasny的回答为其他人像@Brian和我谁有视图在我们的数据库。

如果你有这样的错误:

ERROR 1347 (HY000) at line 17: 'dbname.table_name' is not of type 'BASE TABLE'
这是因为你可能有一些观点，你需要排除它们。 但是当试图排除它们时，MySQL返回2列而不是1列 
SHOW FULL TABLES WHERE Table_Type = 'BASE TABLE';
-- table_name1  BASE TABLE
-- table_name2  BASE TABLE

因此，我们必须用awk改编Jasny的命令，只提取包含表名的第一列。

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

简单复制/粘贴的一行程序

DB="dbname"; ( echo 'ALTER DATABASE `'"$DB"'` CHARACTER SET utf8 COLLATE utf8_general_ci;'; mysql "$DB" -e "SHOW FULL TABLES WHERE Table_Type = 'BASE TABLE'" --batch --skip-column-names | awk '{print $1 }' | xargs -I{} echo 'ALTER TABLE `'{}'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;' ) | mysql "$DB"
小开

要将字符集编码更改为UTF-8，请在PHPMyAdmin中执行简单步骤

  1. 选择您的数据库SS

  2. 执行操作 < img src = " https://i.stack.imgur.com/kQVUm.png " alt = "学生" > < / p > < /李>

  3. 在操作选项卡中，在底部排序规则下拉菜单中，选择您想要的编码，即(utf8_general_ci)，并勾选复选框(1)更改所有表排序规则，(2)更改所有表列排序规则。然后点击Go。

SS

小开

utf8到utf8mb4:

1.显示所有数据库默认字符集:

SELECT SCHEMA_NAME 'YOUR_DATABASE_NAME',
default_character_set_name 'charset',
DEFAULT_COLLATION_NAME 'collation'
FROM information_schema.SCHEMATA;

2.显示所有表的状态(字符集)，关注列的“collation”:

use YOUR_DATABASE_NAME;
SHOW TABLE STATUS ;
< p > 3。生成转换sql: 转换数据库&所有表utf8mb4,utf8mb4_unicode_ci

USE information_schema;
SELECT CONCAT("ALTER DATABASE `",table_schema,"` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;") AS _sql
FROM `TABLES` WHERE table_schema LIKE "YOUR_DATABASE_NAME" AND TABLE_TYPE='BASE TABLE' GROUP BY table_schema UNION
SELECT CONCAT("ALTER TABLE `",table_schema,"`.`",table_name,"` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") AS _sql
FROM `TABLES` WHERE table_schema LIKE "YOUR_DATABASE_NAME" AND TABLE_TYPE='BASE TABLE' GROUP BY table_schema, TABLE_NAME
/*include all columns, commonly don't need this.*/
/*
UNION
SELECT CONCAT("ALTER TABLE `",`COLUMNS`.table_schema,"`.`",`COLUMNS`.table_name, "` CHANGE `",column_name,"` `",column_name,"` ",data_type,"(",character_maximum_length,") CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci",IF(is_nullable="YES"," NULL"," NOT NULL"),";") AS _sql
FROM `COLUMNS` INNER JOIN `TABLES` ON `TABLES`.table_name = `COLUMNS`.table_name WHERE `COLUMNS`.table_schema like "YOUR_DATABASE_NAME" and data_type in ('varchar','char') AND TABLE_TYPE='BASE TABLE' UNION
SELECT CONCAT("ALTER TABLE `",`COLUMNS`.table_schema,"`.`",`COLUMNS`.table_name, "` CHANGE `",column_name,"` `",column_name,"` ",data_type," CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci",IF(is_nullable="YES"," NULL"," NOT NULL"),";") AS _sql
FROM `COLUMNS` INNER JOIN `TABLES` ON `TABLES`.table_name = `COLUMNS`.table_name WHERE `COLUMNS`.table_schema like "YOUR_DATABASE_NAME" and data_type in ('text','tinytext','mediumtext','longtext') AND TABLE_TYPE='BASE TABLE';
*/

4.运行生成的SQL语句。

5.刷新数据库。

6.检查:

SHOW TABLE STATUS ;

提交答案