mysql中复选框类型,用于复选框类型的数据MySQL的数据结构与计算出的数据

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)?

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;

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
";

}

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.

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.

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.