sqlite：唯一每个标题和日期（仅日期没有时间）

发布于 2025-02-09 02:09:07 字数 573 浏览 2 评论 0原文

我有一张表格，我将一些标题存储为文本，并将日期作为文本。

我将日期值插入数据库中的ISO格式的字符串。

现在，我想拥有标题和日期的独特组合，但仅考虑ISO字符串的日期部分（年，月和日）而忽略时间部分。因此，我想要一年中特定一天的独特标题。

示例：

INSERT INTO TABLE_A (TITLE, DATE) VALUES ('TITLE_A', '2022-06-20T13:53:41.680Z') -- OK
INSERT INTO TABLE_A (TITLE, DATE) VALUES ('TITLE_A', '2022-06-20T22:12:32.430Z') -- NOT OK same title and only time is different
INSERT INTO TABLE_A (TITLE, DATE) VALUES ('TITLE_B', '2022-06-20T13:53:41.680Z') -- OK same date but title is different

我最好的选择是什么？我可以实现检查，但是我不确定这是否是最佳选择，因为随着表的增长，这可能会使检查速度慢。

原文

I have a table in which i store some titles as a TEXT and a date also as a TEXT.

I am inserting the date value as a string in ISO format in the database.

Now i want to have a unique combination of title and date but only considering the date part(year, month and day) of the ISO string and ignoring the time part. So i want a unique title per specific day in the year.

Example:

INSERT INTO TABLE_A (TITLE, DATE) VALUES ('TITLE_A', '2022-06-20T13:53:41.680Z') -- OK
INSERT INTO TABLE_A (TITLE, DATE) VALUES ('TITLE_A', '2022-06-20T22:12:32.430Z') -- NOT OK same title and only time is different
INSERT INTO TABLE_A (TITLE, DATE) VALUES ('TITLE_B', '2022-06-20T13:53:41.680Z') -- OK same date but title is different

What are my best options? I could implement a check but i'm not sure if that is the best option because as the table grows this could make the check slow.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

只涨不跌 2025-02-16 02:09:07

sqlite不允许在unique约束的定义中使用函数，这将解决这样的问题：

UNIQUE(TITLE, date(DATE)) -- not allowed

而是可以定义a 生成的列（版本3.31.0+）作为列的日期date的日期部分，并将其用于约束的定义。

将现有列重命名为timestamp并将生成的列命名为date：

CREATE TABLE TABLE_A (
  TITLE TEXT, 
  TIMESTAMP TEXT,
  DATE TEXT GENERATED ALWAYS AS (date(TIMESTAMP)),
  UNIQUE(TITLE, DATE)
);

INSERT INTO TABLE_A (TITLE, TIMESTAMP) VALUES ('TITLE_A', '2022-06-20T13:53:41.680Z'); -- OK
INSERT INTO TABLE_A (TITLE, TIMESTAMP) VALUES ('TITLE_A', '2022-06-20T22:12:32.430Z'); -- error
INSERT INTO TABLE_A (TITLE, TIMESTAMP) VALUES ('TITLE_B', '2022-06-20T13:53:41.680Z'); -- OK

IS，生成的列date 实际上并未存储在表中。

SQLite does not allow the use of functions inside the definition of a UNIQUE constraint, which would solve the problem like this:

UNIQUE(TITLE, date(DATE)) -- not allowed

Instead you can define a generated column (version 3.31.0+) as the date part of the column DATE and use that in the definition of the constraint.

It would make more sense to rename your existing column to TIMESTAMP and name the generated column as DATE:

CREATE TABLE TABLE_A (
  TITLE TEXT, 
  TIMESTAMP TEXT,
  DATE TEXT GENERATED ALWAYS AS (date(TIMESTAMP)),
  UNIQUE(TITLE, DATE)
);

INSERT INTO TABLE_A (TITLE, TIMESTAMP) VALUES ('TITLE_A', '2022-06-20T13:53:41.680Z'); -- OK
INSERT INTO TABLE_A (TITLE, TIMESTAMP) VALUES ('TITLE_A', '2022-06-20T22:12:32.430Z'); -- error
INSERT INTO TABLE_A (TITLE, TIMESTAMP) VALUES ('TITLE_B', '2022-06-20T13:53:41.680Z'); -- OK

As it is, the generated column DATE is not actually stored in the table.

回复收藏 0 原文

救赎№ 2025-02-16 02:09:07

您可以维护纯日期列，然后对标题和日期的组合施加独特的约束。

CREATE TABLE TABLE_A (
    TITLE VARCHAR NOT NULL,
    DATE VARCHAR NOT NULL,
    TIMESTAMP VARCHAR NOT NULL,
    CONSTRAINT c_unique UNIQUE (TITLE, DATE)
);

INSERT INTO TABLE_A (TITLE, DATE, TIMESTAMP)
VALUES ('TITLE_A', '2022-06-20', '2022-06-20T13:53:41.680Z') -- passes
INSERT INTO TABLE_A (TITLE, DATE, TIMESTAMP)
VALUES ('TITLE_A', '2022-06-20', '2022-06-20T22:12:32.430Z') -- fails
INSERT INTO TABLE_A (TITLE, DATE, TIMESTAMP)
VALUES ('TITLE_B', '2022-06-20', '2022-06-20T13:53:41.680Z') -- passes

You could maintain a pure date column and then impose a unique constraint on the combination of title and date.

CREATE TABLE TABLE_A (
    TITLE VARCHAR NOT NULL,
    DATE VARCHAR NOT NULL,
    TIMESTAMP VARCHAR NOT NULL,
    CONSTRAINT c_unique UNIQUE (TITLE, DATE)
);

INSERT INTO TABLE_A (TITLE, DATE, TIMESTAMP)
VALUES ('TITLE_A', '2022-06-20', '2022-06-20T13:53:41.680Z') -- passes
INSERT INTO TABLE_A (TITLE, DATE, TIMESTAMP)
VALUES ('TITLE_A', '2022-06-20', '2022-06-20T22:12:32.430Z') -- fails
INSERT INTO TABLE_A (TITLE, DATE, TIMESTAMP)
VALUES ('TITLE_B', '2022-06-20', '2022-06-20T13:53:41.680Z') -- passes

回复收藏 0 原文

~没有更多了~