PostgreSQL 标识符中的下划线或驼峰命名，当编程语言使用驼峰命名时？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11270753/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Underscores or camelCase in PostgreSQL identifiers, when the programming language uses camelCase?
提问by Zilk
This has been bothering me for a while, and I can't arrive at a solution that feels right...
这已经困扰我一段时间了，我无法找到一个感觉正确的解决方案......
Given an OO language in which the usual naming convention for object properties is camelCased, and an example object like this:
给定一种 OO 语言，其中对象属性的通常命名约定是驼峰式命名，以及一个像这样的示例对象：
{
id: 667,
firstName: "Vladimir",
lastName: "Horowitz",
canPlayPiano: true
}
How should I model this structure in a PostgreSQL table?
我应该如何在 PostgreSQL 表中建模这个结构？
There are three main options:
有三个主要选项：
- unquoted camelCase column names
- quoted camelCase column names
- unquoted (lowercase) names with underscores
- 不带引号的驼峰列名
- 引用的驼峰列名
- 带下划线的未加引号（小写）名称
They each have their drawbacks:
它们都有各自的缺点：
Unquoted identifiers automatically fold to lowercase. This means that you can create a table with a
canPlayPianocolumn, but the mixed case never reaches the database. When you inspect the table, the column will always show up ascanplaypiano- in psql, pgadmin, explain results, error messages, everything.Quoted identifiers keep their case, but once you create them like that, you will alwayshave to quote them. IOW, if you create a table with a
"canPlayPiano"column, aSELECT canPlayPiano ...will fail. This adds a lot of unnecessary noise to all SQL statements.Lowercase names with underscores are unambiguous, but they don't map well to the names that the application language is using. You will have to remember to use different names for storage (
can_play_piano) and for code (canPlayPiano). It also prevents certain types of code automation, where properties and DB columns need to be named the same.
未加引号的标识符会自动折叠为小写。这意味着您可以创建带有
canPlayPiano列的表，但混合大小写永远不会到达数据库。当您检查表时，该列将始终显示为canplaypiano- 在 psql、pgadmin、解释结果、错误消息等。带引号的标识符保留它们的大小写，但是一旦您像这样创建它们，您将始终必须引用它们。IOW，如果您创建一个带有
"canPlayPiano"列的表，aSELECT canPlayPiano ...将失败。这给所有 SQL 语句增加了很多不必要的噪音。带下划线的小写名称是明确的，但它们不能很好地映射到应用程序语言正在使用的名称。您必须记住对存储 (
can_play_piano) 和代码 (canPlayPiano)使用不同的名称。它还可以防止某些类型的代码自动化，其中属性和数据库列需要命名相同。
So I'm caught between a rock and a hard place (and a large stone; there are three options). Whatever I do, some part is going to feel awkward. For the last 10 years or so, I've been using option 3, but I keep hoping there would be a better solution.
所以我被夹在岩石和坚硬的地方（还有一块大石头；有三种选择）之间。无论我做什么，都有一部分会感到尴尬。在过去 10 年左右的时间里，我一直在使用选项 3，但我一直希望有更好的解决方案。
I'm grateful for any advice you might have.
我很感激你的任何建议。
PS: I do realize where the case folding and the need for quotes is coming from - the SQL standard, or rather PostgreSQL's adaptation of the standard. I know how it works; I'm more interested in advice about best practices than explanations about how PG handles identifiers.
PS：我确实意识到大小写折叠和引号的需要来自哪里 - SQL 标准，或者更确切地说是 PostgreSQL 对标准的改编。我知道它是如何运作的；我对最佳实践的建议比对 PG 如何处理标识符的解释更感兴趣。
采纳答案by Richard Huxton
Given that PostgreSQL uses case-insensitive identifiers with underscores, should you change all your identifiers in your application to do the same? Clearly not. So why do you think the reverse is a reasonable choice?
鉴于 PostgreSQL 使用带下划线的不区分大小写的标识符，您是否应该更改应用程序中的所有标识符以执行相同的操作？显然不是。那么为什么你认为反向是一个合理的选择呢？
The convention in PostgreSQL has come about through a mix of standards compliance and long-term experience of its users. Stick with it.
PostgreSQL 中的约定是通过标准合规性和用户长期经验的混合而产生的。坚持下去。
If translating between column-names and identifiers gets tedious, have the computer do it - they're good at things like that. I'm guessing almost all of the 9-million database abstraction libraries out there can do that. If you have a dynamic language it'll take you all of two lines of code to swap column-names to identifiers in CamelCase.
如果在列名和标识符之间进行转换变得乏味，让计算机来做 - 他们擅长这样的事情。我猜几乎所有 900 万个数据库抽象库都可以做到这一点。如果您使用的是动态语言，则需要用两行代码将列名交换为 CamelCase 中的标识符。
回答by Mirza Selimovic
If your columns in the PostgreSQLare with underscores, you can put aliases but with doule-quotes.
如果您的列PostgreSQL是 with underscores，您可以放置别名但使用双引号。
Example :
例子 ：
SELECT my_column as "myColumn" from table;
回答by Lee Brantley
I know this is late however for something that would be simple to translate on the fly, you could write a small help function that would live in your code as such:
我知道这已经很晚了，但是对于一些易于即时翻译的内容，您可以编写一个小的帮助函数，该函数将存在于您的代码中：
function FormatObjForDb(srcObj){
函数 FormatObjForDb(srcObj){
const newObj = {};
Object.keys(srcObj).forEach(key => newObj[key.toLowerCase()] = srcObj[key]);
return newObj;
}
}
export const formatObjForDb = FormatObjForDb;
导出 const formatObjForDb = FormatObjForDb;
