为什么 mysql 更改我的代码视图? [英] why mysql change my code view?

问题描述

我创建表:

CREATE TABLE `table1` (
  `idtable1` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`idtable1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `table2` (
  `idtable2` int(11) NOT NULL AUTO_INCREMENT,
  `idtable1` int(11) DEFAULT NULL,
  `table2col1` int(11) DEFAULT NULL,
  `table2col2` int(11) DEFAULT NULL,
  PRIMARY KEY (`idtable2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我创建了一个视图:

CREATE VIEW `view1` AS
select t2.* 
from table1 t1, table2 t2
where t1.idtable1 = t2.idtable1

编译mysql时更改我的代码:

when complie mysql change my code:

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `view1` AS
    select 
        `t2`.`idtable2` AS `idtable2`,
        `t2`.`idtable1` AS `idtable1`,
        `t2`.`table2col1` AS `table2col1`,
        `t2`.`table2col2` AS `table2col2`
    from
        (`table1` `t1`
        join `table2` `t2`)
    where
        (`t1`.`idtable1` = `t2`.`idtable1`)

有什么办法可以避免这种行为?

there any way to avoid this behavior?

这是另一个例子，没有答案:为什么 mysql 重写我的视图查询?

here is another example, no answer: Why is mysql rewriting my view queries?

mysql 增加了不必要的括号，将代码放在一行.

mysql adds unnecessary parenthesis and put the code on one line.

即使使用正确的语法 mysql 也会更改视图，添加不必要的括号.

Even when using the correct syntax mysql change the view adding unnecessary parentheses.

原文:

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `view2` AS
    select 
        `t2`.`idtable2` AS `idtable2`,
        `t2`.`idtable1` AS `idtable1`,
        `t2`.`table2col1` AS `table2col1`,
        `t2`.`table2col2` AS `table2col2`
    from
        `table1` `t1` 
         left join `table2` `t2` on  `t1`.`idtable1` = `t2`.`idtable1`

编译:

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `view2` AS
    select 
        `t2`.`idtable2` AS `idtable2`,
        `t2`.`idtable1` AS `idtable1`,
        `t2`.`table2col1` AS `table2col1`,
        `t2`.`table2col2` AS `table2col2`
    from
        (`table1` `t1`
        left join `table2` `t2` ON ((`t1`.`idtable1` = `t2`.`idtable1`)))

推荐答案

您看到的是 SQL 被编译然后反编译导致的问题.

What you're seeing is issues resulting from the SQL being compiled and then decompiled.

human readable SQL -> compiled form -> human readable SQL

别担心，都是等价的代码.如果您想要一个示例，请手动编写一些 JSON，通过 JSON 解析器运行它，然后将该数据转换回 JSON.它看起来和原来的不一样.

Don't worry, it's all equivalent code. If you want an example, write some JSON by hand, run it through a JSON parser, then turn that data back into JSON. It won't look the same as the original.

这是转换数据时的一个常见问题，称为往返".如果没有额外的工作，诸如注释、缩进和括号(或缺少它们)之类的非语义信息就会丢失.MySQL 还可以应用优化和语义转换，例如将您的 FROM/WHERE 转换为 JOIN.它还使隐式代码和默认值(例如 ALGORITHM = UNDEFINED)显式.

This is a common problem in converting data known as "round tripping". Without extra work, non-semantic information like comments, indentation, and parenthesis (or the lack of them) is lost. MySQL may also apply optimizations and semantic transformations, such as turning your FROM/WHERE into a JOIN. Its also making implicit code and defaults (such as ALGORITHM = UNDEFINED) explicit.

查看往返的结果可以帮助您发现代码中的细微错误，尤其是关于操作顺序的错误.有时可能会要求反编译器添加额外的括号以明确顺序.

Seeing the result of a round trip can help you spot subtle bugs in your code, especially about order of operations. A decompiler can sometimes be asked to add extra parenthesis to make the order obvious.

在 MySQL 中存储表和视图的原始 CREATE 是没有意义的，如果使用 ALTER，它们将变得毫无用处.但是，您可以按原样返回查询.

There's no point in MySQL storing your original CREATEs for tables and views, they become useless if ALTER is used. However, returning your queries as originally written may be possible.

这篇关于为什么 mysql 更改我的代码视图?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！