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

最新推荐文章于 2024-06-09 20:54:03 发布
最新推荐文章于 2024-06-09 20:54:03 发布
阅读量5.3w 收藏 24
点赞数 3
分类专栏： mysql 数据库 文章标签： MySQL 解析json字符串

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

4、我的例子：

table tb_evaluate_class：有一个字段如下：

`live_system_shortphrases` varchar(500) NOT NULL DEFAULT '' COMMENT '评价系统快捷短语'

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.00 sec)

 

mysql> select '声音异常' as exception_name,case when locate('声音异常',JSON_EXTRACT(live_system_shortphrases,'$.content')) >0 then 1 else 0 end num from tb_evaluate_class;

ERROR 3141 (22032): Unknown error 3141

相关原因分析：

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

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

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

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函数的使用规则

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

Data_IT_Farmer
关注
  • 3
    点赞
  • 踩
  • 24
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
java字符串常用操作方法(查找、截取、分割)
08-27
Java 字符串常用操作方法（查找、截取、分割） Java 字符串是 Java 编程语言中最基本也是最常用的数据类型之一。字符串操作是 Java 编程中最基本的操作之一，对于任何 Java 开发者来说都是必备的技能。本文将为大家...
mysql怎么解析json字符串_mysql解析json字符串
热门推荐
weixin_36155936的博客
01-19 2万+
很多时候，我们需要在sql里面直接解析json字符串。这里针对mysql5.7版本的分水岭进行区分。1.对于mysql5.7以上版本使用mysql的内置函数JSON_EXTRACT(column, '$.key')，这个函数有两个参数，第一个参数column代表json列的列名；第二个参数key代表json字符串中的某一个key。SELECT JSON_EXTRACT('{"priceTag":"...
mysql 解析json字符串、数组字符串、json数组字符串
qq_41018861的博客
05-16 5552
这是目前笔者使用JSON_EXTRACT函数的一些浅见，欢迎大家在评论区，交流经验和问题。
Invalid JSON text:“Invalid value.“ at position 0 in value for column ‘user.info
最新发布
Yjx的博客
06-09 525
传输的字段类型与数据库当中的不对应产生报错，传输正确的即可~
mysql处理json格式的字段，一文搞懂mysql解析json数据
秃了也弱了
08-25 1万+
JSON 数据类型是 MySQL 5.7.8 开始支持的。在此之前，只能通过字符类型（CHAR，VARCHAR 或 TEXT ）来保存 JSON 文档。 MySQL 8.0版本中增加了对JSON类型的索引支持。可以使用CREATE INDEX语句创建JSON类型的索引，提高JSON类型数据的查询效率。 存储JSON文档所需的空间与存储LONGBLOB或LONGTEXT所需的空间大致相同。 在MySQL 8.0.13之前，JSON列不能有非空的默认值。 JSON 类型比较适合存储一些列不固定、修改较少
mysql解析json_Mysql解析json字符串/数组
weixin_36042436的博客
01-18 1591
1 Mysql解析json字符串解决方法：JSON_EXTRACT(原字段，'$.json字段名')执行SQL：SELECT JSON_EXTRACT( t.result,'$.row'),JSON_EXTRACT( t.result,'$.value'),JSON_EXTRACT( t.result,'$.criteria')from t.test t查询结果：2mysql解析json数组示...
Mysql5.7 + 查询并解析json数据方法
stuild stuil 的博客
09-02 5350
Mysql5.7 + 查询并解析json数据方法
Mysql字符串字段判断是否包含某个字符串的2种方法
12-15
在MySQL数据库中，处理字符串字段的数据操作是常见的需求。这里我们关注的是如何判断字符串字段是否包含特定的子字符串。在给定的示例中，我们有一个名为`users`的表，其中`emails`字段存储了以逗号分隔的电子邮件...
MySQL截取和拆分字符串函数用法示例
09-10
本篇文章将深入探讨两个常用的字符串处理函数：`SUBSTRING` 和 `SUBSTRING_INDEX`，它们在数据提取和解析方面发挥着重要作用。 ### 1. `SUBSTRING` 函数 `SUBSTRING` 函数用于从字符串中截取指定长度的部分。其...
Python字符串中查找子串小技巧
09-22
在Python编程语言中，处理字符串是一项常见的任务，而查找子串是其中的关键操作。本文将深入探讨Python中查找子串的几种方法，帮助你更好地理解和优化你的代码。 首先，最直观的方法是通过循环来判断子串是否存在于...
MySQL 处理JSON字符串
C'mon的博客
02-01 2163
现在很多数据会以json格式存储，如果你还在用like查询json字符串，那你就OUT了， MySQL从5.6版本就开始支持json字符串查询了~下面来看看MySQL中支持的json处理函数吧！MySQL支持定义的原生JSON数据类型 由RFC 7159支持高效访问JSON数据 （JavaScript Object Notation）文档。的JSON数据类型提供了这些优势， 在字符串列中存储JSON格式的字符串：自动验证存储在JSON列。无效的文档将生成 错误.优化存储格式。JSON文档存储在。
mysql 解析json字符串
09-09
mysql解析Json字符串插件 安装方法 1、拷贝lib_mysqludf_json_parsing.dll到mysql目录C:\Program Files\MariaDB 5.5\lib\plugin下 2、在数据库中执行 DROP FUNCTION json_get; CREATE FUNCTION json_get RETURNS STRING SONAME 'lib_mysqludf_json_parsing.dll'; 使用方法 SELECT json_get('{"a":1}', 'a') => 1 SELECT json_get('{"a":1}', 'b') => NULL SELECT json_get('[1,2,3]', 2) => 3 SELECT json_get('{"a":[2]}', 'a', 0) => 2
mysql将行数据转为json字符串
weixin_35756892的博客
01-09 4261
你可以使用 MySql 的 JSON 函数来将行转换为 JSON 字符串。例如： SELECT JSON_OBJECT('name', name, 'age', age, 'email', email) FROM users; 这将会从 users 表中选择所有行，并将每一行的数据转换为一个 JSON 对象。对象的键名为 name, age, 和 email，值分别对应于该行中同名的列。 如果你...
MySQL处理Json数据
只是甲的博客
11-08 4432
mysql 处理json数据
ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_extract: “The document is empty
qq_34536921的博客
09-27 3461
Invalid JSON text in argument 1 to function json_extract: "The document is empty." at position 0
MYSQL中解析json格式数据
billxin0621的博客
05-06 4767
MYSQL中解析json格式数据
mysql解析json字符串
qq_42903710的博客
11-06 1654
1.对于mysql5.7以上版本 使用mysql的内置函数JSON_EXTRACT(column, ‘$.key’)，这个函数有两个参数，第一个参数column代表json列的列名；第二个参数key代表json字符串中的某一个key。 SELECT JSON_EXTRACT(’{“priceTag”:“员工/合作关键人”,“priceDiscount”:“90”}’ ‘$.priceDiscoun...
MySQL如何获取JSON串的值
u013851294的博客
11-14 2604
我们使用JSON_EXTRACT()、JSON_UNQUOTE() 这两个函数来操作。
MySQL 8.0的SQL查询JSON返回的数据类型为字符串而非数组
湖北太米网络科技有限公司
08-29 399
在 MySQL 8.0 中，SQL 查询 JSON 返回的数据类型确实是字符串，而不是数组。 这是因为 MySQL 将 JSON 数据存储为字符串，并提供了一些函数和操作符来处理 JSON 数据。 但是，你可以使用内置的 JSO...
c++ 字符串查找子串
11-19
以下是C++中字符串查找子串的例子： ```cpp #include <iostream> #include <string> using namespace std; int main() { string a = "abcdefghigklmn"; string b = "def"; string c = "123"; string::size_type idx; idx = a.find(b); // 在a中查找b if (idx == string::npos) // 不存在 cout << "not found\n"; else // 存在 cout << "found\n"; return 0; } ``` 该程序中，使用了string类的find()函数来查找子串。如果子串存在，则返回子串在原字符串中的起始位置；如果不存在，则返回string::npos。在该程序中，我们定义了三个字符串a、b、c，其中a为原字符串，b为要查找的子串，c为一个不存在于a中的字符串。程序首先在a中查找b，如果存在，则输出“found”；如果不存在，则输出“not found”。
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值