mysql如何插入怎么将json数据存到数据库中键值，急求大神

来源：蜘蛛抓取(WebSpider) 时间：2024-02-05 02:45 标签： 怎么将json数据存到数据库中
需求描述:在mysql数据库中,创建包含json数据类型的表.记录下,在创建的过程中,需要注意的问题.操作过程:1.通过以下的语句,创建包含json数据类型的表mysql> create table tab_json(id bigint not null auto_increment,data json,primary key(id));
Query OK, 0 rows affected (0.09 sec)
mysql> desc tab_json;
+-------+--------+------+-----+---------+----------------+
Field
Type
Null
Key
Default
Extra
+-------+--------+------+-----+---------+----------------+
id
bigint
NO
PRI
NULL
auto_increment
data
json
YES
NULL
+-------+--------+------+-----+---------+----------------+
2 rows in set (0.02 sec)
创建json数据类型的表,json字段不能包含默认值:
mysql> create table tab_json_01(id bigint not null auto_increment,data json default '{"name":"Dear"}',primary key(id));
ERROR 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 'data' can't have a default value
备注：json数据类型的列不能有默认值.2.向json数据类型中插入数据mysql> insert into tab_json values (null,'{"name":"david","address":"Beijing","Tel":"132223232444"}');
Query OK, 1 row affected (0.08 sec)
mysql> insert into tab_json values (null,'{"name":"Mike","address":"Guangzhou","Tel":"13390989765"}');
Query OK, 1 row affected (0.01 sec)
mysql> insert into tab_json values (null,'Jack');
ERROR 3140 (22032): Invalid JSON text: "Invalid value." at position 0 in value for column 'tab_json.data'
mysql> insert into tab_json values (null,'{"names":"Smith","names":"Nancy"}');
#插入的时候有多个names这个key
Query OK, 1 row affected (0.00 sec)
备注：auto_increment字段通过插入null值，就能实现值的自动增长。json数据类型的类有自动检查插入的值是否是json类型的，如果插入的是json类型以外的，就会报错，也就是说，必须要插入json类型的值。3.查询json表中的内容mysql> select * from tab_json;
+----+----------------------------------------------------------------+
id
data
+----+----------------------------------------------------------------+
1
{"Tel": "132223232444", "name": "david", "address": "Beijing"}
2
{"Tel": "13390989765", "name": "Mike", "address": "Guangzhou"}
3
{"names": "Smith"}
#在向json列插入值的时候同样执行标准化,即会将重复的key只保留第一个,即多个names的时候,也只是第一个被保留
+----+----------------------------------------------------------------+
3 rows in set (0.00 sec)
备注：查询到data字段的值是json对象的值.4.获取json数据类型中某个字段的值mysql> select json_extract(data,'$.name'),json_extract(data,'$.address') from tab_json;
+-----------------------------+--------------------------------+
json_extract(data,'$.name')
json_extract(data,'$.address')
+-----------------------------+--------------------------------+
"david"
"Beijing"
"Mike"
"Guangzhou"
NULL
NULL
+-----------------------------+--------------------------------+
3 rows in set (0.00 sec)
备注:查询出来的就是key，name和address的对应的value.
在试图将json数据存入mysql时，出现了错误You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near
总结原因是由于json数据没有用引号的问题,并且必须是单引号原代码insert into `food_info` (`food`,`difficult`)
VALUES ([\"鸡胸肉:500克\", \"奥尔良腌肉料:30克\"] ,
普通 )
修改后insert into `food_info` (`food`,`difficult`)
VALUES ('[\"鸡胸肉:500克\", \"奥尔良腌肉料:30克\"]' ,
'普通')
如果是通过变量插入数据insert into `food_info` (`food`,`difficult`) VALUES ('%s') % value_str
"insert into hello ('food_info') (`food`,`difficult`) VALUES ('{value}')".format(value=value)
参考文章https://www.jb51.net/article/149024.htm

我要回帖

更多关于 怎么将json数据存到数据库中 的文章

 

随机推荐