mysql - 如何使 MySQL View 的创建遵循与数据库默认值相同的字符编码？

Question

我使用的是 MySQL 5.5.37。我的数据库的默认编码是 utf8 ...

mysql> SELECT default_character_set_name FROM information_schema.SCHEMATA  WHERE schema_name = "my_db";
+----------------------------+
| default_character_set_name |
+----------------------------+
| utf8                       |
+----------------------------+
1 row in set (0.00 sec)

但是，当我使用创建 View 时

CREATE OR REPLACE VIEW report_toc_item AS
SELECT ti.*, pti.type_id PARENT_TYPE_ID
            FROM toc_item ti
            JOIN toc_item pti ON pti.id = ti.parent_id
            WHERE ti.type_id = 'sub_segment';

然后我显示该 View ，cndoing 显示为“latin1”而不是 utf8 ...

mysql> show create view report_toc_item;
+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View               | Create View                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | character_set_client | collation_connection |
+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| report_toc_item | CREATE ALGORITHM=UNDEFINED DEFINER=`mytestuser`@`localhost` SQL SECURITY DEFINER VIEW `report_toc_item` AS select `ti`.`ID` AS `ID`,`ti`.`PARENT_ID` AS `PARENT_ID`,`ti`.`TOC_ID` AS `TOC_ID`,`ti`.`TITLE` AS `TITLE`,`ti`.`DESCRIPTION` AS `DESCRIPTION`,`ti`.`TYPE_ID` AS `TYPE_ID`,`ti`.`ORDER_NUM` AS `ORDER_NUM`,`ti`.`MY_OBJECT_SEGMENT_ID` AS `MY_OBJECT_SEGMENT_ID`,`ti`.`MY_OBJECT_SEGMENT_ORDER_NUM` AS `MY_OBJECT_SEGMENT_ORDER_NUM`,`ti`.`ELEMENT_ID` AS `ELEMENT_ID`,`ti`.`UNIT_TOC_ITEM_ID` AS `UNIT_TOC_ITEM_ID`,`ti`.`SHORT_NAME` AS `SHORT_NAME`,`ti`.`THIRD_PARTY_PROMPT_ID` AS `THIRD_PARTY_PROMPT_ID`,`pti`.`TYPE_ID` AS `PARENT_TYPE_ID` from (`toc_item` `ti` join `toc_item` `pti` on((`pti`.`ID` = `ti`.`PARENT_ID`))) where ((`ti`.`TYPE_ID` = 'sub_segment') and ((`pti`.`TYPE_ID` = 'lesson') or (`pti`.`TYPE_ID` = 'activity') or (`pti`.`TYPE_ID` = 'activity_practice') or (`pti`.`TYPE_ID` = 'unit_opener'))) | latin1               | latin1_swedish_ci    |
+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

如何使我的 View 尊重数据库的默认编码？我不想将编码硬编码到 View 的创建中。

Answer 1

SHOW CREATE DATABASE mydb;  -- to see what the database's charset is
SHOW VIEW x;  -- to get the body
DROP VIEW x;
SET NAMES utf8mb4;   -- or whatever you got from the first step
CREATE VIEW ...   -- recreate the view.

是的，笨拙。