如何将数据库中的所有表转换为一个排序规则? [英] How to convert all tables in database to one collation?

问题描述

我遇到错误:

操作'='的归类(utf8_general_ci，IMPLICIT)和(utf8_unicode_ci，IMPLICIT)的非法混合

Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='"

我尝试将两个表手动更改为utf8_general_ci,IMPLICIT，但仍然出现错误.

I tried changing both tables manually to utf8_general_ci,IMPLICIT but I'm still getting the error.

有没有一种方法可以将所有表转换为utf8_general_ci,IMPLICIT并用它完成?

Is there a way to convert all tables to utf8_general_ci,IMPLICIT and be done with it?

推荐答案

您需要为每个表执行一个alter table语句.该语句将采用以下形式:

You need to execute a alter table statement for each table. The statement would follow this form:

ALTER TABLE tbl_name
[[DEFAULT] CHARACTER SET charset_name]
[COLLATE collation_name]

现在要获取数据库中的所有表，您将需要执行以下查询:

Now to get all the tables in the database you would need to execute the following query:

SELECT * 
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="YourDataBaseName"
AND TABLE_TYPE="BASE TABLE";

现在让MySQL为您编写代码:

So now let MySQL write the code for you:

SELECT CONCAT("ALTER TABLE ", TABLE_SCHEMA, '.', TABLE_NAME," COLLATE your_collation_name_here;") AS    ExecuteTheString
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="YourDatabaseName"
AND TABLE_TYPE="BASE TABLE";

您可以复制结果并执行它们.我没有测试语法，但是您应该可以弄清楚其余的语法.把它想象成一个小运动.

You can copy the results and execute them. I have not tested the syntax but you should be able to figure out the rest. Think of it as a little exercise.

希望有帮助！

这篇关于如何将数据库中的所有表转换为一个排序规则?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！