mysql - ALTER VIEW 仅为一个 View 插入数据库前缀

Question

有个奇怪的问题。

我在我的应用程序中使用了多个数据库 View 。所有的 View 都只是引用它们的表而不用数据库名称作为前缀，例如

SELECT  `foo`, `bar`
FROM    `tablename`

不过，我有一个看法是:

SELECT  `foo`, `bar`
FROM    `database_name`.`table_name`

这在执行模式比较时会引起头痛。

我已经使用 ALTER VIEW 多次编辑 View ，但每次它都会重新插入数据库名称作为前缀。我也从头开始删除并重新创建它，但结果相同。

不过它只针对这一个 View 执行此操作。

我使用的是 MySQL Workbench 6.3.10。这是完整的声明:

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `reporting_base` AS
    SELECT 
        `b`.`creditor_id` AS `creditor_id`,
        `c`.`creditor_name` AS `creditor_name`,
        `c`.`group_name` AS `group_name`,
        `rd`.`base_date` AS `base_date`,
        `rd`.`year` AS `year`,
        `rd`.`period` AS `period`,
        `rd`.`week` AS `week`,
        `b`.`id` AS `branch_id`,
        `b`.`branch_name` AS `branch_name`,
        `b`.`area` AS `area`,
        `b`.`manager` AS `manager`,
        `s`.`id` AS `staff_id`,
        `s`.`type` AS `staff_type`,
        `s`.`full_name` AS `staff_name`
    FROM
        (((`database_name`.`reporting_dates` `rd`
        JOIN `database_name`.`branches` `b`)
        JOIN `database_name`.`creditors` `c` ON (((`c`.`id` = `b`.`creditor_id`)
            AND (`c`.`include_in_reports` = 1))))
        LEFT JOIN (SELECT 
            0 AS `id`,
                `database_name`.`creditors`.`id` AS `creditor_id`,
                'n/a' AS `type`,
                'n/a' AS `full_name`
        FROM
            `database_name`.`creditors` UNION SELECT 
            `database_name`.`staff`.`id` AS `id`,
                `database_name`.`staff`.`creditor_id` AS `creditor_id`,
                `database_name`.`staff`.`type` AS `type`,
                `database_name`.`staff`.`full_name` AS `full_name`
        FROM
            `database_name`.`staff`) `s` ON ((`s`.`creditor_id` = `c`.`id`)))
    WHERE
        (`rd`.`base_date` <= UTC_DATE())

Answer 1

如果整个 create view 语句有歧义...它可能会添加数据库名称。

...from (((''table1' 'rd' 
join 'dbname'.'table2' 'b'...

本身就足以将“dbname”触发到 View 代码中。