确保MySQL数据库完全采用UTF8的最佳方法

Modified on: Sun, 18 Aug 2019 13:20:02 +0800

在UTF8和none-UTF8字符串出现问题后,我们正在对UTF8进行标准化。我需要做的一件事是检查MySQL数据库中的所有内容是否都是UTF8?我需要检查什么?

  • 服务器默认字符集
  • 每个数据库的默认字符集
  • 每个文本列都有字符集吗?我如何检查?

我想把所有这些放在一个nagios插件中来检查一切是否都是UTF8。建议?

作者:Rory

最佳答案

罗里,
首先,您想要监视在数据库中创建的内容是正确的。虽然我们都实施了防止错误的步骤,但你不能认为错误不会蔓延。我的做法与大多数基础设施需要UTF8非常相似。

以下查询适用于检查统计信息:

SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME, COUNT(0) AS COUNT FROM information_schema.SCHEMATA GROUP BY DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME;
# to filter schema use SCHEMA_NAME in the where clause
SELECT TABLE_COLLATION, COUNT(0) AS COUNT FROM information_schema.TABLES WHERE TABLE_COLLATION IS NOT NULL GROUP BY TABLE_COLLATION;
# to filter schema use TABLE_SCHEMA in the where clause
SELECT CHARACTER_SET_NAME, COLLATION_NAME, COUNT(0) AS COUNT FROM information_schema.COLUMNS WHERE CHARACTER_SET_NAME IS NOT NULL AND COLLATION_NAME IS NOT NULL GROUP BY CHARACTER_SET_NAME, COLLATION_NAME;
# to filter schema use TABLE_SCHEMA in the where clause

如果找到任何结果,以下查询可以通过电子邮件发送给您的cron作业:

SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE DEFAULT_CHARACTER_SET_NAME NOT LIKE '%utf8%' OR DEFAULT_COLLATION_NAME NOT LIKE '%utf8%';
# to filter schema use SCHEMA_NAME in the where clause
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_COLLATION IS NOT NULL AND TABLE_COLLATION NOT LIKE '%utf8%';
# to filter schema use TABLE_SCHEMA in the where clause
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE CHARACTER_SET_NAME NOT LIKE '%utf8%' OR COLLATION_NAME NOT LIKE '%utf8%';
# to filter schema use TABLE_SCHEMA in the where clause

这些查询适用于MySQL> = 5.0。如果您需要帮助编写cron作业,请告诉我。

我的开发人员(主要是Ruby on Rails团队)在创建InnoDB表时也遇到问题。为了保持这种状态,我使用调用它的cron监视登台数据库:

SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE ENGINE = 'InnoDB';

相关问答

添加新评论