10个常见的SQL错误。他对这篇文章受到如此广泛的关注感到非常惊讶。这种受欢迎程度说明了一些事情：SQL对于专业Java世界的重要性。忘记一些基本的SQL内容是多么普遍。jOOQ或MyBatis等以SQL为中心的库如何通过采用SQL来响应市场需求。一个有趣的事实是，用户甚至提到了他在slick的邮件列表上写的博客文章。Slick是Scala中一个非以SQL为中心的数据库访问库。与LINQ（和LINQ-TO-SQL）一样，它侧重于语言集成，而不是SQL代码生成。无论如何，Eder列出的一些错误还不够，这里有10个Java开发人员在编写SQL时经常犯的错误。1.不要使用PreparedStatements有趣的是，在JDBC多年之后，这种错误或误解仍然出现在博客、论坛和邮件列表中，尽管它是关于记忆和理解上非常简单的事情。一些开发人员似乎出于以下原因避免准备语句：不了解准备语句。认为准备好的语句较慢。认为编写准备好的语句需要更多的努力。首先，需要打破上述误区。在96%的情况下，编写准备好的语句比编写静态语句要好。为什么？这样做的原因很简单：内联绑定值时可以省略由错误的字符串连接引起的语法错误。内联绑定值时，可以忽略由于字符串连接错误导致的SQL注入漏洞。当时间戳、二进制数据等更复杂的数据类型被内联时，可以避免边缘情况。可以将打开的准备语句保持一段时间，用新的绑定值重用它们而不是立即关闭它们（例如在postgres中很有用）。自适应游标共享（Oracle语言）可用于更复杂的数据库。这有助于防止为每组新的绑定值硬解析SQL语句。重要的是要注意，在极少数情况下，确实需要内联绑定值，以便让数据库的基于成本的优化器知道查询实际会影响哪些数据类型。通常，这会导致“常量”谓词，例如：deleted=1status=42但它不应导致“可变”谓词，例如：first_namelike"jon%"amount>19.95请注意，现代数据库实现绑定变量peep.因此，默认情况下，绑定值也可以用于所有查询参数。此外，在编写嵌入式JPQL或嵌入式SQL时，JPACriteriaQuery或jOOQ等高级API将帮助您非常轻松和透明地生成准备好的语句和绑定值。解决方法：默认情况下，始终使用准备好的语句而不是静态语句，并且从不将绑定值内联到SQL中。2.Returntoomanycolumns这种错误很常见，可能会对数据库的执行计划和Java应用程序造成很坏的影响。先看第二种影响：(1)对Java应用的不良影响如果选择*（星号）或者“默认”的50列集合（在各种数据访问对象之间复用），需要移动大量的数据Transfer从数据库到JDBC结果集。即使没有从结果集中读取数据，它也已通过网络传输并由JDBC驱动程序加载到内存中。如果你知道只需要2到3个这样的列，这是相当浪费IO和内存的。这是显而易见的，但也要小心。(2)对数据库执行计划的不利影响这些影响实际上可能比对Java应用程序的影响要严重得多。在计算查询的最佳执行计划时，复杂的数据库会执行大量的SQL转换。查询的某些部分可能会被转换掉，因为知道它们对投影（选择子句）或过滤谓词没有影响。考虑一个将连接两个视图的复杂选择：select*fromcustomer_viewcjoinorder_viewoonc.cust_id=o.cust_idonc.cust_id=o.cust_id连接到上面连接表引用的每个视图可能再次连接来自几十个每个的数据表，如客户地址、订单历史、订单结算等。考虑到select*投影，数据库别无选择，只能完全加载所有这些连接表，而实际上，唯一感兴趣的是：selectc.first_name,c.last_name,o.amountfromcustomer_viewcjoinorder_viewoonc。cust_id=o.cust_id一个好的数据库会以一种去除大部分“隐藏”连接的方式转换SQL，这将显着减少数据库中的IO和内存消耗。解决方法：永远不要执行select*。不要为不同的查询重复使用相同的投影。始终尝试减少对真正需要的数据的预测。请注意，这很难通过对象关系映射(ORM)实现。3.认为join是一个select子句这不是一个对性能或SQL正确性有很大影响的错误，但是，SQL开发人员应该意识到join子句本身不是select语句的一部分这一事实。SQL标准1992像这样定义表引用：6.3<表引用><表引用>::=<表名>[[as]<相关名称>[<左括号><派生列列表><右括号>]]|<派生表>[as]<相关名称>[<左括号><派生列列表><右括号>]|from子句和joinedtables可以使用这样的表引用：7.4::=from[{}...]7.5<连接表>::=<交叉连接>|<合格加入>|::=crossjoin::=[natural][]join连接[]关系数据库主要以表为中心。许多操作以一种或另一种方式对物理表、连接表或派生表执行。为了高效地编写SQL，重要的是要了解select..From子句需要以逗号分隔的表引用列表，无论它们以何种形式提供。根据表引用的复杂性，一些数据库也接受其他语句中的复杂表引用，例如插入、更新、删除和合并。变通方法：始终将from子句中的表引用作为一个整体来考虑。如果您编写连接子句，请将连接子句视为复杂表引用的一部分：selectc.first_name,c.last_name,o.amountfromcustomer_viewcjoinorder_viewoonc.cust_id=o.cust_id4。使用pre-ANSIJOINSYNTAX现在您已经阐明了表引用的工作方式，请务必避免使用pre-ANSIJOIN语法对于执行计划，如果您在JOIN..ON子句或WHERE子句中指定连接谓词通常没有什么区别.但是从可读性和维护的角度来看，为过滤和连接谓词使用where子句是一个主要障碍。考虑这个简单的例子：selectc.first_name,c.last_name,o.amountfromcustomer_viewc,order_viewwhereo.amount>100andc.cust_id=o.cust_idandc.language='en'可以找到连接谓词吗？如果加几十张表呢？当您为外部连接应用专有语法（例如Oracle的(+)语法）时，情况会变得更糟。解决方法：始终使用ansi-join语法。切勿将连接谓词放在where子句中。使用pre-ansi连接语法绝对没有任何好处。5.忘记对like谓词的输入进行转义SQLStandard1992对like谓词的规定如下：8.5::=[not]like[escape]当在SQL查询中使用用户输入时，几乎总是应该使用转义关键字。虽然百分号(%)可能很少被视为数据的一部分，但下划线(_)很可能是：select*fromtwheret.xlike'some!_prefix%'escape'!'解决方法：使用like谓词时，始终考虑适当的转义。6.认为not(in(x,y))是in(x,y)的布尔逆是很微妙的，但是对于null很重要！再回顾一下in(x,y)的真正含义：ain(x,y)等同于a=any(x,y)等同于a=x或a=y同时，not(ain(x,y))的真正意思是：not(ain(x,y))等同于anotin(x,y)等同于a!=any(x,y)等同于a!=x和a!=y这看起来像是前一个谓词的布尔逆运算，但它不是！如果x或y为null，则not-in谓词将导致未知，而in谓词仍可能返回布尔值。换句话说，虽然in(x,y)产生true或false，但not(ain(x,y))仍可能产生unknown而不是false或true。请注意，如果in谓词的右侧是子查询，这也是正确的。不相信？看看这个sql小提琴。它表明以下查询未产生任何结果：select1where1in(null)unionallselect2wherenot(1in(null))解决方法：对于可为空的列，请注意notin谓词！7.Thinknot(aisnull)和aisnotnull一样人们还记得SQL在处理null值的时候实现了三值逻辑。这就是为什么您可以使用null谓词来检查null。但即使是null谓词也是微妙的。请注意，以下两个谓词仅对度数为1的行值表达式等价：not(aisnull)isnotthesameasaisnotnull如果a是度数大于1的行值表达式，则为真value表翻译为：aisnullyieldstrueonlyifallvaluesinainallvaluesarenullnot(aisnull)yieldsfalseonlyifallvaluesinainaisnotnullyieldstruewhenallvaluesinallvaluesarenotnullandnot(aisnotnull)仅当a中的所有值都不为null时才产生false解决方案：使用行值表达式时，请注意null谓词，它可能无法按预期工作。8.在支持行值表达式的地方不使用行值表达式行值表达式是一个很好的SQL特性。SQL是一种以表为中心的语言，而表也是以行为中心的。行值表达式通过创建可以与具有相同程度和行类型的其他行进行比较的本地特殊行，使描述复杂谓词变得更加容易。一个简单的示例是同时查询客户的名字和姓氏。selectc.addressfromcustomerc,where(c.first_name,c.last_name)=(?,?)Where(c.first_name,c.last_name)=(?,?)你可以看到这个语法比等价的要好语法（谓词左边的每一列与右边对应的列进行比较）稍微更简洁。如果许多独立谓词与and组合在一起，则尤其如此。使用行值表达式允许将相关谓词组合成一个谓词。这非常适用于复合外键上的连接表达式：selectc.first_name,c.last_name,a.streetfromcustomercjoinaddressaon(c.id,c.tenant_id)=(a.id,a.tenant_id)不幸的是，并非所有数据库都以相同的方式支持行值表达式。但是SQL标准早在1992年就定义了它们，如果使用它们，像Oracle或Postgres这样的复杂数据库可以使用它们来计算更好的执行计划。解决方法：尽可能使用行值表达式。它们将使SQL更干净，甚至可能更快。9.没有定义足够的约束。要引用TomKyte，请使用索引和Luke。元数据中不能有足够的约束。首先，约束有助于防止数据损坏，这已经非常有用了。但更重要的是，约束将帮助数据库执行SQL转换，因为数据库可以决定：一些值是等价的一些子句是多余的一些子句是“无效的”（即它们不返回任何值）一些开发人员可能认为约束很慢。相比之下，除非你正在插入非常大量的数据，在这种情况下你可以禁用大型操作的约束，或者使用一个没有约束的临时“加载表”，将数据离线传输到没有约束的实际表中。变通方法：定义尽可能多的约束。它们将帮助数据库在查询时更好地执行。10.认为50毫秒是快速查询执行NoSQL炒作仍在继续，许多企业仍然认为他们是Twitter或Facebook，迫切需要更快、更具可扩展性的解决方案，以避开ACID和关系模型以向外扩展。有些可能会成功（例如Twitter或Facebook）。对于那些被迫或选择坚持使用经过验证的关系数据库的人，不要误以为现代数据库很慢，事实上它们非常快，并且在不到一毫秒的时间内解析20kb的查询文本并计算执行计划2000行。它们可能很慢，因为应用程序滥用了流行的对象关系映射(ORM)，或者因为此ORM无法为复杂的查询逻辑生成快速SQL。在这种情况下，人们可能希望选择一种更以SQL为中心的API，例如JDBC、jOOQ或MyBatis，它们将重新获得对SQL的控制。所以，不要认为50毫秒的查询执行速度很快甚至可以接受。但事实上并非如此。如果您在开发过程中获得了这些速度，请务必研究执行计划。这些数据在生产环境中可能会爆炸，因为生产环境中有更复杂的场景和数据。结论SQL非常有趣，但在很多方面也非常微妙。正如Eder之前关于10个常见错误的博文所展示的那样，这并不容易做到。但是SQL是可以掌握的。数据是您最宝贵的资产。所以需要尊重数据，写出更好的SQL。原标题：10MoreCommonMistakesJavaDevelopersMakewhenWritingSQL，作者：LukasEder
