如何在MySql中避免撇号?

The MySQL documentation says that it should be \'. However, both scite and mysql shows that '' works. I saw that and it works. What should I do?

MySQL文档说应该是“\”。然而，scite和mysql都显示了“工作”。我看到了，它起作用了。我应该做什么?

9 个解决方案

#1

136

The MySQL documentation you cite actually says a little bit more than you mention. It also says,

你引用的MySQL文档实际上比你提到的要多一点。它还说,

A “'” inside a string quoted with “'” may be written as “''”.

用" ' "引号括起来的字符串中的" ' "可以写成" " "。

(Also, you linked to the MySQL 5.0 version of Table 8.1. Special Character Escape Sequences, and the current version is 5.6 — but the current Table 8.1. Special Character Escape Sequences looks pretty similar.)

(另外，您还链接到了表8.1的MySQL 5.0版本。特殊的字符转义序列，当前版本是5.6，但是当前的表8.1。特殊字符转义序列看起来非常相似。

I think the Postgres note on the backslash_quote (string) parameter is informative:

我认为backslash_quote (string)参数上的Postgres说明非常有用:

This controls whether a quote mark can be represented by \' in a string literal. The preferred, SQL-standard way to represent a quote mark is by doubling it ('') but PostgreSQL has historically also accepted \'. However, use of \' creates security risks...

它控制一个引号标记是否可以用字符串文字的\'来表示。表示引号的首选的sql标准方法是将其加倍(")，但PostgreSQL历来也接受\'。然而，使用\'会产生安全风险…

That says to me that using a doubled single-quote character is a better overall and long-term choice than using a backslash to escape the single-quote.

对我来说，使用双引号字符比使用反斜杠来转义单引号更全面、更长远。

Now if you also want to add choice of language, choice of SQL database and its non-standard quirks, and choice of query framework to the equation, then you might end up with a different choice. You don't give much information about your constraints.

现在，如果您还想在等式中添加语言选择、SQL数据库及其非标准特性的选择以及查询框架的选择，那么您可能会得到不同的选择。你没有给出太多关于你的约束条件的信息。

#2

Standard SQL uses doubled-up quotes; MySQL has to accept that to be reasonably compliant.

标准SQL使用双重引号;MySQL必须接受这一点才能合理地兼容。

'He said, "Don''t!"'

#3

What I believe user2087510 meant was:

我相信user2087510的意思是:

name = 'something'
name = name.replace("'", "\\'")

I have also used this with success.

我也成功地利用了这一点。

#4

just write '' in place of ' i mean two times '

写"代替"我的意思是2次

#5

There are three ways I am aware of. The first not being the prettiest and the second being the common way in most programming languages:

我知道有三种方式。第一个不是最漂亮的，第二个在大多数编程语言中是最常见的:

Use another single quote: 'I mustn''t sin!'
再引用一句:“我不能犯罪!”
Use the escape character \ before the single quote': 'I mustn\'t sin!'
在“我不能犯罪!”
Use double quotes to enclose string instead of single quotes: "I mustn't sin!"
用双引号代替单引号:“我不能犯罪!”

#6

Here's an example:

这里有一个例子:

SELECT * FROM pubs WHERE name LIKE "%John's%"

Just use double quotes to enclose the single quote.

用双引号将单引号括起来。

If you insist in using single quotes (and the need to escape the character):

如果您坚持使用单引号(以及需要转义字符):

SELECT * FROM pubs WHERE name LIKE '%John\'s%'

#7

Replace the string

替换的字符串

value = value.replace(/'/g, "\\'");

where value is your string which is going to store in your Database.

值是字符串，它将存储在数据库中。

Further,

此外,

NPM package for this, you can have look into it

NPM包装，你可以看一下

https://www.npmjs.com/package/mysql-apostrophe

#8

I think if you have any data point with apostrophe you can add one apostrophe before the apostrophe

如果你有任何带撇号的数据点，你可以在撇号之前添加一个撇号

eg. 'This is John's place'

如。这是约翰的地方

Here MYSQL assumes two sentence 'This is John' 's place'

这里MYSQL假设有两个句子" This is John' s place "

You can put 'This is John''s place'. I think it should work that way.

你可以写上“这是约翰的家”。我认为应该这样。

#9

-5

This one worked:

这一个工作:

name = 'John O'Brien'
name = name.replace("\'", "\\\'")

#1

136