mysql解析json字符串，字符串查找，子串截取和字段类型转换相关总结

分手后的思念是犯贱 2022-03-25 13:53 2035阅读 0赞

## **mysql解析json字符串，字符串查找，子串截取和字段类型转换相关总结** ##

很多时候，我们需要在sql里面直接解析json字符串。这里针对mysql5.7版本的分水岭进行区分。

**1.对于mysql5.7以上版本**

使用mysql的内置函数JSON\_EXTRACT(column, '$.key')，这个函数有两个参数，第一个参数column代表json列的列名；第二个参数key代表json字符串中的某一个key。

SELECT JSON\_EXTRACT('\{"priceTag":"员工/合作关键人","priceDiscount":"90"\}', '$.priceDiscount') AS '定价折扣';

对于简单的json字符串肯定是可以解析成功，但是对于嵌套数组的没试过。

**2.对于mysql5.7以下版本**

只能充分发挥已有函数的功能去截取实现，无论实现方式是存储过程还是简单的sql语句，其原理都是一样的。

第一步，将所有的花括号的闭括号'\}'替换成英文逗号','；第二步，获取key的坐标keyIndex和长度keyLength；第三步，获取以key为起点，第一个英文逗号','的坐标symbolIndex；第四步，使用substring截取字符串SUBSTRING(targetJsonStr, keyIndex + keyLength, symbolIndex - keyIndex - keyLength)；第五步，使用replace将双引号'"'替换成空字符串''，完工。

示例：从\{"priceTag": "员工/合作关键人","priceDiscount": "90"\}中获取priceDiscount的值。

> SELECT  
>     REPLACE(  
>         SUBSTRING(  
>             REPLACE(  
>                 '\{"priceTag":"员工/合作关键人","priceDiscount":"90"\}' ,  
>                 '\}' ,  
>                 ','  
>             ) ,  
>             LOCATE(  
>                 'priceDiscount":' ,  
>                 REPLACE(  
>                     '\{"priceTag":"员工/合作关键人","priceDiscount":"90"\}' ,  
>                     '\}' ,  
>                     ','  
>                 )  
>             ) + CHAR\_LENGTH('priceDiscount":') ,  
>             LOCATE(  
>                 ',' ,  
>                 REPLACE(  
>                     '\{"priceTag":"员工/合作关键人","priceDiscount":"90"\}' ,  
>                     '\}' ,  
>                     ','  
>                 ) ,  
>                 LOCATE(  
>                     'priceDiscount":' ,  
>                     REPLACE(  
>                         '\{"priceTag":"员工/合作关键人","priceDiscount":"90"\}' ,  
>                         '\}' ,  
>                         ','  
>                     )  
>                 ) + CHAR\_LENGTH('priceDiscount":')  
>             ) -(  
>                 LOCATE(  
>                     'priceDiscount":' ,  
>                     REPLACE(  
>                         '\{"priceTag":"员工/合作关键人","priceDiscount":"90"\}' ,  
>                         '\}' ,  
>                         ','  
>                     )  
>                 ) + CHAR\_LENGTH('priceDiscount":')  
>             )  
>         ) ,  
>         '"' ,  
>         ''  
>     ) AS '定价折扣';

参考：https://www.jianshu.com/p/513acedf436d

**3、 其他相关函数如下：**

方法罗列：

分类 函数 描述

**创建json**

json\_array 创建json数组

json\_object 创建json对象

json\_quote 将json转成json字符串类型

**查询json **

json\_contains 判断是否包含某个json值

json\_contains\_path 判断某个路径下是否包json值

json\_extract 提取json值

column->path json\_extract的简洁写法，MySQL 5.7.9开始支持

column->>path json\_unquote(column -> path)的简洁写法

json\_keys 提取json中的键值为json数组

json\_search 按给定字符串关键字搜索json，返回匹配的路径

**修改json **

json\_append 废弃，MySQL 5.7.9开始改名为json\_array\_append

json\_array\_append 末尾添加数组元素，如果原有值是数值或json对 象，则转成数组后，再添加元素

json\_array\_insert 插入数组元素

json\_insert 插入值（插入新值，但不替换已经存在的旧值）

json\_merge 合并json数组或对象

json\_remove 删除json数据

json\_replace 替换值（只替换已经存在的旧值）

json\_set 设置值（替换旧值，并插入不存在的新值）

json\_unquote 去除json字符串的引号，将值转成string类型

**返回json属性 **

json\_depth 返回json文档的最大深度

json\_length 返回json文档的长度

json\_type 返回json值得类型

json\_valid 判断是否为合法json文档

参考：[https://blog.csdn.net/jiangyu1013/article/details/78917425][https_blog.csdn.net_jiangyu1013_article_details_78917425]

**4、我的例子：**

table tb\_evaluate\_class：有一个字段如下：

\`live\_system\_shortphrases\` varchar(500) NOT NULL DEFAULT '' COMMENT '评价系统快捷短语'

**相关原因分析：**

> mysql> SELECT CAST('NULL' AS JSON); ERROR 3141 (22032): Invalid JSON text in argument 1 to function cast\_as\_json: "Invalid value." at position 0 in 'NULL'.
> 
> mysql> SELECT CAST('' AS JSON);
> 
> ERROR 3141 (22032): Invalid JSON text in argument 1 to function cast\_as\_json: "The document is empty." at position 0 in ''.
> 
> mysql> SELECT CAST('\{\]' AS JSON);
> 
> ERROR 3141 (22032): Invalid JSON text in argument 1 to function cast\_as\_json: "Missing a name for object member." at position 1 in '\{\]'.
> 
> Note that many keywords that might be valid in other environments, like NaN, Infinity, javascript built-in constructor fields like Number.

**解决办法，去出长度为0的字符串即可：**

> mysql> select '声音异常' as exception\_name,case when locate('声音异常',JSON\_EXTRACT(cast(live\_system\_shortphrases as json),'$.content')) >0 then 1 else 0 end num from hb\_evaluate\_class where length(live\_system\_shortphrases)!=0 limit 3;
> 
> \+----------------+-----+
> 
> | exception\_name | num |
> 
> \+----------------+-----+
> 
> | 声音异常 | 1 |
> 
> | 声音异常 | 0 |
> 
> | 声音异常 | 0 |
> 
> \+----------------+-----+
> 
> 3 rows in set (0.00 sec)

参考：[https://dev.mysql.com/doc/refman/5.7/en/json.html][https_dev.mysql.com_doc_refman_5.7_en_json.html]

**5、扩展：Mysql json字符串解析成对应字段**

**对于mysql5.7以上版本,使用mysql的内置函数JSON\_EXTRACT(column, '$.key')，这个函数有两个参数，第一个参数column代表json列的列名；第二个参数key代表json字符串中的某一个key。**

> mysql> select live\_system\_shortphrases from tb\_evaluate\_class limit 2;
> 
> \+---------------------------------------------------------------------+
> 
> | live\_system\_shortphrases |
> 
> \+---------------------------------------------------------------------+
> 
> | \{"content":\["\\u5361\\u987f\\u9ed1\\u5c4f","\\u58f0\\u97f3\\u5f02\\u5e38"\]\} |
> 
> | \{"content":\[\]\} |
> 
> \+---------------------------------------------------------------------+
> 
> 2 rows in set (0.01 sec)
> 
> mysql> select JSON\_EXTRACT(live\_system\_shortphrases,'$.content') as josn\_str from tb\_evaluate\_class limit 2;
> 
> \+----------------------------------+
> 
> | josn\_str |
> 
> \+----------------------------------+
> 
> | \["卡顿黑屏", "声音异常"\] |
> 
> | \[\] |
> 
> \+----------------------------------+
> 
> 2 rows in set (0.00 sec)
> 
> **Mysql json字符串进行替换**
> 
> mysql> select REPLACE(JSON\_EXTRACT(live\_system\_shortphrases,'$.content'),'异常',' exception') as replace\_str from tb\_evaluate\_class limit 2;
> 
> \+--------------------------------------+
> 
> | replace\_str |
> 
> \+--------------------------------------+
> 
> | \["卡顿黑屏", "声音 exception"\] |
> 
> | \[\] |
> 
> \+--------------------------------------+
> 
> 2 rows in set (0.00 sec)

**6、MySQL字符查找和截取：**

mysql 查找字符串位置 instr()与LOCATE()字符串查找函数

[https://blog.csdn.net/weixin\_35751376/article/details/60583967][https_blog.csdn.net_weixin_35751376_article_details_60583967]

**NSTR(str,substr) **

返回字符串 str 中子字符串的第一个出现位置。这和LOCATE()的双参数形式相同，除非参数的顺序被颠倒。

> mysql> SELECT INSTR('foobarbar', 'bar');
> 
> \+---------------------------+
> 
> | INSTR('foobarbar', 'bar') |
> 
> \+---------------------------+
> 
> | 4 |
> 
> \+---------------------------+
> 
> 1 row in set (0.00 sec)
> 
>  
> 
> mysql> SELECT INSTR('xbar', 'foobar');
> 
> \+-------------------------+
> 
> | INSTR('xbar', 'foobar') |
> 
> \+-------------------------+
> 
> | 0 |
> 
> \+-------------------------+
> 
> 1 row in set (0.00 sec)
> 
> mysql> SELECT INSTR('foobarbar', 'BAr');
> 
> \+---------------------------+
> 
> | INSTR('foobarbar', 'BAr') |
> 
> \+---------------------------+
> 
> | 4 |
> 
> \+---------------------------+
> 
> 1 row in set (0.00 sec)
> 
>

**LOCATE(substr,str) , LOCATE(substr,str,pos) **

第一个语法返回字符串 str中子字符串substr的第一个出现位置。第二个语法返回字符串 str中子字符串substr的第一个出现位置, 起始位置在pos。如若substr 不在str中，则返回值为0。

> mysql> SELECT LOCATE('bar', 'foobarbar');
> 
> \+----------------------------+
> 
> | LOCATE('bar', 'foobarbar') |
> 
> \+----------------------------+
> 
> | 4 |
> 
> \+----------------------------+
> 
> 1 row in set (0.00 sec)
> 
>  
> 
> mysql> SELECT LOCATE('xbar', 'foobar');
> 
> \+--------------------------+
> 
> | LOCATE('xbar', 'foobar') |
> 
> \+--------------------------+
> 
> | 0 |
> 
> \+--------------------------+
> 
> 1 row in set (0.00 sec)
> 
>  
> 
> mysql> SELECT LOCATE('bar', 'foobarbar',5);
> 
> \+------------------------------+
> 
> | LOCATE('bar', 'foobarbar',5) |
> 
> \+------------------------------+
> 
> | 7 |
> 
> \+------------------------------+
> 
> 1 row in set (0.00 sec)
> 
> mysql> SELECT LOCATE('BaR', 'foobarbar',5);
> 
> \+------------------------------+
> 
> | LOCATE('BaR', 'foobarbar',5) |
> 
> \+------------------------------+
> 
> | 7 |
> 
> \+------------------------------+
> 
> 1 row in set (0.00 sec)
> 
> 这个函数支持多字节字元，并且只有当至少有一个参数是二进制字符串时区分大小写。
> 
>

参考：[https://www.cnblogs.com/mojiexiaolong/p/6979617.html][https_www.cnblogs.com_mojiexiaolong_p_6979617.html]

**mysql 字符串截取函数--substr()**

> 1.用法：
> 
> substr(string string,num start,num length);
> 
> string为字符串；
> 
> start为起始位置；
> 
> length为长度。
> 
> 2.区别：
> 
> mysql中的start是从1开始的，而hibernate中的start是从0开始的。
> 
>  
> 
> mysql> select substr('123456789',1,3);
> 
> \+-------------------------+
> 
> | substr('123456789',1,3) |
> 
> \+-------------------------+
> 
> | 123 |
> 
> \+-------------------------+
> 
> 1 row in set (0.00 sec)
> 
>  
> 
> mysql> select substr('123456789',4,3);
> 
> \+-------------------------+
> 
> | substr('123456789',4,3) |
> 
> \+-------------------------+
> 
> | 456 |
> 
> \+-------------------------+
> 
> 1 row in set (0.01 sec)
> 
> 利用截取的子串和数字比较大小：
> 
> mysql> select substr('123456789',4,3)>123;
> 
> \+-----------------------------+
> 
> | substr('123456789',4,3)>123 |
> 
> \+-----------------------------+
> 
> | 1 |
> 
> \+-----------------------------+
> 
> 1 row in set (0.00 sec)
> 
>  
> 
> mysql> select substr('123456789',4,3)>987;
> 
> \+-----------------------------+
> 
> | substr('123456789',4,3)>987 |
> 
> \+-----------------------------+
> 
> | 0 |
> 
> \+-----------------------------+
> 
> 1 row in set (0.00 sec)

7、[MYSQL中，字段类型转换-CAST函数的使用规则][MYSQL_-CAST]

CAST函数语法规则是：Cast(字段名 as 转换的类型 )，其中类型可以为：

CHAR\[(N)\] 字符型

DATE 日期型

DATETIME 日期和时间型

DECIMAL float型

SIGNED int

TIME 时间型

> mysql> select cast('2015-11-03 15:31:26' as char) as date;
> 
> \+---------------------+
> 
> | date |
> 
> \+---------------------+
> 
> | 2015-11-03 15:31:26 |
> 
> \+---------------------+
> 
> 1 row in set (0.00 sec)
> 
>  
> 
> mysql> select cast('2015-11-03 15:31:26' as datetime) as date;
> 
> \+---------------------+
> 
> | date |
> 
> \+---------------------+
> 
> | 2015-11-03 15:31:26 |
> 
> \+---------------------+
> 
> 1 row in set (0.00 sec)
> 
>  
> 
> mysql> select cast('2015-11-03 15:31:26' as date) as date;
> 
> \+------------+
> 
> | date |
> 
> \+------------+
> 
> | 2015-11-03 |
> 
> \+------------+
> 
> 1 row in set (0.00 sec)
> 
>  
> 
> mysql> select cast('2015-11-03 15:31:26' as time) as date;
> 
> \+----------+
> 
> | date |
> 
> \+----------+
> 
> | 15:31:26 |
> 
> \+----------+
> 
> 1 row in set (0.00 sec)
> 
> 这里date对应日期，time对应时间
> 
> mysql> select cast('30' as decimal(10, 2)) as num;
> 
> \+-------+
> 
> | num |
> 
> \+-------+
> 
> | 30.00 |
> 
> \+-------+
> 
> 1 row in set (0.00 sec)

解释：decimal 数据类型最多可存储 38 个数字,所有数字都能够放到小数点的右边.decimal 数据类型存储了一个准确（精确）的数字表达法；不存储值的近似值.其中10是小数点左边和右边的数字个数之和(不包括小数点),2代表小数点右边的小数位数或数字个数.decimal(10,2)可以存储8位整数2位小数的数字.

**此外，下面将对mysql中cast（）和convert（）的用法讲解**

一、在[mysql][]操作中我们经常需要对数据进行类型转换。此时我们应该使用的是cast()或convert()。

二、两者的对比

相同点：都是进行数据类型转换，实现的功能基本等同

不同点：两者的语法不同，cast(value as type) 、 convert(value,type)

三、所支持的类型如下：

二进制，同带binary前缀的效果 : BINARY

字符型，可带参数 : CHAR()

日期 : DATE

时间: TIME

日期时间型 : DATETIME

浮点数 : DECIMAL

整数 : SIGNED

无符号整数 : UNSIGNED

参考：[https://www.cnblogs.com/yangchunze/p/6667502.html][MYSQL_-CAST]

[https_blog.csdn.net_jiangyu1013_article_details_78917425]: https://blog.csdn.net/jiangyu1013/article/details/78917425
[https_dev.mysql.com_doc_refman_5.7_en_json.html]: https://dev.mysql.com/doc/refman/5.7/en/json.html
[https_blog.csdn.net_weixin_35751376_article_details_60583967]: https://blog.csdn.net/weixin_35751376/article/details/60583967
[https_www.cnblogs.com_mojiexiaolong_p_6979617.html]: https://www.cnblogs.com/mojiexiaolong/p/6979617.html
[MYSQL_-CAST]: https://www.cnblogs.com/yangchunze/p/6667502.html
[mysql]: https://www.2cto.com/database/MySQL/