用于复选框类型的数据MySQL的数据结构与计算出的数据 [英] MySQL data structure vs. calculated data for Checkbox type data

问题描述

假设你有一个共同的范畴，它的数据库存储方法更有效的速度和处理（MySQL和PHP）内的多个布尔字段？

Assuming you have a multiple boolean fields within a common category, which database storage method is more efficient for both speed and processing (for both MySQL and PHP)?

例如，如果选择一个车，你可能有一个类别选项包含以下选项：
（GPS，拖车套餐，雷达，Powersteering）。所有选项都是布尔字段，它必须回答，而且必须是 TRUE 或 FALSE 。

For example, if choosing a car you may have a category "options" with the following selections: (GPS,Tow package,Radar,Powersteering). All options are boolean fields, which must be answered, and must be TRUE or FALSE.

它是更好地建立一个表，每个字段：

Is it better to set up a table with each field:

CREATE TABLE IF NOT EXISTS `manycars` (
  `vin` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `hasGps` tinyint(1) NOT NULL COMMENT '1= TRUE, 0=FALSE',
  `hasTow` tinyint(1) NOT NULL COMMENT '1= TRUE, 0=FALSE',
  `hasRadar` tinyint(1) NOT NULL COMMENT '1= TRUE, 0=FALSE',
  `hasPsteer` tinyint(1) NOT NULL COMMENT '1= TRUE, 0=FALSE',
  PRIMARY KEY (`vin`),
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

或选择名为选项带着几分风情的数据存储，如单场：

or choose a single field named "options" with a bit style data storage, such as:

CREATE TABLE IF NOT EXISTS `singlecars` (
  `vin` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `options` int(3) unsigned NOT NULL COMMENT '1= GPS, 2=Tow, 4=radar, 8=psteer',
  PRIMARY KEY (`vin`),
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

为了确定每个布尔值，我会再查询，如选择选项，Vin的singlecars 和提取：

In order to determine the values for each boolean, I would then query such as SELECT options, vin FROM singlecars and extract:

$q=SELECT options, vin FROM singlecars
$r=mysqli_query($dbconnect, $q);
while($record = mysqli_fetch_array($r, MYSQLI_ASSOC)){
  $option=decbin($record['options']; // returns binary
  $gps=substr($option,3,1);
  $tow=substr($option,2,1);
  $radar=substr($option,1,1);
  $psteer=substr($option,0,1);
  echo "GPS=$gps, Tow package=$tow, Radar=$radar, Power Steering=$psteer <br />";
}

我的想法是，第一台manycars具有更好的语义，快速查询和最小的PHP code编写一个查询。然而，第二个表singlecars使用更少的SQL结构，在所有选项是布尔的情况，很可能是需要每次。

My thoughts are that the first table "manycars" has better semantics, and is quick to query and minimal php code to write a query. However, the second table "singlecars" uses less SQL structure, and in the case where all options are boolean, are likely to be needed everytime.

这是一个理论上的例子，但我感兴趣的是每种方法的PRO / CON。

This is a theoretical example, but I am interested in the pro/con of each method.

推荐答案

我会去为每个选项单独字段的变体。

I would go for the variant with separate fields for each option.

• 它的速度更快：你不需要使用 SUBSTR 在while循环（这
  是一个地方，你可以有一个放缓的同时处理
  大量的数据）。


• 这是灵活的：比如，您需要选择所有的汽车行驶
  的雷达的。 SELECT ... WHERE hasRadar = 1 。这就是它。

• It's faster: you don't need to use substr in your while loop (this is a place where you can have a slowdown while dealing with large volume of data).
• It's flexible: for instance, you need to select all cars with radar. SELECT ... WHERE hasRadar = 1. That's it.

这篇关于用于复选框类型的数据MySQL的数据结构与计算出的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！