在 SQLite 中插入或更新

问题描述

我有一个包含 4 列的简单表格(ID(自动生成)、ip、日期、访问次数)

I have a simple table with 4 columns (ID (auto gen), ip,date,visits)

例如:

1,'10.10.0.10','2017-03-01',1

1,'10.10.0.10', '2017-03-01',1

我正在 SQLite 中寻找插入或更新，它需要检查 ip &日期列，如果存在则更新访问次数 +1 否则插入新行.

I am looking for Insert or Update in SQLite which needs to check the ip & date column and if exists update visits +1 else insert a new row.

我可以发出 2 条 sql，但想将其保留在 1 条 sql 中.

i can issue 2 sql but wanted to keep it in 1 sql.

这在 SQLite 中可行吗?

Is this possible in SQLite?

推荐答案

如果您为 IP 和日期生成唯一索引，您可以使用 insert or replace 语句分两步实现:

If you generate a uniqe index for IP and date you can use the insert or replace statement to achieve this in two steps:

1. 尝试查询IP和日期的当前访问次数；如果未找到记录，则设置为 0.

2. 对 IP 和日期执行 insert or replace(我在本例中使用命名参数；您需要将它们绑定到适当的值):

1. Try to query the current number of visits for IP and date; set to 0 if record not found.

2. Execute the insert or replace for IP and date (I'm using named parameters in this example; you need to bind them to the appropriate values):

insert or replace into Visits (id, ip, date, visits)
values (NULL, :ip, :date, :visits + 1);

您可以添加表约束来创建唯一索引，如下例所示，或者使用单独的 create unique index 语句:

You can add a table contstraint to create the unique index like in the following example, or you use a separate create unique index statement:

create table Visits (..., unique (ip, date));

附录:甚至可以更新一个查询中的访问次数:

Addendum: There's even a possibility to update the number of visits in one query:

insert or replace into Visits (id, ip, date, visits)
    select NULL, :ip, :date, ifnull(max(visits), 0) + 1
    from Visits
    where ip = :ip and date = :date;

这篇关于在 SQLite 中插入或更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！