Store Series of 0s and 1s in MySQL database

Question

Store Series of 0s and 1s in MySQL database

I'm using MySql and Nodejs. I need to store a huge string made of only 0s and 1s. (This is a map grid. 0 = Can Move, 1 = Can't move.)

Ex: 00000000000000000000000000000000000010000000000000000000000010000000000000000000000000000000000000000000000000000000000000000000000100000000000000000000000100000000000000000000000000000000000000000000000000000000000000000000001000000000000000000000001000000000000000000000000000000000000000000000000000000000000000000000010000000000 ...

Should I take that string, encode it into a base64 string and store it that way? Then when I need it, I decode it? If so, how would I do it, using Nodejs/Javascript?

Thanks

mysql
string
node.js
store
bit

Answer 1

The data you're storing appears to compress easily, why don't you use the built-in MySql COMPRESS function

Raw:

mysql> select length('00000000000000000000000000000000000010000000000000000000000010000000000000000000000000000000000000000000000000000000000000000000000100000000000000000000000100000000000000000000000000000000000000000000000000000000000000000000001000000000000000000000001000000000000000000000000000000000000000000000000000000000000000000000010000000000');                                                                                                                                                                                             
332

With COMPRESS

mysql> select length(compress('00000000000000000000000000000000000010000000000000000000000010000000000000000000000000000000000000000000000000000000000000000000000100000000000000000000000100000000000000000000000000000000000000000000000000000000000000000000001000000000000000000000001000000000000000000000000000000000000000000000000000000000000000000000010000000000'));                                                                                                                                                                                                                                                      
23

Answer 2

Don't store a string, store binary.

Here's an example of a way you could convert that data to binary. Hopefully you can reengineer things so that your map data is never represented as a string in the first place.

        static void Main(string[] args)
    {
        const string data =
            "00000000000000000000000000000000000010000000000000000000000010000000000000000000000000000000000000000000000000000000000000000000000100000000000000000000000100000000000000000000000000000000000000000000000000000000000000000000001000000000000000000000001000000000000000000000000000000000000000000000000000000000000000000000010000000000";

        var arrayLength = data.Length/8;
        if (data.Length%8 != 0)
            arrayLength++;
        var binaryData = new byte[arrayLength];

        byte nextByte=0;
        var k = -1;
        for (var i = 0; i < data.Length; i++)
        {
            var j = i%8;
            if (j == 0)
            {
                binaryData[++k] = nextByte;
                nextByte = 0;
            }

            var bit = int.Parse(data[i].ToString());
            if(bit==1)
                nextByte |= (byte)(bit << j);
        }
        binaryData[k] = nextByte;
    }