问题描述
假设你有一个共同的范畴,它的数据库存储方法更有效的速度和处理(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 (thisis a place where you can have a slowdown while dealing withlarge volume of data). - It's flexible: for instance, you need to select all cars withradar.
SELECT ... WHERE hasRadar = 1
. That's it.
这篇关于用于复选框类型的数据MySQL的数据结构与计算出的数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!