如何在H2数据库中插入含两层嵌套单引号的字符串？

我需要在H2控制台插入一段包含两层嵌套引号的字符串，H2数据库使用单引号作为字符串边界，不能用双引号。尝试用转义单引号的方式写INSERT语句，但执行报错。

错误的INSERT语句 #

INSERT INTO Encouragement VALUES(31,'WhoIAmInChrist','NoAnxietyInChrist','Uplifting','God is bigger than our fearful hearts: Isaiah 35:4  '\''Say to those with fearful hearts, '\''Be strong, do not fear; your God will come, he will come with vengeance; with divine retribution he will come to save you'\'' '\'' (NIV).')

浏览器控制台错误信息 #

INSERT INTO Encouragement VALUES(31,'WhoIAmInChrist','NoAnxietyInChrist','Uplifting','God is bigger than our fearful hearts: Isaiah 35:4 \"Say to those with fearful hearts, \"Be strong, do not fear; your God will come, he will come with vengeance; with divine retribution he will come to save you'\" '\" (NIV).');

Syntax error in SQL statement "INSERT INTO Encouragement VALUES(31,'WhoIAmInChrist','NoAnxietyInChrist','Uplifting','God is bigger than our fearful hearts: Isaiah 35:4 \\""Say to those with fearful hearts, \\""Be strong, do not fear; your God will come, he will come with vengeance; with divine retribution he will come to save you'[*]\\"" '\\"" (NIV).')"; SQL statement:
INSERT INTO Encouragement VALUES(31,'WhoIAmInChrist','NoAnxietyInChrist','Uplifting','God is bigger than our fearful hearts: Isaiah 35:4 \"Say to those with fearful hearts, \"Be strong, do not fear; your God will come, he will come with vengeance; with divine retribution he will come to save you'\" '\" (NIV).') [42000-214] 42000/42000 (Help)

数据库表结构 #

CREATE TYPE Category AS ENUM('WhoIAmInChrist','Default')
CREATE TYPE Tone AS ENUM('Default', 'Uplifting', 'Urging', 'Warning', 'Soothing', 'Comforting', 'Inspiring', 'Centering', 'Balanced')
CREATE TYPE Topic AS ENUM('Default', 'AcceptedInChrist', 'SignificantInChrist', 'SecureInChrist', 'NoAnxietyInChrist');
CREATE TABLE Encouragement(ID INT PRIMARY KEY, CATEGORY Category, TOPIC Topic, TONE Tone, MESSAGE VARCHAR(255))

问题原因 #

H2中嵌套单引号的正确方式是用两个连续的单引号表示一个单引号，你之前的写法混淆了其他场景的转义逻辑，导致字符串内部引号不匹配，触发语法错误。

修正后的INSERT语句 #

INSERT INTO Encouragement VALUES(31,'WhoIAmInChrist','NoAnxietyInChrist','Uplifting','God is bigger than our fearful hearts: Isaiah 35:4  ''Say to those with fearful hearts, ''Be strong, do not fear; your God will come, he will come with vengeance; with divine retribution he will come to save you'' '' (NIV).')

关键说明 #

• 在H2的字符串中，每一个需要显示的单引号，都要写成两个连续的单引号（''），不要使用'\''这类非SQL标准的转义方式。
• 确保整个MESSAGE字段被一对单引号包裹，内部所有单引号都替换为双单引号，避免出现引号不闭合的情况。
• 注意MESSAGE字段定义为VARCHAR(255)，需确认最终字符串长度不超过该限制，避免触发长度溢出错误。

内容的提问来源于stack exchange，提问作者likejudo