mysql - 如果触发器和 View 的字符集与数据库的其余部分不匹配，会有什么后果？

Question

我们最近完成了将应用程序的所有 MySQL 表、列和 .ini 设置转换为 utf8 编码的工作。但是，我们发现在此更改之前创建的 View 和触发器 仍然具有对 latin1 字符集的引用——即以下查询返回记录:

SELECT * FROM information_schema.triggers
WHERE trigger_schema=SCHEMA() 
AND    (collation_connection != 'utf8_general_ci' OR character_set_client != 'utf8')
;
SELECT * FROM information_schema.views
WHERE table_schema=SCHEMA()
AND   (collation_connection != 'utf8_general_ci' OR character_set_client != 'utf8')
;

我需要担心这个吗？

information_schema.triggers 上的 MySQL 文档和 information_schema.views只说“创建触发器时 character_set_client 系统变量的 session 值。”如果这就是存储的所有值(value)，那么是否有任何理由尝试修复它们？ (这听起来并不重要。)但另一方面，我认为数据库开发人员出于某些原因选择将其存储在 information_schema 表中。

产品已经使用 utf8 一段时间了， View 和触发器仍然引用 latin1，我们没有发现任何问题(尽管我们没有非常大的非英语用户群)。我用不同的测试字符串做了一些测试，没有发现任何字符损坏。

Answer 1

请参阅下面引用中的粗体 文本。如果您在触发器/ View 中使用了非 ASCII 字符，例如为了与您的 UTF-8 列之一进行比较，您最好重新创建它们。如果不是，那也没关系，因为这些变量用于设置您的对象的上下文，以便稍后使用/重新创建。

引自Changes in MySQL 5.1.21 (2007-08-16)

Bugs Fixed

Incompatible Change: Several issues were identified for stored programs (stored procedures and functions, triggers, and events) and views containing non-ASCII symbols. These issues involved conversion errors due to incomplete character set information when translating these objects to and from stored format, such as:

• Parsing the original object definition so that it can be stored.

• Compiling the stored definition into executable form when the object is invoked.

• Retrieval of object definitions from INFORMATION_SCHEMA tables.

• Displaying the object definition in SHOW statements. This issue also affected mysqldump, which uses SHOW.

The fix for the problems is to store character set information from the object creation context so that this information is available when the object needs to be used later. The context includes the client character set, the connection character set and collation, and the collation of the database with which the object is associated.

As a result of the patch, several tables have new columns:

In the mysql database, the proc and event tables now have these columns: character_set_client, collation_connection, db_collation, body_utf8.

In INFORMATION_SCHEMA, the VIEWS table now has these columns: CHARACTER_SET_CLIENT, COLLATION_CONNECTION. The ROUTINES, TRIGGERS, and EVENTS tables now have these columns: CHARACTER_SET_CLIENT, COLLATION_CONNECTION, DATABASE_COLLATION.

These columns store the session values of the character_set_client and collation_connection system variables, and the collation of the database with which the object is associated. The values are those in effect at object creation time. (The saved database collation is not the value of the collation_database system variable, which applies to the default database; the database that contains the object is not necessarily the default database.)