SQL 如何插入包含撇号（单引号）的值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1912095/
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
How to insert a value that contains an apostrophe (single quote)?
提问by leora
What is the correct SQL syntax to insert a value with an apostrophe in it?
插入带有撇号的值的正确 SQL 语法是什么？
Insert into Person
(First, Last)
Values
'Joe',
'O'Brien'
I keep getting an error as I think the apostrophe after the O is the ending tag for the value.
我不断收到错误消息，因为我认为 O 后面的撇号是该值的结束标记。
回答by Paul Sasik
Escape the apostrophe (i.e. double-up the single quote character) in your SQL:
转义 SQL 中的撇号（即双引号）：
INSERT INTO Person
(First, Last)
VALUES
('Joe', 'O''Brien')
/\
right here
The same applies to SELECT queries:
这同样适用于 SELECT 查询：
SELECT First, Last FROM Person WHERE Last = 'O''Brien'
The apostrophe, or single quote, is a special character in SQL that specifies the beginning and end of string data. This means that to use it as part of your literal string data you need to escapethe special character. With a single quote this is typically accomplished by doubling your quote. (Two single quote characters, not double-quote instead of a single quote.)
撇号或单引号是 SQL 中的特殊字符，用于指定字符串数据的开头和结尾。这意味着要将它用作文字字符串数据的一部分，您需要escape特殊字符。对于单引号，这通常是通过将您的报价加倍来实现的。（两个单引号字符，不是双引号而是单引号。）
Note: You should only ever worry about this issue when you manually edit data via a raw SQL interface since writing queries outside of development and testing should be a rare occurrence. In code there are techniques and frameworks (depending on your stack) that take care of escaping special characters, SQL injection, etc.
注意：只有在通过原始 SQL 接口手动编辑数据时才应该担心这个问题，因为在开发和测试之外编写查询应该很少发生。在代码中，有一些技术和框架（取决于您的堆栈）负责转义特殊字符、SQL 注入等。
回答by Justin Niessner
You just have to double up on the single quotes...
你只需要在单引号上加倍......
insert into Person (First, Last)
values ('Joe', 'O''Brien')
回答by David Hall
回答by OMG Ponies
Because a single quote is used for indicating the start and end of a string; you need to escape it.
因为单引号用于表示字符串的开始和结束；你需要逃避它。
The short answer is to use two single quotes - ''- in order for an SQL database to store the value as '.
简短的回答是使用两个单引号 -''以便 SQL 数据库将值存储为'.
Look at using REPLACE to sanitize incoming values:
看看使用 REPLACE 来清理传入的值：
You want to check for '''', and replace them if they exist in the string with ''''''in order to escape the lone single quote.
您想检查''''，并替换它们（如果它们存在于字符串''''''中）以逃避单独的单引号。
回答by Robert Sherman
eduffy had a good idea. He just got it backwards in his code example. Either in JavaScript or in SQLite you can replace the apostrophe with the accent symbol.
eduffy 有一个好主意。他只是在他的代码示例中倒退了。在 JavaScript 或 SQLite 中，您可以用重音符号替换撇号。
He (accidentally I am sure) placed the accent symbol as the delimiter for the string instead of replacing the apostrophe in O'Brian. This is in fact a terrifically simple solution for most cases.
他（偶然地我确信）将重音符号作为字符串的分隔符而不是替换 O'Brian 中的撇号。对于大多数情况，这实际上是一个非常简单的解决方案。
回答by Nathan
The apostrophe character can be inserted by calling the CHARfunction with the apostrophe's ASCII table lookupvalue, 39. The string values can then be concatenated together with a concatenate operator.
可以通过使用撇号的ASCII 表查找值 39调用CHAR函数来插入撇号字符。然后可以使用连接运算符将字符串值连接在一起。
Insert into Person
(First, Last)
Values
'Joe',
concat('O',char(39),'Brien')
回答by Max Pringle
Single quotes are escaped by doubling them up,
单引号通过加倍转义，
The following SQL illustrates this functionality.
以下 SQL 说明了此功能。
declare @person TABLE (
[First] nvarchar(200),
[Last] nvarchar(200)
)
insert into @person
(First, Last)
values
('Joe', 'O''Brien')
select * from @person
Results
结果
First | Last
===================
Joe | O'Brien
回答by run_time_error
use double quotation marks around the values.
在值周围使用双引号。
insert into Person (First, Last) Values("Joe","O'Brien")
回答by eduffy
Use a backtick (on the ~ key) instead;
改用反引号（在 ~ 键上）；
`O'Brien`
