I remember when I was working with PHP several years back I could blow up my application by naming a MySQL column 'desc' or any other term that was used as an operator.
我记得几年前我使用PHP时,我可以通过命名MySQL列'desc'或任何其他用作运算符的术语来炸毁我的应用程序。
So, in general are there names I should avoid giving my table columns?
那么,一般来说有名称我应该避免给我的表列吗?
9 个解决方案
#1
As long as you surround every column name with '[' and ']', it really doesn't matter what you use. Even a space works (try it: [ ]).
只要用'['和']'包围每个列名,就无论你使用什么都没关系。即使是空间也可以工作(试试吧:[])。
Edit: If you can't use '[' and ']' in every case, check the documentation for characters that are not allowable as well as keywords that are intrinsic to the system; those would be out of bounds. Off the top of my head, the characters allowed (for SqlServer) for an identifier are: a-z, A-Z, 0-9, _, $, #.
编辑:如果在每种情况下都不能使用'['和']',请检查文档中是否包含不允许的字符以及系统固有的关键字;那些将是出界的。在我的头顶,允许(对于SqlServer)标识符的字符是:a-z,A-Z,0-9,_,$,#。
#2
in general don't start with a number, don't use spaces, don't use reserved words and don't use non alphanumeric characters
一般不要以数字开头,不要使用空格,不要使用保留字,也不要使用非字母数字字符
however if you really want to you can still do it but you need to surround it with brackets
但是,如果你真的想要,你仍然可以做,但你需要用括号括起来
this will fail
这会失败
create table 1abc (id int)
this will not fail
这不会失败
create table [1abc] (id int)
but now you need to use [] all the time, I would avoid names as the ones I mentioned above
但是现在你需要一直使用[],我会避免使用上面提到的名字
#3
Check the list of reserved keywords as indicated in other answers.
检查其他答案中指示的保留关键字列表。
Also avoid using the "quoting" using quotes or square brackets for the sake of having a space or other special character in the object name. The reason is that when quoted the object name becomes case sensitive in some database engines (not sure about MSSQL though)
另外,为了在对象名称中包含空格或其他特殊字符,请避免使用引号或方括号使用“引号”。原因是当引用时,对象名称在某些数据库引擎中变为区分大小写(虽然不确定MSSQL)
Some teams use the prefix for database objects (tables, views, columns) like T_PERSON, V_PERSON, C_NAME etc. I personally do not like this convention, but it does help avoiding keyword issues.
有些团队使用数据库对象(表,视图,列)的前缀,如T_PERSON,V_PERSON,C_NAME等。我个人不喜欢这种约定,但它确实有助于避免关键字问题。
#4
You should avoid any reserved SQL keywords (ex. SELECT) and from a best practices should avoid spaces.
您应该避免使用任何保留的SQL关键字(例如SELECT),并且从最佳实践中应避免使用空格。
#5
Yes, and no.
是的,不。
- Yes, because it's annoying and confusing to have names that match keywords, and that you have to escape in funny ways (when you're not consistently escaping)
- and No, because it's possible to have any sequence of characters as an identifier, if you escape it properly :)
是的,因为拥有与关键字匹配的名称令人烦恼并且令人困惑,并且你必须以有趣的方式逃避(当你不能一直逃脱时)
和不,因为如果你正确地逃避它,可以将任何字符序列作为标识符:)
Use [square brackets] or "double quotes" to escape multi-word identifiers or keywords, or even names that have backslashes or any other slightly odd character, if you must.
如果必须,使用[方括号]或“双引号”来转义多字标识符或关键字,甚至是具有反斜杠或任何其他略微奇怪字符的名称。
#6
Strictly speaking, there's nothing you can't name your columns. However, it will make your life easier if you avoid names with spaces, SQL reserved words, and reserved words in the language you're programming in.
严格地说,没有什么你不能命名你的列。但是,如果您避免使用带有空格,SQL保留字和您正在编程的语言中的保留字的名称,它将使您的生活更轻松。
#7
You can use pretty much anything as long as you surround it with square brackets:
只要用方括号括起来,你几乎可以使用任何东西:
SELECT [value], [select], [insert] FROM SomeTable
I however like to avoid doing this, partly because typing square brackets everywhere is anoying and partyly because I dont generally find that column names like 'value' particularly descriptive! :-)
然而,我喜欢避免这样做,部分原因是因为在任何地方键入方括号都很烦人且因为我通常不会发现像'value'这样的列名特别具有描述性! :-)
Just stay away from SQL keywords and anything which contains something other than letters and you shouldn't need to use those pesky square brackets.
只是远离SQL关键字和包含字母之外的任何东西,你不应该使用那些讨厌的方括号。
#8
You can surround a word in square brackets [] and basically use anything you'd like.
你可以在方括号[]中包围一个单词,基本上可以使用你想要的任何东西。
I prefer not to use the brackets, and in order to do so you just have to avoid reserved words.
我不想使用括号,为了做到这一点,你只需要避免使用保留字。
MS SQL Server 2008 has these reserved words
MS SQL Server 2008具有这些保留字
#9
Beware of using square brackets on updates, I had a problem using the following query:
谨防在更新时使用方括号,使用以下查询时遇到问题:
UPDATE logs SET locked=1 WHERE [id] IN (SELECT [id] FROM ids)
UPDATE日志SET locked = 1 WHERE [id] IN(SELECT [id] FROM ids)
This caused all records to be updated, however, this appears to work fine:
这导致所有记录都被更新,但是,这似乎工作正常:
UPDATE logs SET locked=1 WHERE id IN (SELECT [id] FROM ids)
UPDATE日志SET locked = 1 WHERE id IN(SELECT [id] FROM ids)
Note that this problem appears specific to updates, as the following returns only the rows expected (not all rows):
请注意,此问题特定于更新,因为以下仅返回预期的行(不是所有行):
SELECT * FROM logs WHERE [id] IN (SELECT [id] FROM ids)
SELECT * FROM logs WHERE [id] IN(SELECT [id] FROM ids)
This was using MSDE 2000 SP3 and connecting to the database using MS SQL (2000) Query Analyzer V 8.00.194
这是使用MSDE 2000 SP3并使用MS SQL(2000)查询分析器V 8.00.194连接到数据库
Very odd, possibly related to this Knowledgebase bug http://support.microsoft.com/kb/140215
非常奇怪,可能与此知识库错误http://support.microsoft.com/kb/140215有关
In the end I just removed all the unnecessary square brackets.
最后我删除了所有不必要的方括号。
#1
As long as you surround every column name with '[' and ']', it really doesn't matter what you use. Even a space works (try it: [ ]).
只要用'['和']'包围每个列名,就无论你使用什么都没关系。即使是空间也可以工作(试试吧:[])。
Edit: If you can't use '[' and ']' in every case, check the documentation for characters that are not allowable as well as keywords that are intrinsic to the system; those would be out of bounds. Off the top of my head, the characters allowed (for SqlServer) for an identifier are: a-z, A-Z, 0-9, _, $, #.
编辑:如果在每种情况下都不能使用'['和']',请检查文档中是否包含不允许的字符以及系统固有的关键字;那些将是出界的。在我的头顶,允许(对于SqlServer)标识符的字符是:a-z,A-Z,0-9,_,$,#。
#2
in general don't start with a number, don't use spaces, don't use reserved words and don't use non alphanumeric characters
一般不要以数字开头,不要使用空格,不要使用保留字,也不要使用非字母数字字符
however if you really want to you can still do it but you need to surround it with brackets
但是,如果你真的想要,你仍然可以做,但你需要用括号括起来
this will fail
这会失败
create table 1abc (id int)
this will not fail
这不会失败
create table [1abc] (id int)
but now you need to use [] all the time, I would avoid names as the ones I mentioned above
但是现在你需要一直使用[],我会避免使用上面提到的名字
#3
Check the list of reserved keywords as indicated in other answers.
检查其他答案中指示的保留关键字列表。
Also avoid using the "quoting" using quotes or square brackets for the sake of having a space or other special character in the object name. The reason is that when quoted the object name becomes case sensitive in some database engines (not sure about MSSQL though)
另外,为了在对象名称中包含空格或其他特殊字符,请避免使用引号或方括号使用“引号”。原因是当引用时,对象名称在某些数据库引擎中变为区分大小写(虽然不确定MSSQL)
Some teams use the prefix for database objects (tables, views, columns) like T_PERSON, V_PERSON, C_NAME etc. I personally do not like this convention, but it does help avoiding keyword issues.
有些团队使用数据库对象(表,视图,列)的前缀,如T_PERSON,V_PERSON,C_NAME等。我个人不喜欢这种约定,但它确实有助于避免关键字问题。
#4
You should avoid any reserved SQL keywords (ex. SELECT) and from a best practices should avoid spaces.
您应该避免使用任何保留的SQL关键字(例如SELECT),并且从最佳实践中应避免使用空格。
#5
Yes, and no.
是的,不。
- Yes, because it's annoying and confusing to have names that match keywords, and that you have to escape in funny ways (when you're not consistently escaping)
- and No, because it's possible to have any sequence of characters as an identifier, if you escape it properly :)
是的,因为拥有与关键字匹配的名称令人烦恼并且令人困惑,并且你必须以有趣的方式逃避(当你不能一直逃脱时)
和不,因为如果你正确地逃避它,可以将任何字符序列作为标识符:)
Use [square brackets] or "double quotes" to escape multi-word identifiers or keywords, or even names that have backslashes or any other slightly odd character, if you must.
如果必须,使用[方括号]或“双引号”来转义多字标识符或关键字,甚至是具有反斜杠或任何其他略微奇怪字符的名称。
#6
Strictly speaking, there's nothing you can't name your columns. However, it will make your life easier if you avoid names with spaces, SQL reserved words, and reserved words in the language you're programming in.
严格地说,没有什么你不能命名你的列。但是,如果您避免使用带有空格,SQL保留字和您正在编程的语言中的保留字的名称,它将使您的生活更轻松。
#7
You can use pretty much anything as long as you surround it with square brackets:
只要用方括号括起来,你几乎可以使用任何东西:
SELECT [value], [select], [insert] FROM SomeTable
I however like to avoid doing this, partly because typing square brackets everywhere is anoying and partyly because I dont generally find that column names like 'value' particularly descriptive! :-)
然而,我喜欢避免这样做,部分原因是因为在任何地方键入方括号都很烦人且因为我通常不会发现像'value'这样的列名特别具有描述性! :-)
Just stay away from SQL keywords and anything which contains something other than letters and you shouldn't need to use those pesky square brackets.
只是远离SQL关键字和包含字母之外的任何东西,你不应该使用那些讨厌的方括号。
#8
You can surround a word in square brackets [] and basically use anything you'd like.
你可以在方括号[]中包围一个单词,基本上可以使用你想要的任何东西。
I prefer not to use the brackets, and in order to do so you just have to avoid reserved words.
我不想使用括号,为了做到这一点,你只需要避免使用保留字。
MS SQL Server 2008 has these reserved words
MS SQL Server 2008具有这些保留字
#9
Beware of using square brackets on updates, I had a problem using the following query:
谨防在更新时使用方括号,使用以下查询时遇到问题:
UPDATE logs SET locked=1 WHERE [id] IN (SELECT [id] FROM ids)
UPDATE日志SET locked = 1 WHERE [id] IN(SELECT [id] FROM ids)
This caused all records to be updated, however, this appears to work fine:
这导致所有记录都被更新,但是,这似乎工作正常:
UPDATE logs SET locked=1 WHERE id IN (SELECT [id] FROM ids)
UPDATE日志SET locked = 1 WHERE id IN(SELECT [id] FROM ids)
Note that this problem appears specific to updates, as the following returns only the rows expected (not all rows):
请注意,此问题特定于更新,因为以下仅返回预期的行(不是所有行):
SELECT * FROM logs WHERE [id] IN (SELECT [id] FROM ids)
SELECT * FROM logs WHERE [id] IN(SELECT [id] FROM ids)
This was using MSDE 2000 SP3 and connecting to the database using MS SQL (2000) Query Analyzer V 8.00.194
这是使用MSDE 2000 SP3并使用MS SQL(2000)查询分析器V 8.00.194连接到数据库
Very odd, possibly related to this Knowledgebase bug http://support.microsoft.com/kb/140215
非常奇怪,可能与此知识库错误http://support.microsoft.com/kb/140215有关
In the end I just removed all the unnecessary square brackets.
最后我删除了所有不必要的方括号。