blocks|key|2048381|text|SQL查询是代码，而不是配置。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2048382|在敏捷环境中，在SQL+Server等动态编译存储过程的系统上使用存储过程非常有用，因为您通常可以通过修补存储过程来修复或解决应用程序问题。|2048383|但它仍然是代码。|2048384|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

SQL queries are code, not configuration.

It is useful in an agile environment to use stored procedures on a system like SQL Server where they are dynamically compiled, as you can often fix or workaround an application problem by patching the stored proc.

But it is still code.

blocks|key|2051445|text|另一个区别(除了代码和配置之外)是连接字符串可能在代码库中使用，而SQL查询更有可能耦合到特定的类或方法。因此，如果您订阅了“不要重复自己”，您肯定会从集中连接字符串中获益，但将SQL字符串移动到不同的位置可能不会获得任何好处。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2051446|也就是说，一些最优雅的解决方案包括通过使用Hibernate或其他ORM完全避免SQL字符串，但这是另一个主题。|2051447|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Another distinction (aside from code vs. configuration) is that a connection string is probably used all over your codebase whereas a SQL query is more likely to be coupled to a specific class or method. Therefore, if you subscribe to "Don't Repeat Yourself", you'd definitely benefit from centralizing the connection string but would likely gain nothing from moving the SQL string to a different location.

That being said, some of the most elegant solutions involve avoiding SQL strings entirely by using Hibernate or another ORM, but that's a different topic.

blocks|key|5063772|text|编译代码中的"SELECT+value1，value2+FROM+TABLE“是所谓Object-relational+impedance+mismatch的本质。自从LINQ发布以来，内联SQL在绝大多数情况下都不再被接受……|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|5063773|entityMap|0|LINK|mutability|MUTABLE|url|http://en.wikipedia.org/wiki/Object-relational_impedance_mismatch^0|16|10|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

"SELECT value1,value2 FROM TABLE" in compiled code was the essence of so called <a href="http://en.wikipedia.org/wiki/Object-relational_impedance_mismatch" rel="nofollow">Object-relational impedance mismatch</a>. Since LINQ was released inline SQL is no longer acceptable in vast majority of cases...

blocks|key|5063813|text|我认为在数据库查询和围绕它的代码之间存在大量的耦合。例如，查询可能从数据库中获取特定用户的名字和姓氏，然后创建一个包含名字和姓氏的XML文件。|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|5063814|如果你把数据库查询放在其他地方(例如配置文件)，你可能看起来增加了可配置性和灵活性。但是，如果您想实际更改它，比如向XML文件添加一个名为"age“的字段，则不能只更改查询，还需要更改编写XML文件的代码。因此，实际上，您已经将必须更改一段代码的问题替换为必须更改两件事情(代码和配置)的问题，以及它们可能不一致的危险，并且您没有获得任何额外的灵活性。|5063815|(如果看起来只需编写通用代码来获取所有数据库字段并将其放入XML文件中，请考虑这样一种情况:年龄作为出生年份存储在数据库中，您必须进行计算才能创建年龄。)|5063816|entityMap^0|4|5|E|2|N|1C|0|0|4W|0|0|25|0^^$0|@$1|2|3|4|5|6|7|L|8|@$9|M|A|N|B|C]|$9|O|A|P|B|C]|$9|Q|A|R|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|V|8|@$9|W|A|X|B|C]]|D|@]|E|$]]|$1|J|3|-4|5|6|7|Y|8|@]|D|@]|E|$]]]|K|$]]

I think there is a lot of coupling between a database query and the code that surrounds it. For example, a query might fetch first name and last name for a particular user from a database, and then create an XML file containing the first name and last name.

If you put the database query somewhere else (e.g. config file) it might seem like you've increased configurability and flexibility. However, if you want to actually change it, say add a field called "age" to the XML file, you can't just change the query alone, you need to change the code writing the XML file. So in fact you've replaced the problem of having to change one piece of code, with the problem of having to change two things (code and configuration), and the danger that they might not be consistent, and you haven't gained any extra flexibility.

(If it seems like one can just write generic code to take all database fields and put them in the XML file, consider the case where age is stored as the birth year in the database, and you have to do computation to create the age.)

blocks|key|5063840|text|SQL就是代码，只是它不同于其他代码的编程语言。如果需要更改SQL，通常还需要更改处理该SQL、数据和数据库模式的代码。SQL不应该在不同的安装之间变化。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5063841|如果添加一个列，仅更改SQL是不够的，还需要更改处理该列的代码。几乎所有对数据库模式和SQL的更改，仅仅更改SQL是不够的。|5063842|另一方面，连接字符串是配置，而不是代码。|5063843|(是的，所有这些都有例外，如果你正在创建一个通用的报告工具，你需要设计它，以便SQL可以“可配置”)|5063844|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

SQL is code, it's just not the same programming language as the rest of your code. If you need to change the SQL, you usally always need to change the code dealing with that SQL, data and database schema as well. The SQL shouldn't vary between installation. 

If you add a column, changing the SQL isn't enough, you'll need to change code handling that column as well. Pretty much any change to the database schema and SQL, just changing the SQL is not enough.

A connection string on the other hand is configuration, not code.

(And yes, there's exceptions to all of this, if you're creating a general purpose reporting tool, you'll need to engineer that so the SQL can be "configurable")

blocks|key|5063888|text|我制作了一些系统，其中所有的SQL都在存储过程中，而存储过程的名称本身甚至不是存储在程序中，而是存储在app.config中。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5063889|这意味着不仅可以修补存储过程，而且不同的客户可以使用不同的过程。|5063890|因此，提供给应用程序的数据库服务接口是系统设计的一部分。|5063891|在客户端应用程序中使用任何SQL+(包括基于表的ORM和LINQ)的一个缺点是，您几乎没有显式的安全接口。对于存储过程，只有一个GRANT+-+EXEC。对于表甚至视图，ORM需要对工具生成或存储在应用程序中的任何SQL执行SELECT、UPDATE、INSERT、DELETE操作。盘点系统正常运行和审计所需的权限要比拥有一个良好的存储过程层(在某种程度上，是只读视图)要困难得多。|5063892|因此，我认为"SELECT+value1，value2+FROM+TABLE“在客户机代码中是不可接受的，因为每个可能执行该语句的用户都需要对基表中的这两列进行SELECT操作，这很难管理，即使使用角色也是如此。|5063893|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|N|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|O|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|K|$]]

I've made systems where all the SQL is in stored procedures and the names of the stored procedures are themselves not even stored in the program, but in the app.config.

This means then not only can stored procedures be patched, but different customers can use different procedures.

The database services interface provided to the application(s) is thus part of the system design.

A drawback of having any SQL in a client application at all (and this includes table-based ORMs and LINQ) is that you have very little explicit security interface. With stored procedures, there is a single GRANT - EXEC. With tables or even views, the ORM will need SELECT, UPDATE, INSERT, DELETE for whatever SQL is generated by the tool or stored in the application. Taking an inventory of the rights needed for the system to properly run and being able to audit is much more difficult than having a good solid layer of stored procedures (and, to some extent, read-only views) which everything goes through.

So I would say that "SELECT value1, value2 FROM TABLE" is not acceptable in client code, because every user who would potentially execute that statement needs to have SELECT on those two column from a base table, and that is hard to manage, even with roles.

blocks|key|1397152|text|当我看到程序员为了避免简单的查询而使用的长度时，比如：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1397153|select+*+from+products+where+productid+in+(1,2)+and+productactive+=+1|code-block|syntax|javascript|1397154|或者：|1397155|select+*+from+products+where+not+exists+(
++++select+*+from+order_lines+where+products.id+=+order_lines.productid)|1397156|然后我总是想知道他们真正的目标是什么。因为它不可能是简单的。|1397157|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

When I see the length programmers go to just to avoid simple queries like:

<pre><code>select * from products where productid in (1,2) and productactive = 1
</code></pre>

or:

<pre><code>select * from products where not exists (
 select * from order_lines where products.id = order_lines.productid)
</code></pre>

Then I always wonder what their real goal is. Because it can't be simplicity.

blocks|key|3463724|text|看待它的一种方法是将表名和列名视为数据库的“公共”接口的一部分。这就是数据库实现物理数据独立性的方式。在某种程度上，可更新视图实现了逻辑数据独立性。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3463725|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

One way to look at it is to think of table names and column names as part of the database's "public" interface. That's how databases implement physical data independence. And updatable views implement logical data independence, to some degree.

I have heard some strong developers say that "inline SQL in and of itself is not evil". I do not understand how inline SQL is acceptable. To me its just like hard coding. Many a developer would scoff in my face for putting a connection string in the code vs a config file. So why is it that "SELECT value1,value2 FROM TABLE" is perfectly acceptable in compiled code?

Is Inline SQL hard coding?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我听一些有实力的开发人员说“内联SQL本身并不是坏事”。我不明白内联SQL是如何被接受的。对我来说，这就像是硬编码。许多开发人员会嘲笑我将连接字符串放在代码中而不是放在配置文件中。那么，为什么"SELECT value1，value2 FROM TABLE“在编译代码中是完全可以接受的呢？

问内联SQL是硬编码吗？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问内联SQL是硬编码吗？EN