mysql 不是主键upsert_SQLite-UPSERT *不是* INSERT或REPLACE

在我没有想到的SQLite中，有什么聪明的方法可以做到这一点吗？

基本上，如果记录存在，我想更新四列中的三列；如果不存在，我想使用第四列的默认(NUL)值插入记录。

ID是一个主键，因此UPSERT只会有一个记录。

(我试图避免SELECT的开销，以便确定是否需要显式更新或插入)

有什么建议吗？

我无法在SQLite网站上确认TABLE CREATE的语法。 我还没有建立一个演示来测试它，但是似乎不被支持。

如果是的话，我有三列，因此实际上看起来像：

CREATE TABLE table1(

id INTEGER PRIMARY KEY ON CONFLICT REPLACE,

Blob1 BLOB ON CONFLICT REPLACE,

Blob2 BLOB ON CONFLICT REPLACE,

Blob3 BLOB

);

但前两个Blob不会引起冲突，只有ID会发生冲突，因此我不会替换Blob1和Blob2(根据需要)

当绑定数据是一个完整的事务时，SQLite中的UPDATE意味着要更新的每个发送行都需要：Prepare / Bind / Step / Finalize语句，与INSERT不同，后者允许使用reset函数

语句对象的寿命如下所示：

使用sqlite3_prepare_v2()创建对象

使用sqlite3_bind_接口将值绑定到主机参数。

通过调用sqlite3_step()运行SQL

使用sqlite3_reset()重置该语句，然后返回到步骤2并重复。

使用sqlite3_finalize()销毁语句对象。

我猜测UPDATE与INSERT相比比较慢，但是与使用主键的SELECT相比又如何呢？

也许我应该使用select读取第4列(Blob3)，然后使用REPLACE编写一条将原始第4列与前3列的新数据混合在一起的新记录？

#1楼

扩展亚里士多德的答案，您可以从虚拟“单人”表(您自己创建的具有一行的表)中进行选择。 这样可以避免重复。

我还将该示例跨MySQL和SQLite进行了移植，并使用“ date_added”列作为仅第一次设置列的示例。

REPLACE INTO page (

id,

name,

title,

content,

author,

date_added)

SELECT

old.id,

"about",

"About this site",

old.content,

42,

IFNULL(old.date_added,"21/05/2013")

FROM singleton

LEFT JOIN page AS old ON old.name = "about";

#2楼

这是一个实际上是UPSERT(更新或INSERT)而不是INSERT或REPLACE(在许多情况下工作原理不同)的解决方案。

它是这样的：

1.尝试更新是否存在具有相同ID的记录。

2.如果更新未更改任何行( NOT EXISTS(SELECT changes() AS change FROM Contact WHERE change <> 0) )，则插入记录。

因此，要么更新现有记录，要么执行插入操作。

重要的细节是使用change()SQL函数检查update语句是否命中了任何现有记录，并且仅在未命中任何记录时才执行insert语句。

值得一提的是，changes()函数不会返回由较低级别的触发器执行的更改(请参阅http://sqlite.org/lang_corefunc.html#changes )，因此请务必考虑到这一点。

这是SQL ...

测试更新：

--Create sample table and records (and drop the table if it already exists)

DROP TABLE IF EXISTS Contact;

CREATE TABLE [Contact] (

[Id] INTEGER PRIMARY KEY,

[Name] TEXT

);

INSERT INTO Contact (Id, Name) VALUES (1, 'Mike');

INSERT INTO Contact (Id, Name) VALUES (2, 'John');

-- Try to update an existing record

UPDATE Contact

SET Name = 'Bob'

WHERE Id = 2;

-- If no record was changed by the update (meaning no record with the same Id existed), insert the record

INSERT INTO Contact (Id, Name)

SELECT 2, 'Bob'

WHERE NOT EXISTS(SELECT changes() AS change FROM Contact WHERE change <> 0);

--See the result

SELECT * FROM Contact;

测试插入：

--Create sample table and records (and drop the table if it already exists)

DROP TABLE IF EXISTS Contact;

CREATE TABLE [Contact] (

[Id] INTEGER PRIMARY KEY,

[Name] TEXT

);

INSERT INTO Contact (Id, Name) VALUES (1, 'Mike');

INSERT INTO Contact (Id, Name) VALUES (2, 'John');

-- Try to update an existing record

UPDATE Contact

SET Name = 'Bob'

WHERE Id = 3;

-- If no record was changed by the update (meaning no record with the same Id existed), insert the record

INSERT INTO Contact (Id, Name)

SELECT 3, 'Bob'

WHERE NOT EXISTS(SELECT changes() AS change FROM Contact WHERE change <> 0);

--See the result

SELECT * FROM Contact;

#3楼

SELECT COUNT(*) FROM table1 WHERE id = 1;

如果COUNT(*) = 0

INSERT INTO table1(col1, col2, cole) VALUES(var1,var2,var3);

否则，如果COUNT(*) > 0

UPDATE table1 SET col1 = var4, col2 = var5, col3 = var6 WHERE id = 1;

#4楼

该答案已经更新，因此下面的评论不再适用。

2018-05-18停止新闻。

SQLite中的UPSERT支持！ UPSERT语法已添加到SQLite版本3.24.0(待定)中！

UPSERT是INSERT的特殊语法补充，如果INSERT违反唯一性约束，则它会使INSERT表现为UPDATE或no-op。 UPSERT不是标准的SQL。 SQLite中的UPSERT遵循PostgreSQL建立的语法。

或者：

另一个完全不同的方法是：在我的应用程序中，当我在内存中创建行时，我将内存中的rowID设置为long.MaxValue。 (MaxValue永远不会用作ID，您的寿命将不够长。...然后，如果rowID不是该值，则它必须已经存在于数据库中，因此如果它是MaxValue，则需要进行UPDATE，然后需要插入。仅当您可以跟踪应用程序中的rowID时，此功能才有用。

#5楼

此方法重新混合了该问题答案中的其他几种方法，并结合使用了CTE(公用表表达式)。 我将介绍查询，然后解释为什么我做了我所做的事情。

如果有300名员工，我想将300名员工的姓氏更改为DAVIS。否则，我将添加一个新员工。

表名称：employees列：id，first_name，last_name

查询是：

INSERT OR REPLACE INTO employees (employee_id, first_name, last_name)

WITH registered_employees AS ( --CTE for checking if the row exists or not

SELECT --this is needed to ensure that the null row comes second

*

FROM (

SELECT --an existing row

*

FROM

employees

WHERE

employee_id = '300'

UNION

SELECT --a dummy row if the original cannot be found

NULL AS employee_id,

NULL AS first_name,

NULL AS last_name

)

ORDER BY

employee_id IS NULL --we want nulls to be last

LIMIT 1 --we only want one row from this statement

)

SELECT --this is where you provide defaults for what you would like to insert

registered_employees.employee_id, --if this is null the SQLite default will be used

COALESCE(registered_employees.first_name, 'SALLY'),

'DAVIS'

FROM

registered_employees

;

基本上，我使用CTE来减少必须使用select语句确定默认值的次数。 由于这是CTE，因此我们只需从表中选择所需的列，而INSERT语句将使用此列。

现在，您可以通过将COALESCE函数中的空值替换为应设置的值来决定要使用的默认值。