如何在MySQL数据库中存储60个布尔值？ [英] How to store 60 Booleans in a MySQL Database?

问题描述

我正在建立一个使用PHP& MySQL写一个后端 - REST API。

如果我必须在名为Reports的表中存储大约50-60个布尔值（用户必须在form）在我的移动应用程序中我存储的值（0/1）在一个简单的数组。在我的MySql表应该为每个布尔值创建一个不同的列，还是足够，如果我只是使用一个字符串或Int将其存储为数字，如110101110110111 ...？

我使用JSON获取和放置数据。

更新1：所有我需要做的是检查一切是否为1，如果其中一个是0，那么这是一个问题。在2年内，这个表将有大约15.000-20.000行，它必须非常快，并尽可能节省空间。

更新2：在速度解决方案更快？制作单独的列，并将其存储在字符串/二进制类型中。如果我必须检查哪些是0s怎么办？这是一个伟大的解决方案，如果我将它作为一个数字存储在一个列，如果它不是111..111然后发送到移动应用程序作为JSON，我解析的值，并在用户的设备上分析它？让我们说我必须处理50K行。

提前感谢。

解决方案

如果不同的行有不同的布尔值集合，一个单独的键/值表是更灵活的

如果

1. or-less static


2. 所有行都具有所有布尔值


3. 您的性能关键型搜索是查找任何值false

然后使用像'1001010010'等文本字符串是一种很好的存储方式。您可以像这样搜索

  WHERE标志<> '11111111'
  

找到所需的行。

您可以使用每个标记一个位的BINARY列。但如果你使用文本，你的表将更容易用于休闲查询和眼球检查。

编辑它必须是可用的，而不是使用BINARY而不是CHAR。说：每次我使用布尔属性数组构建这样的东西，我后来对它是如何不灵活的失望。例如，假设它是一个灯泡目录。在千年之交，布尔标志可能是像

 螺丝基座
卤素
汞蒸气
低电压
  

然后，事情改变了，我发现自己需要更多的布尔标志，

  LED 
 CFL 
 dimmable 
能源之星
  

等。突然，我的数据类型不够大，不足以容纳我需要他们持有。当我写你的布尔值列表是更多或更少的静态我的意思是，你不合理地期望有一些像灯泡特性改变在应用程序的生命周期。

所以，一个单独的属性表可能是一个更好的解决方案。它将有这些列：

  item_id fk到项目表 -  pk 
 attribute_id属性标识符 -  pk 
 attribute_value 
  

这最终是灵活的。你可以只添加新标志。您可以在应用程序的生命周期内随时将其添加到现有项目或新项目。并且，每个项目不需要相同的标志集合。你可以写什么项有任何假属性？查询如下：

  SELECT DISTINCT item_id FROM attribute_table WHERE attribute_value = 0 
  pre> 
 
 
但是，你必须小心，因为查询什么项目缺少属性很难写。
 
I'm building a mobile App I use PHP & MySQL to write a backend - REST API.

If I have to store around 50-60 Boolean values in a table called "Reports"(users have to check things in a form) in my mobile app I store the values (0/1) in a simple array. In my MySql Table should I create a different column for each Boolean value or is it enough if I simply use a string or an Int to store it as a "number" like "110101110110111..."?

I get and put the data with JSON. 

UPDATE 1: All I have to do is check if everything is 1, if one of them is 0 then that's a "problem". In 2 years this table will have around 15.000-20.000 rows, it has to be very fast and as space-saving as possible.

UPDATE 2: In terms of speed which solution is faster? Making separate columns vs store it in a string/binary type. What if I have to check which ones are the 0s? Is it a great solution if I store it as a "number" in one column and if it's not "111..111" then send it to the mobile app as JSON where I parse the value and analyse it on the user's device? Let's say I have to deal with 50K rows.

Thanks in advance.
解决方案
A separate column per value is more flexible when it comes to searching.

A separate key/value table is more flexible if different rows have different collections of Boolean values.

And, if 

your list of Boolean values is more-or-less static
all your rows have all those Boolean values
your performance-critical search is to find rows in which any of the values are false
then using text strings like '1001010010' etc is a good way to store them.  You can search like this
 WHERE flags <> '11111111'
to find the rows you need. 

You could use a BINARY column with one bit per flag. But your table will be easier to use for casual queries and eyeball inspection if you use text.  The space savings from using BINARY instead of CHAR won't be significant until you start storing many millions of rows.

edit It has to be said: every time I've built something like this with arrays of Boolean attributes, I've later been disappointed at how inflexible it turned out to be. For example, suppose it was a catalog of light bulbs. At the turn of the millennium, the Boolean flags might have been stuff like
screw base
halogen
mercury vapor
low voltage
Then, things change and I find myself needing more Boolean flags, like,
LED
CFL 
dimmable
Energy Star
etc.  All of a sudden my data types aren't big enough to hold what I need them to hold.  When I wrote "your list of Boolean values is more-or-less static" I meant that you don't reasonably expect to have something like the light-bulb characteristics change during the lifetime of your application.

So, a separate table of attributes might be a better solution.  It would have these columns:
   item_id           fk to item table         -- pk
   attribute_id      attribute identifier     -- pk
   attribute_value   
This is ultimately flexible. You can just add new flags. You can add them to existing items, or to new items, at any time in the lifetime of your application. And, every item doesn't need the same collection of flags. You can write the "what items have any false attributes?" query like this:
 SELECT DISTINCT item_id FROM attribute_table WHERE attribute_value = 0
But, you have to be careful because the query "what items have missing attributes" is a lot harder to write.

                        
这篇关于如何在MySQL数据库中存储60个布尔值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！