澳门新葡萄京娱乐网站-澳门新葡萄京888官网-所有平台

热门关键词: 澳门新葡萄京娱乐网站,澳门新葡萄京888官网

澳门新葡萄京888官网JSON格式IP抓取数据存款和储

作者: MySQL数据库  发布:2019-12-22

前言

【原创文章,转载请注明原文章地址,谢谢!】

需求

因为项目需要,存储字段存储成了JSON格式,在项目中是将查询出来的值通过jackson转成相应的bean进行处理的,觉得不够简单方便。

首先回顾一下JSON的语法规则:

抓取网站多样

MySQL从5.7版本开始就支持JSON格式的数据,操作用起来挺方便的。

数据在键值对中,
数据由逗号分隔,
花括号保存对象,
方括号保存数组。

抓取元素不同

建表

按照最简单的形式,可以用下面的JSON表示:

查询条件不固定

在新建表时字段类型可以直接设置为json类型,比如我们创建一张表:

{"NAME": "Brett", "email": "brett@xxx.com"}

Mysql JSON介绍

mysql> CREATE TABLE `test_user`(`id` INT PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR NOT NULL, `info` JSON);

如何在MySQL中使用JSON类型:

JSON的格式非常简单:名称/键值。之前MySQL版本里面要实现这样的存储,要么用VARCHAR要么用TEXT大文本。 MySQL5.7发布后,专门设计了JSON数据类型以及关于这种类型的检索以及其他函数解析。 为什么JSON的原生支持

json类型字段可以为NULL

新建user表,设置lastlogininfo列为JSON类型。

文档合法性

mysql> INSERT INTO test_user VALUES('xiaoming','{"sex": 1, "age": 18, "nick_name": "小萌"}');
mysql> CREATE TABLE user(id INT PRIMARY KEY, name VARCHAR(20) , lastlogininfo JSON);
Query OK, 0 rows affected (0.27 sec)

在MySQL5.7.7对JSON提供原生类型的支持之前,用户可以用TEXT或者BLOB类型来存储JSON文档。但对于MySQL来说,用户插入的数据只是序列化后的一个普通的字符串,不会对JSON文档本身的语法合法性做检查,文档的合法性需要用户自己保证。在引入新的JSON类型之后,插入语法错误的JSON文档,MySQL会提示错误,并在插入之后做归一化处理,保证每一个键对应一个值。

json类型的字段必须时一个有效的json字符串

向user表插入普通数据与json数据。mysql会对插入的数据进行JSON格式检查,确保其符合JSON格式,若插的是不合法的数据,会出现Invalid JSON text错误。

更有效的访问

可以使用JSON_OBJECT()函数构造json对象:

mysql> INSERT INTO user VALUES(1 ,"lucy",'{"time":"2015-01-01 13:00:00","ip":"
192.168.1.1","result":"fail"}');
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO user VALUES(2 ,"bobo",'{"time":"2015-10-07 06:44:00","ip":"
192.168.1.0","result":"success"}');
Query OK, 1 row affected (0.04 sec)

MySQL 5.7.7+本身提供了很多原生的函数以及路径表达式来方便用户访问JSON数据。例如对于下面的JSON文档: { "a": [ [ 3, 2 ], [ { "c" : "d" }, 1 ] ], "b": { "c" : 6 }, "one potato": 7, "b.c" : 8 }

mysql> INSERT INTO test_user VALUES('xiaohua', JSON_OBJECT;

也可以使用JSON_OBJECT()函数:

用户可以使用 $.a[1][0]获取{ "c" : "d" }, $.a[1]获取[ { "c" : "d" }, 1 ] 还可以使用通配符 * 和 ** 来进行模糊匹配,详见下一段。

使用JSON_ARRAY()函数构造json数组:

mysql> INSERT INTO user VALUES(1 ,"lucy",JSON_OBJECT("time",NOW(),"ip","
192.168.1.1","result","fail"));
Query OK, 1 row affected (0.00 sec)

性能优化

mysql> INSERT INTO test_user VALUES('xiaozhang', JSON_OBJECT("sex", 1, "age", 19, "tag", JSON_ARRAY;

查询name为'lucy'的最后登陆信息。

在MySQL提供JSON原生支持之前,如果用户需要获取或者修改某个JSON文档的键值,需要把TEXT或者BLOB整个字符串读出来反序列化成JSON对象,然后通过各种库函数访问JSON数据。显然这样是非常没有效率的,特别是对较大的文档。而原生JSON的性能,特别是读性能非常好。根据Oracle公司针对200K+数据文档做的性能测试表明,同样的数据用TEXT和JSON类型的查询性能差异达到两个数量级以上,而且用户还可以对经常访问的JSON键值做索引,进一步提升性能。JSON数据操作性能的提升是基于JSON数据本身的存储结构的,下文会进一步介绍。

现在查看test_user表中的数据:

mysql> SELECT lastlogininfo FROM user WHERE name = 'lucy';
+------------------------------------------------------------------------+
| lastlogininfo  |
+------------------------------------------------------------------------+
| {"ip": "192.168.1.1", "time": "2015-01-01 13:00:00", "result": "fail"} |
+------------------------------------------------------------------------+
1 row in set (0.00 sec)

JSON的操作接口及路径表达式

mysql> select * from test_user; +----+-----------+--------------------------------------------+ | id | name | info |+----+-----------+--------------------------------------------+ | 1 | xiaoming | {"age": 18, "sex": 1, "nick_name": "小萌"} | | 2 | xiaohua | {"age": 17, "sex": 0} || 3 | xiaozhang | {"age": 19, "sex": 1, "tag": [3, 5, 90]} | +----+-----------+--------------------------------------------+3 rows in set 

查询最后登陆时间在2015-10-02后的用户。JSON数据使用->操作符,其表达式为:该json列->'$.键'与JSON_EXTRACT(json列 , '$.键')等效使用。如果传入的不是一个有效的键,则返回Empty set。该表达式可以用于SELECT查询列表 ,WHERE/HAVING , ORDER/GROUP BY中,但它不能用于设置值。

JSON的操作接口

查询

表达式 : json列->'$.键'

JSON_APPEND()JSON_ARRAY_INSERT()JSON_UNQUOTE()JSON_ARRAY()JSON_REPLACE()JSON_CONTAINS()JSON_DEPTH()JSON_EXTRACT()JSON_INSERT()JSON_KEYS()JSON_LENGTH()JSON_VALID()JSON_MERGE()JSON_OBJECT()JSON_QUOTE()JSON_REMOVE()JSON_CONTAINS_PATH()JSON_SEARCH()JSON_SET()JSON_TYPE()

表达式: 对象为json列->'$.键', 数组为json列->'$.键[index]'

mysql>  SELECT * FROM user WHERE lastlogininfo ->'$.time' > '2015-10-02';
+-----------------------------------------------------------------------+
| id | name | lastlogininfo|
+-----------------------------------------------------------------------+
|  2 | bobo | {"ip": "192.168.1.0", "time": "2015-10-07 06:44:00", "result": "success"} |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

JSON路径表达式

mysql> select name, info->'$.nick_name', info->'$.sex', info->'$.tag[0]' from test_user; +-----------+---------------------+---------------+------------------+ | name | info->'$.nick_name' | info->'$.sex' | info->'$.tag[0]' | +-----------+---------------------+---------------+------------------+ | xiaoming | "小萌" | 1 | NULL | | xiaohua | NULL | 0 | NULL | | xiaozhang | NULL | 1 | 3 | +-----------+---------------------+---------------+------------------+ 3 rows in set 

等价于 :JSON_EXTRACT(json列 , '$.键')

为了更方便快速的访问JSON的键值,MySQL 5.7.7+提供了新的路径表达式语法支持。前文提到的$.a[1][0]就是路径表达式的一个具体的示例。完整的路径表达式语法为:

等价于:对象为JSON_EXTRACT ,数组为JSON_EXTRACT

mysql>  SELECT * FROM user WHERE JSON_EXTRACT(lastlogininfo,'$.time') > '2015-10-02';
+-----------------------------------------------------------------------+
| id | name | lastlogininfo|
+-----------------------------------------------------------------------+
|  2 | bobo | {"ip": "192.168.1.0", "time": "2015-10-07 06:44:00", "result": "success"} |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

pathExpression>::= scope  [ ( pathLeg )* ]scope::= [ columnReference ] dollarSigncolumnReference::= [ [ databaseIdentifier period  ] tableIdentifier period ] columnIdentifierdatabaseIdentifier::= sqlIdentifiertableIdentifier::= sqlIdentifiercolumnIdentifier::= sqlIdentifierpathLeg::= member | arrayLocation | doubleAsteriskmember::= period ( keyName | asterisk )arrayLocation::= leftBracket ( non-negative-integer | asterisk ) rightBracketkeyName::= ECMAScript-identifier | double-quoted-string-literaldoubleAsterisk::= **

mysql> select name, JSON_EXTRACT, JSON_EXTRACT, JSON_EXTRACT from test_user; +-----------+-----------------------------------+-----------------------------+--------------------------------+ | name | JSON_EXTRACT | JSON_EXTRACT | JSON_EXTRACT | +-----------+-----------------------------------+-----------------------------+--------------------------------+ | xiaoming | "小萌" | 1 | NULL || xiaohua | NULL | 0 | NULL | | xiaozhang | NULL | 1 | 3 | +-----------+-----------------------------------+-----------------------------+--------------------------------+ 3 rows in set 

比较JSON值采用两个级别。第一级是基于JSON类型的比较。如果类型不同,则取决于哪种类型具有更高的优先级。如果是相同的JSON类型,则是第二级,使用该类型的规则来比较。

还是以

不过看到上面"小萌"是带双引号的,这不是我们想要的,可以用JSON_UNQUOTE函数将双引号去掉

下面的列表显示了JSON类型的比较规则,从最高优先级到最低优先级。显示在一行的类型则是具有相同的优先级。

{ "a":[ [3,2], [ { "c" :"d"},1] ], "b":{ "c" :6}, "one potato":7, "b.c" :8}

mysql> select name, JSON_UNQUOTE from test_user where name='xiaoming'; +----------+-----------------------------------+ | name | JSON_UNQUOTE | +----------+-----------------------------------+ | xiaoming | 小萌 | +----------+-----------------------------------+ 1 row in set 
BLOB
BIT
OPAQUE
DATETIME
TIME
DATE
BOOLEAN
ARRAY
OBJECT
STRING
INTEGER, DOUBLE
NULL

$.a[1] 获取的值为 [ { "c" : "d" }, 1 ] $.b.c 获取的值为 6 $."b.c" 获取的值为 8

也可以直接使用操作符->>

使用JSON_TYPE()函数返回指定属性对应的类型名称:

JSON的存储结构及具体实现

mysql> select name, info->>'$.nick_name' from test_user where name='xiaoming';+----------+----------------------+ | name | info->>'$.nick_name' | +----------+----------------------+ | xiaoming | 小萌 | +----------+----------------------+ 1 row in set 
mysql> SELECT JSON_TYPE(lastlogininfo->'$.ip') FROM user;
+----------------------------------+
| JSON_TYPE(lastlogininfo->'$.ip') |
+----------------------------------+
| STRING   |
| STRING   |
+----------------------------------+
2 rows in set (0.00 sec)

在处理JSON时,MySQL使用的utf8mb4字符集,utf8mb4是utf8和ascii的超集。由于历史原因,这里utf8并非是我们常说的UTF-8 Unicode变长编码方案,而是MySQL自身定义的utf8编码方案,最长为三个字节。具体区别非本文重点,请大家自行Google了解。

当然属性也可以作为查询条件

值得一提的是,可以通过虚拟列对JSON类型的指定属性进行快速查询。

MySQL在内存中是以DOM的形式表示JSON文档,而且在MySQL解析某个具体的路径表达式时,只需要反序列化和解析路径上的对象,而且速度极快。要弄清楚MySQL是如何做到这些的,我们就需要了解JSON在硬盘上的存储结构。有个有趣的点是,JSON对象是BLOB的子类,在其基础上做了特化。

mysql> select name, info->>'$.nick_name' from test_user where info->'$.nick_name'='小萌'; +----------+----------------------+ | name | info->>'$.nick_name' | +----------+----------------------+ | xiaoming | 小萌 | +----------+----------------------+ 1 row in set 

创建虚拟列:

JSON的索引

值得一提的是,可以通过虚拟列对JSON类型的指定属性进行快速查询。

mysql> ALTER TABLE user ADD lastloginresult VARCHAR(15)
    -> GENERATED ALWAYS AS (lastlogininfo->'$.result') VIRTUAL;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

现在MySQL不支持对JSON列进行索引,官网文档的说明是:

mysql> ALTER TABLE `test_user` ADD `nick_name` VARCHAR GENERATED ALWAYS AS  VIRTUAL;

使用时和普通类型的列查询是一样的:

JSON columns cannot be indexed. You can work around this restriction by creating an index on a generated column that extracts a scalar value from the JSON column.

注意用操作符->>

mysql> SELECT lastloginresult FROM user WHERE name='lucy';
+-----------------+
| lastloginresult |
+-----------------+
| "fail"  |
+-----------------+
1 row in set (0.00 sec)

虽然不支持直接在JSON列上建索引,但MySQL规定,可以首先使用路径表达式对JSON文档中的标量值建立虚拟列,然后在虚拟列上建立索引。这样用户可以使用表达式对自己感兴趣的键值建立索引。举个具体的例子来说明:

使用时和普通类型的列查询是一样:

这只是一个简单的JSON类型例子,Mysql还提供了许多对JSON类型处理的函数,可以从MySQL的官方网站查看帮助文档:
http://dev.mysql.com/doc/refman/5.7/en/json.html

CREATETABLEfeatures (idINTNOTNULLAUTO_INCREMENT, featureJSONNOTNULL, PRIMARYKEY(id));

mysql> select name,nick_name from test_user where nick_name='小萌'; +----------+-----------+ | name | nick_name | +----------+-----------+ | xiaoming | 小萌 | +----------+-----------+ 1 row in set 

澳门新葡萄京888官网 1

插入它的JSON数据的格式为:

更新

image

{  "type":"Feature",  "properties":{      "TO_ST":"0",      "BLKLOT":"0001001",      "STREET":"UNKNOWN",      "FROM_ST":"0",      "LOT_NUM":"001",      "ST_TYPE":null,      "ODD_EVEN":"E",      "BLOCK_NUM":"0001",      "MAPBLKLOT":"0001001"}}

使用JSON_INSERT()插入新值,但不会覆盖已经存在的值

使用:

mysql> UPDATE test_user SET info = JSON_INSERT(info, '$.sex', 1, '$.nick_name', '小花') where id=2;

mysql> select * from test_user where id=2; +----+---------+--------------------------------------------+-----------+ | id | name | info | nick_name | +----+---------+--------------------------------------------+-----------+ | 2 | xiaohua | {"age": 17, "sex": 0, "nick_name": "小花"} | 小花 | +----+---------+--------------------------------------------+-----------+ 1 row in set 

ALTERTABLEfeaturesADDfeature_streetVARCHAR(30)AS(JSON_UNQUOTE(feature->"$.properties.STREET"));ALTERTABLEfeaturesADDINDEX(feature_street);

使用JSON_SET()插入新值,并覆盖已经存在的值

两个步骤,可以对feature列中properties键值下的STREET键(feature->"$.properties.STREET")创建索引。

mysql> UPDATE test_user SET info = JSON_INSERT(info, '$.sex', 0, '$.nick_name', '小张') where id=3;

mysql> select * from test_user where id=3; +----+-----------+---------------------------------------------------------------+-----------+ | id | name | info | nick_name | +----+-----------+---------------------------------------------------------------+-----------+ | 3 | xiaozhang | {"age": 19, "sex": 1, "tag": [3, 5, 90], "nick_name": "小张"} | 小张 | +----+-----------+---------------------------------------------------------------+-----------+ 1 row in set 

其中,feature_street列就是新添加的虚拟列。之所以取名虚拟列,是因为与它对应的还有一个存储列(stored column)。它们最大的区别为虚拟列只修改数据库的metadata,并不会存储真实的数据在硬盘上,读取过程也是实时计算的方式;而存储列会把表达式的列存储在硬盘上。两者使用的场景不一样,默认情况下通过表达式生成的列为虚拟列。

使用JSON_REPLACE()只替换存在的值

这样虚拟列的添加和删除都会非常快,而在虚拟列上建立索引跟传统的建立索引的方式并没有区别,会提高虚拟列读取的性能,减慢整体插入的性能。虚拟列的特性结合JSON的路径表达式,可以方便的为用户提供高效的键值索引功能。

mysql> UPDATE test_user SET info = JSON_REPLACE(info, '$.sex', 1, '$.tag', '[1,2,3]') where id=2;

mysql> select * from test_user where id=2; +----+---------+--------------------------------------------+-----------+ | id | name | info | nick_name | +----+---------+--------------------------------------------+-----------+ | 2 | xiaohua | {"age": 17, "sex": 1, "nick_name": "小花"} | 小花 | +----+---------+--------------------------------------------+-----------+ 1 row in set 

JSON比较与排序

可以看到tag没有更新进去

JSON值可以使用=, <, <=, >, >=, <>, !=, <=>等操作符,BETWEEN, IN,GREATEST, LEAST等操作符现在还不支持。JSON值使用的两级排序规则,第一级基于JSON的类型,类型不同的使用每个类型特有的排序规则。

删除

实践

使用JSON_REMOVE()删除JSON元素

创建表

mysql> UPDATE test_user SET info = JSON_REMOVE(info, '$.sex', '$.tag') where id=1;

mysql> select * from test_user where id=1; +----+----------+----------------------------------+-----------+ | id | name | info | nick_name | +----+----------+----------------------------------+-----------+ | 1 | xiaoming | {"age": 18, "nick_name": "小萌"} | 小萌 | +----+----------+----------------------------------+-----------+ 1 row in set 

CREATETABLE`user`(`uid`int(11)NOTNULLAUTO_INCREMENT,`info`jsonDEFAULTNULL, #注意desc字段类型为jsonPRIMARYKEY(`uid`))ENGINE=InnoDBAUTO_INCREMENT=3DEFAULTCHARSET=utf8;

总结

插入数据

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对脚本之家的支持。

INSERT  INTO `user`(`uid`,`info`) VALUES (1,'{"mail":"jiangchengyao@gmail.com","name":"David","address":"Shangahai"}'),(2,'{"mail":"amy@gmail.com","name":"Amy"}');

普通查询

selectuid,infofromuserwhereinfolike'%mail%'

key查询

SELECTuid,json_keys(info)as"keys"FROMuser;

value查询

SELECTuid,json_extract(info,'$.mail')AS'mail',json_extract(info,'$.name')AS'name'FROMUSER;

条件查询

json表里面有个content字段数据如下面的Json格式 {"eq":{"f_1360040399":"admin","f_3038116851":"20","f_318208994":"admin@localhost.com"}}

需要对其中的f开头的Json key值所对的value进行模糊查询,方法如下:

select * from table where content->'$.eq.f_1360040399' like '%min%'

SELECTuid,json_extract(info,'$.mail')AS'mail',json_extract(info,'$.name')AS'name'FROMUSERwhereinfo->'$.name'="Amy";

SELECTuid,json_extract(info,'$.mail')AS'mail',json_extract(info,'$.name')AS'name'FROMUSERwhereinfo->'$.name'like"%A%";

性能测试

100万条数据检索

创建随机字符串

select concat('{"mail":"',rand_string(15),'@gmail.com","name":"',rand_string(20),'","address":"',rand_string(30),'","company":"',rand_string(30),'"}') from dual ;

造100万数据

DELIMITER $$USE `platform`$$DROPPROCEDUREIFEXISTS`autoinsert`$$CREATEPROCEDURE`autoinsert`(INIP_NUM INT)BEGINDECLAREiIPINTDEFAULT0 ;DECLARE json varchar(255);WHILE(iIP < IP_NUM)DOSETjson =concat('{"mail": "',rand_string(15),'@gmail.com", "name":"',rand_string(20),'", "address": "',rand_string(30),'", "company": "',rand_string(30),'"}');  insertintouser(info)values (json);SETiIP = iIP+1;ENDWHILE;END$$DELIMITER ;call `autoinsert`(1000000);

条件查询

SELECTuid,json_extract(info,'$.mail')AS'mail',json_extract(info,'$.name')AS'name'FROMUSERwhereinfo->'$.name'like"%D%";

创建索引

ALTERTABLEuserADDnameVARCHAR(50)AS(JSON_UNQUOTE(info->"$.name"));ALTERTABLEuserADDINDEX(name);

条件查询(同上)

DurationTime统计表

数据量未加索引添加索引

110万0.005s0.005s

09:58:36SELECT uid,json_extract(info,'$.mail')AS'mail',json_extract(info,'$.name')AS'name'FROM USER where info->'$.name'like"%D%"277070row(s)returned0.005sec /2.416sec10:09:05SELECT uid,json_extract(info,'$.mail')AS'mail',json_extract(info,'$.name')AS'name'FROM USER where info->'$.name'like"%A%"276982row(s)returned0.002sec /1.858sec

结论

本文介绍了Mysql JSON数据类型的特性,并实践测试JSON效率。

Json格式的灵活性满足需求中

抓取网站多样

不同网站对应不同json格式

抓取元素不同

查询条件不固定

查询条件可以先将json_keys(json)返回前端动态生成查询条件

100万数据量添加索引效果不明显 ,大数据量待测试

参考资料

生成 JSON 值的函数

MySQL5.7 JSON实现简介

MySQL 5.7 新特性 JSON 的创建,插入,查询,更新

mysql存储过程中(varchar)变量的问题

mysql concat 字符串拼接命令

关于mysql的Fetch Time 和 Duration Time

mysql 索引( mysql index )

本文由澳门新葡萄京娱乐网站发布于MySQL数据库,转载请注明出处:澳门新葡萄京888官网JSON格式IP抓取数据存款和储

关键词: