sql-server - Mssql Server 到 MySql 之间的迁移视图

Question

我将mssql之间的数据库表迁移到mysql。现在我想在 mssql 到 mysql 数据库之间迁移一个视图，我尝试手动执行此操作。对于 mysql，我使用工作台。mssql 数据库中的视图如下：

    SELECT  
    u.Name, 
    u.Rights, 
    u.FirstName, 
    u.LastName, 
    u.IsDeleted, 
    activities.SiteName, 
    activities.RegionID, 
    activities.RegionName, 
    activities.ActivityID, 
    activities.DateTime, 
    activities.UserID, 
    activities.IPAddress, 
    activities.ClientID, 
    activities.SiteID, 
    activities.VariableID, 
    activities.ActivityActionID, 
    activities.Description, 
    activities.LogActionDescription, 
    activities.DeviceName, 
    activities.VariableName
FROM    Security.[User] AS u 
INNER JOIN
(SELECT a.SiteName, 
        a.RegionID, 
        a.RegionName, 
        a.ActivityID, 
        a.DateTime, 
        a.UserID, 
        a.IPAddress, 
        a.ClientID, 
        a.SiteID, 
        a.VariableID, 
        a.ActivityActionID, 
        a.Description, 
        a.LogActionDescription, 
        v.DeviceName, 
        v.VariableName
FROM  dbo.v_ReportVariables AS v 
RIGHT OUTER JOIN
    (SELECT s.SiteName, 
            s.RegionID, s.RegionName, [Log].Activity.ActivityID, 
            [Log].Activity.DateTime, [Log].Activity.UserID, [Log].Activity.IPAddress, 
            [Log].Activity.ClientID, [Log].Activity.SiteID, [Log].Activity.VariableID, 
            [Log].Activity.ActivityActionID, [Log].Activity.Description, 
            [Log].ActivityAction.Description AS LogActionDescription
        FROM  dbo.v_Sites AS s 
        RIGHT OUTER JOIN
            [Log].Activity ON s.SiteID = [Log].Activity.SiteID 
        INNER JOIN [Log].ActivityAction ON [Log].Activity.ActivityActionID = [Log].ActivityAction.ActivityActionID) AS a 
        ON v.VariableID = a.VariableID
    ) AS activities ON u.UserID = activities.UserID

当我在 mysql 中迁移数据库表时，表的名称发生了变化。例如，当 mssql 中的名称为“Security.User”时，mysql 中的名称更改为 security_user。我尝试手动创建从 mssql 到 mysqlsql 的上述视图，更改表的名称如下：

 SELECT  
    u.Name, 
    u.Rights, 
    u.FirstName, 
    u.LastName, 
    u.IsDeleted, 
    activities.SiteName, 
    activities.RegionID, 
    activities.RegionName, 
    activities.ActivityID, 
    activities.DateTime, 
    activities.UserID, 
    activities.IPAddress, 
    activities.ClientID, 
    activities.SiteID, 
    activities.VariableID, 
    activities.ActivityActionID, 
    activities.Description, 
    activities.LogActionDescription, 
    activities.DeviceName, 
    activities.VariableName
FROM    Security_User AS u 
INNER JOIN
(SELECT a.SiteName, 
        a.RegionID, 
        a.RegionName, 
        a.ActivityID, 
        a.DateTime, 
        a.UserID, 
        a.IPAddress, 
        a.ClientID, 
        a.SiteID, 
        a.VariableID, 
        a.ActivityActionID, 
        a.Description, 
        a.LogActionDescription, 
        v.DeviceName, 
        v.VariableName
FROM  dbo_v_ReportVariables AS v 
RIGHT OUTER JOIN
    (SELECT s.SiteName, 
            s.RegionID, s.RegionName, Log_Activity.ActivityID, 
            Log_Activity.DateTime, Log_Activity.UserID, Log_Activity.IPAddress, 
            Log_Activity.ClientID, Log_Activity.SiteID, Log_Activity.VariableID, 
            Log_Activity.ActivityActionID, Log_Activity.Description, 
            Log_ActivityAction.Description AS LogActionDescription
        FROM  dbo_v_Sites AS s 
        RIGHT OUTER JOIN
            Log_Activity ON s.SiteID = Log_Activity.SiteID 
        INNER JOIN Log_ActivityAction ON Log_Activity.ActivityActionID = Log_ActivityAction.ActivityActionID) AS a 
        ON v.VariableID = a.VariableID
    ) AS activities ON u.UserID = activities.UserID

我在 mysql 中创建了一个没有语法错误的视图。但是创建后的代码视图在mysql中更改为：

SELECT 
    `u`.`Name` AS `Name`,
    `u`.`Rights` AS `Rights`,
    `u`.`FirstName` AS `FirstName`,
    `u`.`LastName` AS `LastName`,
    `u`.`IsDeleted` AS `IsDeleted`,
    `activities`.`SiteName` AS `SiteName`,
    `activities`.`RegionID` AS `RegionID`,
    `activities`.`RegionName` AS `RegionName`,
    `activities`.`ActivityID` AS `ActivityID`,
    `activities`.`DateTime` AS `DateTime`,
    `activities`.`UserID` AS `UserID`,
    `activities`.`IPAddress` AS `IPAddress`,
    `activities`.`ClientID` AS `ClientID`,
    `activities`.`SiteID` AS `SiteID`,
    `activities`.`VariableID` AS `VariableID`,
    `activities`.`ActivityActionID` AS `ActivityActionID`,
    `activities`.`Description` AS `Description`,
    `activities`.`LogActionDescription` AS `LogActionDescription`,
    `activities`.`DeviceName` AS `DeviceName`,
    `activities`.`VariableName` AS `VariableName`
FROM
    (`eye`.`security_user` `u`
    JOIN (SELECT 
        `a`.`SiteName` AS `SiteName`,
            `a`.`RegionID` AS `RegionID`,
            `a`.`RegionName` AS `RegionName`,
            `a`.`ActivityID` AS `ActivityID`,
            `a`.`DateTime` AS `DateTime`,
            `a`.`UserID` AS `UserID`,
            `a`.`IPAddress` AS `IPAddress`,
            `a`.`ClientID` AS `ClientID`,
            `a`.`SiteID` AS `SiteID`,
            `a`.`VariableID` AS `VariableID`,
            `a`.`ActivityActionID` AS `ActivityActionID`,
            `a`.`Description` AS `Description`,
            `a`.`LogActionDescription` AS `LogActionDescription`,
            `v`.`DeviceName` AS `DeviceName`,
            `v`.`VariableName` AS `VariableName`
    FROM
        (((SELECT 
        `s`.`SiteName` AS `SiteName`,
            `s`.`RegionID` AS `RegionID`,
            `s`.`RegionName` AS `RegionName`,
            `eye`.`log_activity`.`ActivityID` AS `ActivityID`,
            `eye`.`log_activity`.`DateTime` AS `DateTime`,
            `eye`.`log_activity`.`UserID` AS `UserID`,
            `eye`.`log_activity`.`IPAddress` AS `IPAddress`,
            `eye`.`log_activity`.`ClientID` AS `ClientID`,
            `eye`.`log_activity`.`SiteID` AS `SiteID`,
            `eye`.`log_activity`.`VariableID` AS `VariableID`,
            `eye`.`log_activity`.`ActivityActionID` AS `ActivityActionID`,
            `eye`.`log_activity`.`Description` AS `Description`,
            `eye`.`log_activityaction`.`Description` AS `LogActionDescription`
    FROM
        ((`eye`.`log_activity`
    LEFT JOIN `eye`.`dbo_v_sites` `s` ON ((`s`.`SiteID` = `eye`.`log_activity`.`SiteID`)))
    JOIN `eye`.`log_activityaction` ON ((`eye`.`log_activity`.`ActivityActionID` = `eye`.`log_activityaction`.`ActivityActionID`))))) `a`
    LEFT JOIN `eye`.`dbo_v_reportvariables` `v` ON ((`v`.`VariableID` = `a`.`VariableID`)))) `activities` ON ((`u`.`UserID` = `activities`.`UserID`)))

它与我创建的代码不同，我不知道它是否具有相同的结果。我可以帮助我一些指导如何手动迁移视图吗？

标签： sql-servermysql-workbench

Answer 1

没有理由 99% 的时间你不能只逐字复制视图，如果需要的话更改表名。任何语法错误都应该足够明显。

我会从那里开始，因为在您的第三个示例中发生的重构不是我想要经历的。所有引用字符都让我的眼睛流泪。并且从右到左连接的交换也需要仔细理解。

顺便说一句，我还要说，在我（比我想记住的时间更长）的职业生涯中，我从来没有写过正确的加入，因为我发现另一种方式更清楚，但这可能只是我。即便如此，我也不希望将它留给一个自动化的重构，这使得它更难阅读，而不是更容易。

因此，请使用您的手动版本 - 两个数据库之间的 sql 几乎总是可以工作的。我相信您可以找到详细说明可能妨碍您的差异（如数据类型、转换等）的资源。

你的观点没有这些。