SQL 如果在 SQLite 中不存在，如何做
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/531035/
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 do IF NOT EXISTS in SQLite
提问by AngryHacker
I am trying to port this line from MS SQL Server to SQLite
我正在尝试将此行从 MS SQL Server 移植到 SQLite
IF NOT EXISTS(SELECT 1 FROM EVENTTYPE WHERE EventTypeName = 'ANI Received')
INSERT INTO EVENTTYPE (EventTypeName) VALUES ('ANI Received');
It seems that SQLite does not support IF NOT EXISTS or at least I can't make it work. Am I missing something simple? Is there a workaround?
似乎 SQLite 不支持 IF NOT EXISTS 或者至少我不能让它工作。我错过了一些简单的东西吗？有解决方法吗？
回答by beach
How about this?
这个怎么样？
INSERT OR IGNORE INTO EVENTTYPE (EventTypeName) VALUES 'ANI Received'
(Untested as I don't have SQLite... however this linkis quite descriptive.)
（未经测试，因为我没有 SQLite ......但是这个链接非常具有描述性。）
Additionally, this should also work:
此外，这也应该有效：
INSERT INTO EVENTTYPE (EventTypeName)
SELECT 'ANI Received'
WHERE NOT EXISTS (SELECT 1 FROM EVENTTYPE WHERE EventTypeName = 'ANI Received');
回答by TheDean
If you want to ignore the insertion of existing value, there must be a Key field in your Table. Just create a table With Primary Key Field Like:
如果您想忽略现有值的插入，则您的表中必须有一个 Key 字段。只需创建一个带有主键字段的表，例如：
CREATE TABLE IF NOT EXISTS TblUsers (UserId INTEGER PRIMARY KEY, UserName varchar(100), ContactName varchar(100),Password varchar(100));
And Then Insert Or Replace / Insert Or Ignore Query on the Table Like:
然后在表上插入或替换/插入或忽略查询，例如：
INSERT OR REPLACE INTO TblUsers (UserId, UserName, ContactName ,Password) VALUES('1','UserName','ContactName','Password');
It Will Not Let it Re-Enter The Existing Primary key Value... This Is how you can Check Whether a Value exists in the table or not.
它不会让它重新输入现有的主键值...这是检查表中是否存在值的方法。
回答by 1lb3r
You can also set a Constraint on a Table with the KEY fields and set On Conflict "Ignore"
您还可以使用 KEY 字段在表上设置约束并设置冲突时“忽略”
When an applicable constraint violation occurs, the IGNORE resolution algorithm skips the one row that contains the constraint violation and continues processing subsequent rows of the SQL statement as if nothing went wrong. Other rows before and after the row that contained the constraint violation are inserted or updated normally. No error is returned when the IGNORE conflict resolution algorithm is used.
当发生适用的约束冲突时，IGNORE 解析算法会跳过包含约束冲突的一行，并继续处理 SQL 语句的后续行，就好像没有出错一样。包含违反约束的行之前和之后的其他行被正常插入或更新。使用 IGNORE 冲突解决算法时不会返回错误。
