如何在MySQL中存储工作日列表? [英] How to store a list of weekdays in MySQL?

问题描述

我正在使用 PHP 编写应用程序，并且需要将唯一工作日列表存储在 MySQL 中.

I'm writing an application using PHP and I need to store a list of unique weekdays in MySQL.

在应用程序中，我有一个数组来存储工作日，像这样:

In the application, I have an array to store the weekdays, like this:

$days = [
    'Wed',
    'Thu',
    'Sat',
];

我知道我可以使用 SET 列，但是由于与我正在使用的框架(Laravel)不兼容，我宁愿不使用这种类型.

I know I can use a SET column, but I prefer to not use this type due incompatibility with the framework I'm using (Laravel).

如何使用仍可以在存储的值中搜索的格式保存此列表?例如，我想检索所有具有星期日的行.

How can I save this list using a format that I can still search within the stored values? I'd like to retrieve all rows that have a Sunday, for instance.

推荐答案

为解决此问题，我最终使用位掩码存储了工作日.

To solve this problem, I end up using a bitmask to store the weekdays.

我将工作日数组的内容更改为以下内容:

I changed the weekdays array contents to something like this:

$days = [
     8, // Wednesday
    16, // Thursday
    64, // Saturday
];

使用此列表作为参考:

 1 : Sunday
 2 : Monday
 4 : Tuesday
 8 : Wednesday
16 : Thursday
32 : Friday
64 : Saturday

然后，我添加了 TINYINT 列来保存工作日的位掩码.将值存储在数据库中时，我可以简单地使用以下内容:

Then I added a TINYINT column to hold the weekdays bitmask. When storing the values in the database I can simply use the following:

$valueToMySQL = array_sum($days); // 88, with the sample above

要搜索具有特定工作日(例如星期六)的行，我可以使用以下条件:

To search for rows with a specific weekday, Saturday for example, I can use this condition:

... WHERE `weekdays` & 64;

从数据库中检索工作日是一个简单的数组.我使用以下逻辑:

Retrieve the weekdays from database as an array is a little less simple. I'm using the following logic:

$days = [];
$bitmask = strrev(decbin($valueFromMySQL));
for ($i = 0, $s = strlen($bitmask); $i < $s; $i++) {
    if ($bitmask{$i}) {
        $days[] = pow(2, $i);
    }
}

如果需要检索与当前日期相同的工作日的所有行，则可以将当前的工作日传递给以前的SQL条件，如下所示:

If I need to retrieve all rows with the same weekday as the current date, I can pass the current weekday to the previous SQL condition as follows:

$weekday = (int) date('w'); // 0 for Sunday, to 6 for Saturday
$weekdayToMySQL = pow(2, $weekday); // 1 - 64

然后:

... WHERE `weekdays` & {$weekdayToMySQL};

这篇关于如何在MySQL中存储工作日列表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！