MySQL JSON Query sends random numbers

I’m writing a MySQL Query in Python using pymysql to send JSON data to a MySQL table. When it sends the data, the following results are produced.

| id | data                                                                                                                                                                                                                                               |
+----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 21 | 0x7B226775696C6473223A207B22383538393137313431303633343031353132223A207B226D656D62657273223A207B22343130373936333038323431303535373436223A207B22706F696E7473223A203137302C2022726166666C65735F776F6E223A20307D7D2C2022726166666C6573223A20307D7D7D |

The code I used to send the data is the following:

        self.data_str = json.dumps(self.data)
        sql = "insert into jsondata ( data) values ('" + self.data_str + "') "
        mysql.exec_sql(sql)
        mysql.close_db()

The exec_sql function is:

    def exec_sql(self, sql):
        # sql is insert, delete or update statement
        cursor = self.db.cursor()
        try:
            cursor.execute(sql)
            # commit sql to mysql
            self.db.commit()
            cursor.close()
            return True
        except:
            self.db.rollback()
        return False

An example line of JSON data is

{"guilds": {"853317141063401512": {"members": {"410846308241055746": {"points": 250, "raffles_won": 0}}, "raffles": 0}}}

My SQL table was setup as follows:

| Field | Type   | Null | Key | Default | Extra          |
+-------+--------+------+-----+---------+----------------+
| id    | int(6) | NO   | PRI | NULL    | auto_increment |
| data  | blob   | YES  |     | NULL    |                |
+-------+--------+------+-----+---------+----------------+

Answer

The bytes are not random. They are the hex representation of ASCII bytes in your JSON string. Observe:

mysql> select unhex('7B226775696C6473223A207B22383538393137313431303633343031353132223A207B226D656D62657273223A207B22343130373936333038323431303535373436223A207B22706F696E7473223A203137302C2022726166666C65735F776F6E223A20307D7D2C2022726166666C6573223A20307D7D7D') as j;
+--------------------------------------------------------------------------------------------------------------------------+
| j                                                                                                                        |
+--------------------------------------------------------------------------------------------------------------------------+
| {"guilds": {"858917141063401512": {"members": {"410796308241055746": {"points": 170, "raffles_won": 0}}, "raffles": 0}}} |
+--------------------------------------------------------------------------------------------------------------------------+

What you’re seeing is that when you store a JSON string in a binary column (BLOB), MySQL “forgets” that it is supposed to be text, and dumps only the hex encoding of the bytes when you query it.

If you want to store JSON, then use the JSON data type, not BLOB.