0

I need a bitfield column in my MySQL database and I heard from the datatype BIT(M) where M is the number of bits and of course there is the datatype UNSIGNED INTEGER which holds 32 bits (0 - 2³²-1).

Of course, for a bitfield, BIT is better because I can set the number of bytes exactly as high as necessary. But I need to access the bitfield using JDBC and I could not find a good way to read and set bitfields from JDBC.

Is it in this case just better to use the UNSIGNED datatype which's size is next to the wished bitfield size? For JDBC it would simplify everything. But are there any serious performance/diskspace drawbacks?

MinecraftShamrock
  • 3,504
  • 2
  • 25
  • 44
  • possible duplicate of [JDBC, MySQL: getting bits into a BIT(M!=1) column](http://stackoverflow.com/questions/1601838/jdbc-mysql-getting-bits-into-a-bitm-1-column) – m0skit0 Nov 23 '14 at 00:14
  • Do you *really* need to do bit-fiddling in a database? There may be other more appropriate-for-MySQL ways to do what you want. – Gordon Linoff Nov 23 '14 at 00:22
  • @GordonLinoff I need to store lots of yes/no information in each column. So I think bitfields are the most efficient way. – MinecraftShamrock Nov 23 '14 at 00:25
  • I would start with booleans. Unless you have lots of such columns and many, many, many rows, there probably isn't a big performance advantage for what you want to do. If performance is an issue, you would more wisely spend your time thinking about how to partition the data, the proper indexes, whether the overall data structure is correct, and whether MySQL performance parameters are set to what you need. – Gordon Linoff Nov 23 '14 at 00:31
  • @GordonLinoff There are about 20 of such yes/no bits and I never need to know only one of them. I always need them all or none of them. That's why I would prefer a single column. – MinecraftShamrock Nov 23 '14 at 00:35
  • 1
    @MinecraftShamrock . . . You may be able to do what you want with the `set` data type (http://dev.mysql.com/doc/refman/5.7/en/set.html). It works nicely with `enum` so you can name the elements of your set. (As a note, I don't usually recommend sets because they are easily misused.) – Gordon Linoff Nov 23 '14 at 00:40
  • @GordonLinoff That seems to be exactly the right thing. How can I set and get sets in JDBC? – MinecraftShamrock Nov 23 '14 at 00:52

0 Answers0