在 MySQL 中使用 JSON 格式数据,并利用 Python 调用


最近考虑使用在 MySQL 中存储配置文件,首选的方案是针对每个配置项设置字段,但是产生的问题在于,配置项过多,且设置很灵活,采用固定的字段存储,使用起来很麻烦,仅创建就让人觉得很头疼。

而如果直接存储配置文件,则还需要对配置文件进行解析,且在前端进行修改时,无法对配置项的修改正确与否做出简单的判断,两头控制也比较麻烦。

仔细筛选了后,发觉可以使用 JSON 格式的字段,它有如下好处:

  • 自动的格式检查
  • 易于扩展变化
  • 易于迁移

所以就决定采用 JSON 格式字段啦。

如下是使用 Python 读写 JSON 字段数据的示例。

首先在 Python 中,该 JSON 数据,原始情况如下(一个 Dict):

In [1]: config
Out[1]: 
{'MYSQL': {'host': 'host.docker.internal',
  'port': 3306,
  'user': 'root',
  'charset': 'utf8',
  'database': 'alert_test',
  'password': 123456}}

然后我们使用 pymysql 库进行此部分数据的插入,此时有一个问题在于,我们该如何插入 JSON 数据呢?

答案是,直接插入字符串就好,MySQL 会对此字符串格式数据,自动进行检查转换,如下所示:

In [1]: config_str = json.dumps(config)

In [2]: config_str
Out[2]: '{"MYSQL": {"host": "host.docker.internal", "port": 3306, "user": "root", "charset": "utf8", "database": "alert_test", "password": 123456}}'

然后构建 SQL 执行插入

In [1]: insert_sql = '''
    ...:         INSERT INTO configuration(
    ...:             config_name,
    ...:             config_json
    ...:         )
    ...:         VALUES('%s', '%s')
    ...:     ''' %('test', config_str)

In [2]: insert_sql
Out[2]: '\n        INSERT INTO configuration(\n            config_name,\n            config_json\n        )\n        VALUES(\'dev\', \'{"MYSQL": {"host": "host.docker.internal", "port": 3306, "user": "root", "charset": "utf8", "database": "alert_test", "password": 123456}}\')\n    '

In [3]: mysql.cur.execute(insert_sql)
Out[3]: 1

In [4]: mysql.conn.commit()

可见执行是成功的,此时查看数据库。

mysql> select * from configuration where config_name='test'\G
*************************** 1. row ***************************
  config_id: 4
config_name: test
config_json: {"MYSQL": {"host": "host.docker.internal", "port": 3306, "user": "root", "charset": "utf8", "database": "alert_test", "password": 123456}}
1 row in set (0.00 sec)

mysql> desc configuration;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| config_id   | int          | NO   | PRI | NULL    | auto_increment |
| config_name | varchar(255) | YES  |     | NULL    |                |
| config_json | json         | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

数据已经在库中了,且表现为 JSON 格式。

读更简单,直接 Query 就好,返回的是字符串格式的数据,再通过 JSON loads 即可。

参考文档

  1. 说说 MySQL JSON 数据类型
  2. 11.5 The JSON Data Type