首页    技术博客    PHP教程    数据库技术    前端开发   HTML5    Nginx    php论坛
新用户注册 | 会员登录
加入会员，解锁专属内容
取消
热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

SQLite-UPSERT*not*INSERT或REPLACE。-SQLite-UPSERT*not*INSERTorREPLACE

作者：紫云轻梦lyq | 来源：互联网 | 2023-01-07 10:44
http:en.wikipedia.orgwikiUpserthttp:en.wikipedia.orgwikiUpsertInsertUpdatestoredproco

http://en.wikipedia.org/wiki/Upsert

http://en.wikipedia.org/wiki/Upsert

Insert Update stored proc on SQL Server

在SQL Server上插入更新存储的proc。

Is there some clever way to do this in SQLite that I have not thought of?

在SQLite中有什么我没有想到的聪明方法吗?

Basically I want to update three out of four columns if the record exists, If it does not exists I want to INSERT the record with the default (NUL) value for the fourth column.

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

The ID is a primary key so there will only ever be one record to UPSERT.

ID是一个主键，因此只有一条记录需要维护。

(I am trying to avoid the overhead of SELECT in order to determin if I need to UPDATE or INSERT obviously)

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

Suggestions?

建议吗?

I cannot confirm that Syntax on the SQLite site for TABLE CREATE. I have not built a demo to test it, but It doesnt seem to be supported..

我无法确认SQLite站点上的表创建语法。我还没有构建一个demo来测试它，但是它似乎不被支持。

If it was, I have three columns so it would actually look like:

如果是的话，我有三列所以它看起来是这样的:

CREATE TABLE table1( 
    id INTEGER PRIMARY KEY ON CONFLICT REPLACE, 
    Blob1 BLOB ON CONFLICT REPLACE, 
    Blob2 BLOB ON CONFLICT REPLACE, 
    Blob3 BLOB 
);

but the first two blobs will not cause a conflict, only the ID would So I asusme Blob1 and Blob2 would not be replaced (as desired)

但是前两个blobs不会引起冲突，只有ID会导致我asusme Blob1和Blob2不会被替换(如需要)

UPDATEs in SQLite when binding data are a complete transaction, meaning Each sent row to be updated requires: Prepare/Bind/Step/Finalize statements unlike the INSERT which allows the use of the reset function

当绑定数据是一个完整的事务时，在SQLite中进行更新，这意味着要更新的每一行都需要:准备/绑定/步骤/终结语句，这与允许使用重置功能的INSERT不同

The life of a statement object goes something like this:

语句对象的生命是这样的:

  1. Create the object using sqlite3_prepare_v2()
  2. 使用sqlite3_prepare_v2()创建对象
  3. Bind values to host parameters using sqlite3_bind_ interfaces.
  4. 使用sqlite3_bind_接口将值绑定到主机参数。
  5. Run the SQL by calling sqlite3_step()
  6. 通过调用sqlite3_step()运行SQL
  7. Reset the statement using sqlite3_reset() then go back to step 2 and repeat.
  8. 使用sqlite3_reset()重置语句，然后回到步骤2并重复。
  9. Destroy the statement object using sqlite3_finalize().
  10. 使用sqlite3_finalize()销毁语句对象。

UPDATE I am guessing is slow compared to INSERT, but how does it compare to SELECT using the Primary key?

我猜更新比插入慢，但是它与使用主键进行选择有什么不同呢?

Perhaps I should use the select to read the 4th column (Blob3) and then use REPLACE to write a new record blending the original 4th Column with the new data for the first 3 columns?

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

16 个解决方案

#1

754  

Assuming 3 columns in the table.. ID, NAME, ROLE

假设表格中有3列。ID、名称、角色

BAD: This will insert or replace all columns with new values for ID=1:

BAD:这将插入或替换ID=1的新值的所有列:

INSERT OR REPLACE INTO Employee (id, name, role) 
  VALUES (1, 'John Foo', 'CEO');

BAD: This will insert or replace 2 of the columns... the NAME column will be set to NULL or the default value:

坏:这将插入或替换2个列…NAME列将设置为NULL或默认值:

INSERT OR REPLACE INTO Employee (id, role) 
  VALUES (1, 'code monkey');

GOOD: This will update 2 of the columns. When ID=1 exists, the NAME will be unaffected. When ID=1 does not exist, the name will be default (NULL).

好:这将更新2列。当ID=1存在时，该名称将不受影响。当ID=1不存在时，该名称将默认为(NULL)。

INSERT OR REPLACE INTO Employee (id, role, name) 
  VALUES (  1, 
            'code monkey',
            (SELECT name FROM Employee WHERE id = 1)
          );

This will update 2 of the columns. When ID=1 exists, the ROLE will be unaffected. When ID=1 does not exist, the role will be set to 'Benchwarmer' instead of the default value.

这将更新2列。当ID=1存在时，该角色将不受影响。当ID=1不存在时，角色将被设置为“基准测试器”而不是默认值。

INSERT OR REPLACE INTO Employee (id, name, role) 
  VALUES (  1, 
            'Susan Bar',
            COALESCE((SELECT role FROM Employee WHERE id = 1), 'Benchwarmer')
          );

#2

117  

INSERT OR REPLACE is NOT equivalent to "UPSERT".

插入或替换不等同于“UPSERT”。

Say I have the table Employee with the fields id, name, and role:

假设我有具有字段id、名称和角色的表员工:

INSERT OR REPLACE INTO Employee ("id", "name", "role") VALUES (1, "John Foo", "CEO")
INSERT OR REPLACE INTO Employee ("id", "role") VALUES (1, "code monkey")

Boom, you've lost the name of the employee number 1. SQLite has replaced it with a default value.

突然，你失去了1号员工的名字。SQLite已经用默认值替换了它。

The expected output of an UPSERT would be to change the role and to keep the name.

UPSERT的预期输出将是更改角色并保留名称。

#3

98  

Eric B’s answer is OK if you want to preserve just one or maybe two columns from the existing row. If you want to preserve a lot of columns, it gets too cumbersome fast.

Eric B的答案是可以的，如果您想保留现有行中的一列或两列。如果你想保留大量的列，它会很快变得太麻烦。

Here’s an approach that will scale well to any amount of columns on either side. To illustrate it I will assume the following schema:

这里有一种方法可以扩展到任意数量的列。为了说明这一点，我将假设以下模式:

 CREATE TABLE page (
     id      INTEGER PRIMARY KEY,
     name    TEXT UNIQUE,
     title   TEXT,
     content TEXT,
     author  INTEGER NOT NULL REFERENCES user (id),
     ts      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
 );

Note in particular that name is the natural key of the row – id is used only for foreign keys, so the point is for SQLite to pick the ID value itself when inserting a new row. But when updating an existing row based on its name, I want it to continue to have the old ID value (obviously!).

特别要注意的是，名称是行的自然键——id只用于外键，因此，当插入新行时，SQLite将选择id值本身。但是，当基于它的名称更新现有行时，我希望它继续具有旧的ID值(显然!)

I achieve a true UPSERT with the following construct:

我实现了以下结构的真正维护:

 WITH new (name, title, author) AS ( VALUES('about', 'About this site', 42) )
 INSERT OR REPLACE INTO page (id, name, title, content, author)
 SELECT old.id, new.name, new.title, old.content, new.author
 FROM new LEFT JOIN page AS old ON new.name = old.name;

The exact form of this query can vary a bit. The key is the use of INSERT SELECT with a left outer join, to join an existing row to the new values.

这个查询的确切形式可能有所不同。关键是使用带有左外部连接的INSERT SELECT来将现有行连接到新值。

Here, if a row did not previously exist, old.id will be NULL and SQLite will then assign an ID automatically, but if there already was such a row, old.id will have an actual value and this will be reused. Which is exactly what I wanted.

在这里，如果一个行以前不存在，那么旧的。id将为NULL，然后SQLite将自动分配id，但如果已经有这样一行，则为old。id将有一个实际值，这将被重用。这正是我想要的。

In fact this is very flexible. Note how the ts column is completely missing on all sides – because it has a DEFAULT value, SQLite will just do the right thing in any case, so I don’t have to take care of it myself.

事实上，这是非常灵活的。注意ts列在各个方面都是完全缺失的——因为它有一个默认值，SQLite在任何情况下都会做正确的事情，所以我不必亲自处理它。

You can also include a column on both the new and old sides and then use e.g. COALESCE(new.content, old.content) in the outer SELECT to say “insert the new content if there was any, otherwise keep the old content” – e.g. if you are using a fixed query and are binding the new values with placeholders.

你也可以在新的和旧的两边都加上一列，然后使用COALESCE(新的)。内容(old.content)在外部选择中说“如果有新内容，插入新内容，否则保留旧内容”——例如，如果您使用固定的查询并将新值与占位符绑定。

#4

73  

If you are generally doing updates I would ..

如果你一般都在做更新，我会……

  1. Begin a transaction
  2. 开始一个事务
  3. Do the update
  4. 做更新
  5. Check the rowcount
  6. 检查rowcount
  7. If it is 0 do the insert
  8. 如果是0，插入
  9. Commit
  10. 提交

If you are generally doing inserts I would

如果你一般做插入，我会的

  1. Begin a transaction
  2. 开始一个事务
  3. Try an insert
  4. 试着插入
  5. Check for primary key violation error
  6. 检查主键违规错误。
  7. if we got an error do the update
  8. 如果我们有错误，就进行更新
  9. Commit
  10. 提交

This way you avoid the select and you are transactionally sound on Sqlite.

这样，您就可以避免选择，并且在Sqlite上可以进行事务处理。

#5

54  

I realize this is an old thread but I've been working in sqlite3 as of late and came up with this method which better suited my needs of dynamically generating parameterized queries:

我意识到这是一个旧的线程，但我最近一直在使用sqlite3，并提出了这个更适合我动态生成参数化查询的方法:

insert or ignore into (, , , ...) values(, , , ...); 
update 
 set =, =, ... where changes()=0 and =; 
 

It's still 2 queries with a where clause on the update but seems to do the trick. I also have this vision in my head that sqlite can optimize away the update statement entirely if the call to changes() is greater than zero. Whether or not it actually does that is beyond my knowledge, but a man can dream can't he? ;)
 

它仍然是两个带有where子句的查询，但是看起来很有用。我还认为，如果对changes()的调用大于0,sqlite可以完全优化掉更新语句。这是否真的是我所不知道的，但一个人可以做梦，不是吗?,)
 

For bonus points you can append this line which returns you the id of the row whether it be a newly inserted row or an existing row.
 

对于额外的点，您可以附加这一行，该行返回该行的id，无论它是新插入的行还是现有的行。
 

select case changes() WHEN 0 THEN last_insert_rowid() else  end;


							     
							                          
                           
							  

							    
#6

							    

							    

							      

39  


2018-05-18 STOP PRESS.
 

2018-05-18最新消息。
 

UPSERT support in SQLite! UPSERT syntax was added to SQLite with version 3.24.0 (pending) !
 

UPSERT SQLite的支持!SQLite增加了UPSERT语法，版本为3.24.0 (pending) !
 

UPSERT is a special syntax addition to INSERT that causes the INSERT to behave as an UPDATE or a no-op if the INSERT would violate a uniqueness constraint. UPSERT is not standard SQL. UPSERT in SQLite follows the syntax established by PostgreSQL. 
 

UPSERT是插入的一种特殊语法，如果插入会违反惟一性约束，它将使插入行为表现为更新或无操作。UPSERT不是标准SQL。SQLite中的UPSERT遵循PostgreSQL建立的语法。
 

图片来源:https://www.sqlite.org/images/syntax/upsert-clause.gif

							     

							  
                        
                           
							  

							    
#14

							    

							    

							      

0  


Following Aristotle Pagaltzis and the idea of COALESCE from Eric B’s answer, here it is an upsert option to update only few columns or insert full row if it does not exist.
 

按照亚里斯多德·帕格尔茨(Aristotle Pagaltzis)和埃里克·B (Eric B)的答案进行合并的想法，这里有一个upsert选项，如果列不存在，则只更新少量列或插入整行。
 

In this case, imagine that title and content should be updated, keeping the other old values when existing and inserting supplied ones when name not found:
 

在这种情况下，假设应该更新标题和内容，保留现有的其他旧值，并在未找到名称时插入所提供的值:
 

NOTE id is forced to be NULL when INSERT as it is supposed to be autoincrement. If it is just a generated primary key then COALESCE can also be used (see Aristotle Pagaltzis comment).
 

注id在插入时强制为NULL，因为它应该是自动递增的。如果它只是一个生成的主键，那么也可以使用COALESCE(参见亚里士多德•帕格尔茨的评论)。
 

WITH new (id, name, title, content, author)
     AS ( VALUES(100, 'about', 'About this site', 'Whatever new content here', 42) )
INSERT OR REPLACE INTO page (id, name, title, content, author)
SELECT
     old.id, COALESCE(old.name, new.name),
     new.title, new.content,
     COALESCE(old.author, new.author)
FROM new LEFT JOIN page AS old ON new.name = old.name;

 

So the general rule would be, if you want to keep old values, use COALESCE, when you want to update values, use new.fieldname
 

一般的规则是，如果你想保留旧的值，使用COALESCE，当你想要更新值时，使用new.fieldname

							     

							  
                        
                           
							  

							    
#15

							    

							    

							      

-2  


Having just read this thread and been disappointed that it wasn't easy to just to this "UPSERT"ing, I investigated further...
 

刚读了这篇文章，并且很失望，仅仅是为了这个“UPSERT”，我做了进一步的调查…
 

You can actually do this directly and easily in SQLITE.
 

你可以在SQLITE中直接和容易地做到这一点。
 

Instead of using: INSERT INTO
 

不要使用:插入
 

Use: INSERT OR REPLACE INTO
 

使用:插入或替换
 

This does exactly what you want it to do!
 

这正是你想要的效果!

							     

							  
                        
                           
							  

							    
#16

							    

							    

							      

-4  


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

 

if COUNT(*) = 0
 

如果COUNT(*)= 0
 

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

 

else if COUNT(*) > 0
 

如果计数(*)>
 

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


							     

							  
                        
                           
                        
                         

        
        
    


    

        
    




    

    

        
推荐阅读

        

            
    
                                
  • 
                    
    
                        int
                        
    Java学习笔记之面向对象编程（OOP）
    
                    
    

                    
    
                                                
    
                            Java学习笔记之面向对象编程（OOP）
                        
    
                                                
    
                        本文介绍了Java学习笔记中的面向对象编程（OOP）内容，包括OOP的三大特性（封装、继承、多态）和五大原则（单一职责原则、开放封闭原则、里式替换原则、依赖倒置原则）。通过学习OOP，可以提高代码复用性、拓展性和安全性。 ...
                        [详细]
                    
    
                    
    

                    

                
    • 

                
                                
  • 
                    
    
                        web
                        
    CSS3选择器的使用方法详解，提高Web开发效率和精准度
    
                    
    

                    
    
                                                
    
                            CSS3选择器的使用方法详解，提高Web开发效率和精准度
                        
    
                                                
    
                        本文详细介绍了CSS3新增的选择器方法，包括属性选择器的使用。通过CSS3选择器，可以提高Web开发的效率和精准度，使得查找元素更加方便和快捷。同时，本文还对属性选择器的各种用法进行了详细解释，并给出了相应的代码示例。通过学习本文，读者可以更好地掌握CSS3选择器的使用方法，提升自己的Web开发能力。 ...
                        [详细]
                    
    
                    
    

                    

                
    • 

                                
  • 
                    
    
                    
                
    • 
                
                                
  • 
                    
    
                        go
                        
    拥抱Android Design Support Library新变化（导航视图、悬浮ActionBar）
    
                    
    

                    
    
                                                
    
                            拥抱Android Design Support Library新变化（导航视图、悬浮ActionBar）
                        
    
                                                
    
                        转载请注明明桑AndroidAndroid5.0Loollipop作为Android最重要的版本之一，为我们带来了全新的界面风格和设计语言。看起来很受欢迎࿰ ...
                        [详细]
                    
    
                    
    

                    

                
    • 

                
                                
  • 
                    
    
                        go
                        
    Linux如何安装Mongodb的详细步骤和注意事项
    
                    
    

                    
    
                                                
    
                            Linux如何安装Mongodb的详细步骤和注意事项
                        
    
                                                
    
                        本文介绍了Linux如何安装Mongodb的详细步骤和注意事项，同时介绍了Mongodb的特点和优势。Mongodb是一个开源的数据库，适用于各种规模的企业和各类应用程序。它具有灵活的数据模式和高性能的数据读写操作，能够提高企业的敏捷性和可扩展性。文章还提供了Mongodb的下载安装包地址。 ...
                        [详细]
                    
    
                    
    

                    

                
    • 

                
                                
  • 
                    
    
                        go
                        
    在类中定义数组时出错 - Error on defining arrays in class
    
                    
    

                    
    
                                                
    
                        Iamtryingtomakeaclassthatwillreadatextfileofnamesintoanarray,thenreturnthatarra ...
                        [详细]
                    
    
                    
    

                    

                
    • 

                
                                
  • 
                    
    
                        format
                        
    C#学习教程：在Console中工作但在Windows窗体中不工作的异步代码分享
    
                    
    

                    
    
                                                
    
                        本文分享了一个关于在C#中使用异步代码的问题，作者在控制台中运行时代码正常工作，但在Windows窗体中却无法正常工作。作者尝试搜索局域网上的主机，但在窗体中计数器没有减少。文章提供了相关的代码和解决思路。 ...
                        [详细]
                    
    
                    
    

                    

                
    • 

                
                                
  • 
                    
    
                        match
                        
    android listview OnItemClickListener失效原因
    
                    
    

                    
    
                                                
    
                            android listview OnItemClickListener失效原因
                        
    
                                                
    
                        最近在做listview时发现OnItemClickListener失效的问题，经过查找发现是因为button的原因。不仅listitem中存在button会影响OnItemClickListener事件的失效，还会导致单击后listview每个item的背景改变，使得item中的所有有关焦点的事件都失效。本文给出了一个范例来说明这种情况，并提供了解决方法。 ...
                        [详细]
                    
    
                    
    

                    

                
    • 

                
                                
  • 
                    
    
                        int
                        
    关于cuowu类的错误提示和使用AdjustmentListener的问题
    
                    
    

                    
    
                                                
    
                        本文讨论了一个关于cuowu类的问题，作者在使用cuowu类时遇到了错误提示和使用AdjustmentListener的问题。文章提供了16个解决方案，并给出了两个可能导致错误的原因。 ...
                        [详细]
                    
    
                    
    

                    

                
    • 

                
                                
  • 
                    
    
                        filter
                        
    Perl的测试框架Test::Base简介及使用方法
    
                    
    

                    
    
                                                
    
                        本文介绍了Perl的测试框架Test::Base，它是一个数据驱动的测试框架，可以自动进行单元测试，省去手工编写测试程序的麻烦。与Test::More完全兼容，使用方法简单。以plural函数为例，展示了Test::Base的使用方法。 ...
                        [详细]
                    
    
                    
    

                    

                
    • 

                
                                
  • 
                    
    
                        filter
                        
    Voicewo在线语音识别转换jQuery插件的特点和示例
    
                    
    

                    
    
                                                
    
                            Voicewo在线语音识别转换jQuery插件的特点和示例
                        
    
                                                
    
                        本文介绍了一款名为Voicewo的在线语音识别转换jQuery插件，该插件具有快速、架构、风格、扩展和兼容等特点，适合在互联网应用中使用。同时还提供了一个快速示例供开发人员参考。 ...
                        [详细]
                    
    
                    
    

                    

                
    • 

                
                                
  • 
                    
    
                        go
                        
    Support Paged.JS for automatic hugo resume> PDF conversion.
    
                    
    

                    
    
                                                
    
                        FeatureRequestIsyourfeaturerequestrelatedtoaproblem?Please ...
                        [详细]
                    
    
                    
    

                    

                
    • 

                
                                
  • 
                    
    
                        int
                        
    Java中闭包的争论以及闭包的定义和特性
    
                    
    

                    
    
                                                
    
                        闭包一直是Java社区中争论不断的话题，很多语言都支持闭包这个语言特性，闭包定义了一个依赖于外部环境的自由变量的函数，这个函数能够访问外部环境的变量。本文以JavaScript的一个闭包为例，介绍了闭包的定义和特性。 ...
                        [详细]
                    
    
                    
    

                    

                
    • 

                
                                
  • 
                    
    
                        match
                        
    Android开发实现的计时器功能示例
    
                    
    

                    
    
                                                
    
                            Android开发实现的计时器功能示例
                        
    
                                                
    
                        本文分享了Android开发实现的计时器功能示例，包括效果图、布局和按钮的使用。通过使用Chronometer控件，可以实现计时器功能。该示例适用于Android平台，供开发者参考。 ...
                        [详细]
                    
    
                    
    

                    

                
    • 

                
                                
  • 
                    
    
                        go
                        
    Go Cobra命令行工具入门教程
    
                    
    

                    
    
                                                
    
                            Go Cobra命令行工具入门教程
                        
    
                                                
    
                        本文介绍了Go语言实现的命令行工具Cobra的基本概念、安装方法和入门实践。Cobra被广泛应用于各种项目中，如Kubernetes、Hugo和Github CLI等。通过使用Cobra，我们可以快速创建命令行工具，适用于写测试脚本和各种服务的Admin CLI。文章还通过一个简单的demo演示了Cobra的使用方法。 ...
                        [详细]
                    
    
                    
    

                    

                
    • 

                
                                
  • 
                    
    
                        int
                        
    iOS数据库Sqlite的SQL语句分类和常见约束关键字
    
                    
    

                    
    
                                                
    
                        本文介绍了iOS数据库Sqlite的SQL语句分类和常见约束关键字。SQL语句分为DDL、DML和DQL三种类型，其中DDL语句用于定义、删除和修改数据表，关键字包括create、drop和alter。常见约束关键字包括if not exists、if exists、primary key、autoincrement、not null和default。此外，还介绍了常见的数据库数据类型，包括integer、text和real。 ...
                        [详细]
                    
    
                    
    

                    

                
    • 

                
                
                                
  • 
                    
                    
                    
                
    • 
                

            

        

    




















    


    

        

            
            

                
                author-avatar
            


            

                紫云轻梦lyq            


            

                这个家伙很懒，什么也没留下！            



        

    


    

    

    
    

    

        
Tags | 热门标签

        

            
        

    


    
    

        

            

            

        

        
RankList | 热门文章

        

            
        

    

    

    





    PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具  | 在线工具

    Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved  |   京公网安备 11010802041100号  |  京ICP备19059560号-4 | PHP1.CN 第一PHP社区      版权所有