在单个表列中存储多个位值 [英] Store multiple bit values in a single table column

问题描述

我需要在数据库中存储某种星期几的日程安排，在那里我可以安排一周中一天或多天的记录(比如它代表一项任务).我需要将它存储在一列中，以便于保存和检索.然后每天我都会选择日程表与当前星期几匹配的记录，例如:获取所有日程表匹配第 7 周的记录.

I need to store some sort of day-of-week scheduling in database, where I can schedule a record (say it represents a task) for a day or multiple days of the week. I need to have it stored in one column so that it's easy to save and retrieve. Then everyday I select records where the schedule matches the current day of week, something like: get me all records with schedule matches day of week 7.

我想到的是一系列 7 位，每个位代表一周中的一天，其中 0 表示未安排，1 表示安排.例如:1111111"表示日常任务的情况，1000000"表示仅在一周的第一天运行的任务.

What came to my mind is a series of 7 bits each one represents a day of the week where 0 means not schedule, and 1 means scheduled. For example: "1111111" for a case of an everyday task, and "1000000" for a task that only runs in the first day of week.

为了清楚起见，记录可能会安排在我不知道的任意天数组合中，因此检索中的唯一输入是要搜索的工作日.

Just to be clear, the record might be scheduled in any combination of days, which I don't know, therefore the only input in the retrieval would be the weekday to search by.

我有两个问题:

1. 在 SQL Server 2008 中存储此类数据的最佳方式是什么?以允许根据当前星期几轻松查询的方式.

1. What's the best way to store this type of data in SQL Server 2008? In a way that allows easy querying depending of the current day of week.

上述方法有哪些更好的替代方案.我需要高效且易于实施的解决方案.

What are better alternatives to the above approach. Please I need efficient and easy to implement solutions.

推荐答案

您可以将其存储为位域，然后使用布尔逻辑运算符来检索值

You could store this as a bitfield and then use boolean logic operators to retrieve the values

例如:

CREATE TABLE [dbo].[testBF](
    [field1] [varchar](max) NOT NULL,
    [field2] [varchar](max) NOT NULL,
    [bitfield] [int] NOT NULL CONSTRAINT [DF_testBF_bitfield]  DEFAULT ((0))
) ON [PRIMARY]

然后对于选择:

SELECT field1, field2,
       CASE WHEN (bitfield & 1) = 1 THEN 'monday ' ELSE '' END + 
       CASE WHEN (bitfield & 2) = 2 THEN 'tuesday ' ELSE '' END +
       CASE WHEN (bitfield & 4) = 4 THEN 'wednesday ' ELSE '' END +
       CASE WHEN (bitfield & 8) = 8 THEN 'thursday ' ELSE '' END +
       CASE WHEN (bitfield & 16) = 16 THEN 'friday' ELSE '' END as [days of week]
FROM testBF

查找包含星期二标志的所有日子(星期二是第 2 位或 2^1 或 2)

To find all days that contain tuesday flag (tuesday is the 2nd bit or 2^1 or 2)

SELECT * 
FROM aTable
WHERE (bitfield & 2) = 2

或

SELECT * 
FROM aTable
WHERE (bitfield & 2) != 0

注意，第二种情况下的模板适用于任何位——即星期五(第 5 位或 2^4 或 16)将是

Note, the template in the second case will work for any bit -- that is for friday (the 5th bit or 2^4 or 16) would be

SELECT * 
FROM aTable
WHERE (bitfield & 16) != 0

最后是一般情况...传入一个数字(周一为 1)

Finally the general case... pass in a number (1 for monday) you get

SELECT * 
FROM aTable
WHERE (bitfield & POWER(2,@inNumOfWeekday-1)) != 0

<小时>

这对我来说似乎有很多工作，当您可以将其保存为 5(或 7 个位字段)时，但这就是您可以做到的.

---

This seems like a lot of work to me, when you could just save it as 5 (or 7 bit fields) but that is how you could do it.

有关更多示例，请查看我为另一个问题编写的要点:

For more examples look at the gist I wrote for another question:

https://gist.github.com/1846338

和答案:

https://stackoverflow.com/a/9302106/215752

这篇关于在单个表列中存储多个位值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！