MySQL 在字段名称周围使用反引号

Using backticks around field names

提问by nickf

After reading a couple of answers and comments on some SQL questions here, and also hearing that a friend of mine works at a place which has a policy which bans them, I'm wondering if there's anything wrong with using backticks around field names in MySQL.

在这里阅读了一些关于 SQL 问题的答案和评论后，还听说我的一个朋友在一个有禁止它们的政策的地方工作，我想知道在 MySQL 中的字段名称周围使用反引号是否有什么问题.

That is:

那是：

SELECT `id`, `name`, `anotherfield` ...
-- vs --
SELECT id, name, anotherfield ...

采纳答案by Kent Fredric

Using backticks permits you to use alternative characters. In query writing it's not such a problem, but if one assumes you can just use backticks, I would assume it lets you get away with ridiculous stuff like

使用反引号允许您使用替代字符。在查询编写中，这不是一个问题，但如果有人假设您可以只使用反引号，我认为它可以让您摆脱诸如此类的荒谬之类的东西

SELECT `id`, `my name`, `another field` , `field,with,comma` 

Which does of course generate badly named tables.

这当然会生成名称错误的表。

If you're just being concise I don't see a problem with it, you'll note if you run your query as such

如果你只是简洁，我看不出有什么问题，你会注意到你是否这样运行你的查询

EXPLAIN EXTENDED Select foo,bar,baz 

The generated warning that comes back will have back-ticks andfully qualified table names. So if you're using query generation features and automated re-writing of queries, backticks would make anything parsing your code less confused.

返回的生成警告将带有反引号和完全限定的表名。因此，如果您正在使用查询生成功能和自动重写查询，反引号将使解析代码的任何内容变得不那么混乱。

I think however, instead of mandating whether or not you can use backticks, they should have a standard for names. It solves more 'real' problems.

但是，我认为，与其强制您是否可以使用反引号，不如他们应该有一个名称标准。它解决了更多“真实”的问题。

回答by Alexander Kojevnikov

The only problem with backticks is that they are not ANSI-SQL compliant, e.g. they don't work in SQL Server.

反引号的唯一问题是它们不符合 ANSI-SQL，例如它们在 SQL Server 中不起作用。

If there is a chance you would have to port your SQL to another database, use double quotes.

如果有可能必须将 SQL 移植到另一个数据库，请使用双引号。

回答by nickf

To me it makes a lot of sense to use them at all times when dealing with field names.

对我来说，在处理字段名称时始终使用它们很有意义。

• Firstly, once you get into the habit, it doesn't hurt to just hit the backtick key.
• Secondly, to me, it makes it easier to see what exactly are the fields in your query, and what are keywords or methods.
• Lastly, it allows you to use whatever field name you wish when designing your table. Sometimes it makes a lot of sense to name a field "key", "order", or "values"... all of which require backticks when referring to them.

• 首先，一旦你养成了这个习惯，只要按反引号键就没有什么坏处。
• 其次，对我来说，它可以更轻松地查看查询中的字段到底是什么，以及什么是关键字或方法。
• 最后，它允许您在设计表格时使用任何您希望的字段名称。有时将字段命名为“键”、“顺序”或“值”很有意义……所有这些在引用它们时都需要反引号。

回答by Greg

Backticks aren't part of standard ANSI SQL. From the mysql manual:

反引号不是标准 ANSI SQL 的一部分。从mysql 手册：

If the ANSI_QUOTES SQL mode is enabled, it is also allowable to quote identifiers within double quotes

如果启用了 ANSI_QUOTES SQL 模式，也允许在双引号内引用标识符

So if you use backticks and then decide to move away from MySQL, you have a problem (although you probably have a lot bigger problems as well)

因此，如果您使用反引号，然后决定放弃 MySQL，则会遇到问题（尽管您可能也遇到了很多更大的问题）

回答by Christian Lescuyer

There isn't anything wrong if you keep using MYSQL, except maybe the visual fuziness of the queries. But they do allow the use of reserved keywords or embedded spaces as table and column names. This is a no-no with most database engines and will prevent any migration at a later time.

如果您继续使用 MYSQL，则没有任何问题，除了查询的视觉模糊性。但它们确实允许使用保留关键字或嵌入空格作为表名和列名。这是大多数数据库引擎的禁忌，将阻止以后的任何迁移。

As for easy reading, many people use caps for SQL keywords, eg.

为了便于阅读，很多人对 SQL 关键字使用大写，例如。

SELECT some_fied, some_other_field FROM whatever WHERE id IS NULL;

回答by Federico

If you ask to me, backticks should always be used. But there are some reasons why a team may prefer not to use them.

如果你问我，应该总是使用反引号。但是有一些原因可能导致团队不喜欢使用它们。

Advantages:

好处：

• Using them, there are no reserved words or forbidden chars.
• In some cases, you get more descriptive error messages.
• If you avoid bad practices you don't care, but... in real word, sometimes they are a decent way to avoid SQL injections.

• 使用它们，没有保留字或禁止字符。
• 在某些情况下，您会收到更具描述性的错误消息。
• 如果您避免不良做法，您并不在乎，但是...实际上，有时它们是避免 SQL 注入的好方法。

Disadvantages:

缺点：

• They are not standard and usually not portable. However, as long as you don't use a backtick as part of an identifier (which is the worst practice I am able to imagine), you can port your query by automatically removing backticks.
• If some of your query come from Access, they may quote table names with " (and maybe you can't remove all the " blindly). However, mixtures of backticks and double quotes are allowed.
• Some stupid software or function filters your queries, and has problems with backticks. However, they are part of ASCII so this means that your software/function is very bad.

• 它们不是标准的，通常不便携。但是，只要您不使用反引号作为标识符的一部分（这是我能想象的最糟糕的做法），您就可以通过自动删除反引号来移植您的查询。
• 如果您的某些查询来自 Access，它们可能会用 " 引用表名（也许您不能盲目地删除所有的 "）。但是，允许混合使用反引号和双引号。
• 一些愚蠢的软件或功能会过滤您的查询，并且会出现反引号问题。但是，它们是 ASCII 的一部分，因此这意味着您的软件/功能非常糟糕。

回答by EllisGL

It's a lot easier to search your code-base for something in backticks. Say you have a table named event. grep -r "event" *might return hundreds of results. grep -r "\`event\`" *will return anything probably referencing your database.

在代码库中搜索反引号中的内容要容易得多。假设您有一个名为event. grep -r "event" *可能会返回数百个结果。grep -r "\`event\`" *将返回可能引用您的数据库的任何内容。

回答by Chris Jester-Young

Well, as far as I know, the whole purpose of using backticks is so you can use names that coincide with reserved keywords. So, if the name isn't colliding with a reserved keyword, I don't see any reason to use backticks. But, that's no reason to ban them, either.

好吧，据我所知，使用反引号的全部目的是让您可以使用与保留关键字一致的名称。因此，如果名称不与保留关键字冲突，我认为没有任何理由使用反引号。但是，这也不是禁止它们的理由。

回答by Sonpal singh Sengar

Simple Thing about backtick ``is use for denote identifier like database_name, table_name etc, and single quote '', double quote ""for string literals, whereas "" use for print value as it is and '' print the value variable hold or in another case print the text his have.

关于反引号`` 的简单事情是用于表示标识符，如 database_name、table_name 等，以及单引号''、双引号""用于字符串文字，而 "" 用于按原样打印值和 '' 打印值变量保持或在另一种情况下，打印他拥有的文本。

i.e 1.-> use `model`;   
    here `model` is database name not conflict with reserve keyword 'model'
2- $age = 27;
insert into `tbl_people`(`name`,`age`,`address`) values ('Ashoka','$age',"Delhi");

here i used both quote for all type of requirement. If anything not clear let me know..

回答by Command

The main use of backticks (`) in SQL is to use them in situations where you are going to call them again in upcoming clauses. In every other time it is recommended to use double quotes("").

SQL 中反引号 (`) 的主要用途是在您将在后面的子句中再次调用它们的情况下使用它们。建议每隔一段时间使用双引号("")。

For example

例如

SELECT CONCAT(Name, ' in ', city, ', ', statecode) AS `Publisher and Location`,
    COUNT(ISBN) AS "# Books",
    MAX(LENGTH(title)) AS "Longest Title",
    MIN(LENGTH(title)) AS "Shortest Title"
FROM Publisher JOIN Book
ON Publisher.PublisherID = Book.PublisherID WHERE INSTR(name, 'read')>0
GROUP BY `Publisher and Location`
HAVING COUNT(ISBN) > 1;

In the above statement do you see how Publisher and Locationis used again in GROUP BYclause.

在上面的语句中，您是否看到如何Publisher and Location在GROUP BY子句中再次使用。

Instead of using

而不是使用

GROUP BY Name, city, statecode

GROUP BY 姓名、城市、州代码

I just used

我刚用

GROUP BY Publisher and Location

通过...分组 Publisher and Location

Only when such situations arise, it is useful to use backticks. In all other times using double quotes is recommended.

只有在出现这种情况时，使用反引号才有用。在所有其他时间，建议使用双引号。