Back to Details

using-sqlite-as-a-key-value-store

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Using SQLite as a Key Value Store

将SQLite用作键值存储

SQLite is a very capable edge database that can store various shapes of data.
Key/Value databases are popular in applications for storing settings, and other non-relational data.
By using SQLite to store the key/values you can contain all the data for a user in a single file and can attach it to other databases or sync it to a server.
SQLite 是一款功能强大的边缘数据库，可存储各种类型的数据。
键值数据库在应用程序中广泛用于存储设置和其他非关系型数据。
通过使用SQLite存储键值对，你可以将用户的所有数据封装在单个文件中，还可以将其附加到其他数据库或同步到服务器。

Create the table

创建表

To store key/value type data we need to first create our table.
CREATE TABLE key_value (
  key TEXT NOT NULL PRIMARY KEY,
  value,
  UNIQUE(key)
);
key
value
user_id
1
foo
bar
active
1
guest
0
SQLite has optional column types and can be very useful for dynamic values.
要存储键值类型的数据，我们首先需要创建对应的表。
CREATE TABLE key_value (
  key TEXT NOT NULL PRIMARY KEY,
  value,
  UNIQUE(key)
);
key
value
user_id
1
foo
bar
active
1
guest
0
SQLite支持可选列类型，这对于存储动态值非常有用。

Save a value

保存值

To save a value for a given key we can run the following:
INSERT OR REPLACE 
INTO key_value (key, value) 
VALUES (:key, :value)
RETURNING *;
key
value
user_id
1
Since the key is UNIQUE we do not have to worry about conflicts as it will overwrite the value as intended.
要为指定键保存值，我们可以执行以下语句：
INSERT OR REPLACE 
INTO key_value (key, value) 
VALUES (:key, :value)
RETURNING *;
key
value
user_id
1
由于键具有UNIQUE约束，我们无需担心冲突问题，它会按预期覆盖现有值。

Read a value

读取值

To read a value we can pass in a key to our query:
SELECT value FROM key_value 
WHERE key = :key;
value
1
This will only return a single value column with a max of 1 rows.
要读取值，我们可以在查询中传入对应的键：
SELECT value FROM key_value 
WHERE key = :key;
value
1
这将仅返回单个值列，最多1行数据。

Delete a value

删除值

To delete a value or key we can run the following:
DELETE FROM key_value 
WHERE key = :key;
要删除某个键或对应的值，我们可以执行以下语句：
DELETE FROM key_value 
WHERE key = :key;

Search for key or value

搜索键或值

We can also search for a specific key or value (if it is a string) with the following:
SELECT key, value
FROM key_value 
WHERE key LIKE :query 
OR value LIKE :query;
key
value
bar
1
foo
bar
我们还可以搜索特定的键或值（如果值是字符串类型），使用以下语句：
SELECT key, value
FROM key_value 
WHERE key LIKE :query 
OR value LIKE :query;
key
value
bar
1
foo
bar

Drift Support

Drift 支持

If you are using Drift in dart, create a new file 
key_value.drift
and add the following:
CREATE TABLE key_value (
  "key" TEXT NOT NULL PRIMARY KEY,
  value TEXT,
  UNIQUE("key")
);

setItem:
INSERT OR REPLACE 
INTO key_value ("key", value) 
VALUES (:key, :value)
RETURNING *;

getItem:
SELECT value FROM key_value 
WHERE "key" = :key;

deleteItem:
DELETE FROM key_value 
WHERE "key" = :key;

searchItem:
SELECT "key", value
FROM key_value 
WHERE "key" LIKE :query 
OR value LIKE :query;
如果你在Dart中使用Drift，可以创建一个新文件 
key_value.drift
并添加以下内容：
CREATE TABLE key_value (
  "key" TEXT NOT NULL PRIMARY KEY,
  value TEXT,
  UNIQUE("key")
);

setItem:
INSERT OR REPLACE 
INTO key_value ("key", value) 
VALUES (:key, :value)
RETURNING *;

getItem:
SELECT value FROM key_value 
WHERE "key" = :key;

deleteItem:
DELETE FROM key_value 
WHERE "key" = :key;

searchItem:
SELECT "key", value
FROM key_value 
WHERE "key" LIKE :query 
OR value LIKE :query;

Demo

演示