如何将整个MySQL数据库字符集转换为UTF-8和排序为UTF-8?
当前回答
如果你不能让你的表转换,或者你的表总是设置为一些非utf8字符集,但你想要utf8,你最好的办法可能是擦掉它,重新开始,并显式地指定:
create database database_name character set utf8;
其他回答
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 ;
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 ;
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的回答。
如果你有这样的错误:
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"
使用HeidiSQL。它是免费的,而且是一个非常好的数据库工具。
从工具菜单,进入批量表编辑器
选择完整的数据库或选择表进行转换,
tick更改默认排序规则:utf8mb4_general_ci tick转换为字符集:utf8
执行
这将在短短几秒钟内将完整的数据库从拉丁语转换为utf8。
工作就像一个魅力:)
HeidiSQL默认连接为utf8,因此任何特殊字符现在都应该被视为字符(æ ø å),而不是在检查表数据时被编码。
从latin到utf8的真正陷阱是确保pdo与utf8字符集连接。如果不是,你会得到垃圾数据插入到utf8表和问号在你的网页上到处都是,让你认为表数据不是utf8…
要将字符集编码更改为UTF-8,请在PHPMyAdmin中执行简单步骤
选择数据库 进入操作区 在操作选项卡中,在底部排序规则下拉菜单中,选择您想要的编码,即(utf8_general_ci),并勾选复选框(1)更改所有表排序规则,(2)更改所有表列排序规则。然后点击Go。