mysql工作中的sql

1.json字段数据更新值

update cms.cms_content set CONTENT = JSON_REPLACE(CONTENT, JSON_UNQUOTE(REPLACE(JSON_SEARCH(CONTENT, "one", "preLabel_120"),".code",".value")), CAST('["1","2","3"]' as JSON)) where id = 10634;

--sql分析

---查询path

JSON_SEARCH(CONTENT, "one", "preLabel_120")

---字符串转换成path

JSON_UNQUOTE()

---替换path的值

JSON_REPLACE()

---转换成JSON

CAST('["1","2","3"]' as JSON)

update cms_content_text set

DETAIL = JSON_REPLACE(DETAIL, '$.preUrl_122', CAST('{}' as JSON) ),

CONTENT = JSON_REPLACE(CONTENT, JSON_UNQUOTE(REPLACE(JSON_SEARCH(CONTENT, 'one', 'preUrl_122'),'.code','.value')), CAST('{}' as JSON))

where id in (select id from cms_content  where tid in (select t.ID from cms_template t where t.TYPE in (2,9)));

select * from  T_NPORTAL_CMS_CONTENT  WHERE 1=1

AND TENANT_ID=1600089311 AND TID IN (15)

AND DETAIL->>'$.preContentStatus_3'='1'

and JSON_CONTAINS(DETAIL->'$.preLabel_120',JSON_ARRAY('456'))

and (JSON_CONTAINS(DETAIL->>'$.preBelongColumnId_3', '10064'))

#AND (JSON_SEARCH(DETAIL->'$.preBelongColumnId_3','one' ,10064) );

更新json中的字段值

update T_NPORTAL_CMS_FIELD set ELEMENT=JSON_SET(ELEMENT, '$[0].sortStatus', 2),RULE=JSON_SET(RULE, '$.sortStatus', 2)

where TYPE=525;


2.批量更新

UPDATE CMS_CONTENT SET DETAIL = CASE ID WHEN 13558 THEN JSON_REPLACE(DETAIL,"$.preLabel_120",CAST('["13552","10373","10347"]' AS JSON)) WHEN 13559 THEN JSON_REPLACE(DETAIL,"$.preLabel_120",CAST('[]' AS JSON)) END, DETAIL_ES = CASE ID WHEN 13558 THEN JSON_REPLACE(DETAIL_ES,"$.es_multi_preLabel_120",CAST('["13552","10373","10347"]' AS JSON)) WHEN 13559 THEN JSON_REPLACE(DETAIL_ES,"$.es_multi_preLabel_120",CAST('[]' AS JSON)) END, CONTENT = CASE ID WHEN 13558 THEN JSON_REPLACE(CONTENT,JSON_UNQUOTE(REPLACE(JSON_SEARCH(CONTENT,"one","preLabel_120"),".code",".value")),CAST('["13552","10373","10347"]' AS JSON)) WHEN 13559 THEN JSON_REPLACE(CONTENT,JSON_UNQUOTE(REPLACE(JSON_SEARCH(CONTENT,"one","preLabel_120"),".code",".value")),CAST('[]' AS JSON)) END, UPDATE_DATE = "2021-01-19 13:48:01.272" WHERE ID IN (13558,13559) AND TENANT_ID = 1600035223


3.插入

从一个表复制数据插入到另外一个表,目标表中任何已存在的行都不会受影响。

  语法:

INSERT INTO table_xxx VALUES();INSERT INTO table_xxx SELECT xxx from table_yyy;

  当表A和表B的表结构一致时,直接插入即可。

insert into A select * from B;

  当表结构不一致时(字段大小、类型都相同)

insert into A(col1, col2) select col1, col2 from B;

4.修改json结构的字段

update t_nportal_cms_field  set RULE =(CASE RULE

WHEN '[]' THEN '{}'

ELSE JSON_EXTRACT(RULE, '$[0]')

END)

where ID=164427

select ID,ELEMENT->'$[0].etype' from t_nportal_cms_field where tid=20364 and TYPE=521;

select ID,JSON_SET(ELEMENT,'$[0].etype',520) from t_nportal_cms_field where tid=20364 and TYPE=520;

SELECT BIZ_ID,SRC_CONTENT->'$."e_date-17"' FROM T_NPORTAL_LOW_CODE_FORM_CONTENT where BIZ_ID=1446757662515847168;

5.json查询

SELECT DETAIL FROM T_NPORTAL_CMS_CONTENT WHERE STATUS=1 AND TID = 5 and TENANT_ID='269778' AND JSON_UNQUOTE(JSON_EXTRACT(DETAIL,CONCAT('$.','pictureFullName_symbol'))) in ('ALvBm8EeQsaZ0LvseY4Ckg.jpg','jYAr432ITraG86DEndKS8w.jpg','yI7kETUbQU-W6NJ5dYYXKQ.jpg');

6.修改字段

-- 修改表结构(修改大字段表的detail字段 es聚合时父子表如果字段一样会有同步问题  子表里面的字段值会被主表的字段覆盖)

ALTER TABLE `T_NPORTAL_CMS_CONTENT_TEXT`

CHANGE COLUMN `DETAIL` `TEXT_DETAIL` json NOT NULL COMMENT '大字段内容json' AFTER `CID`;

7.判断json字段的key是否“”

SELECT ID,
       JSON_KEYS(QUERY_CONTENT)
from T_NPORTAL_LOW_CODE_FORM_CONTENT where JSON_CONTAINS(JSON_KEYS(QUERY_CONTENT), '""')  = 1;

8.正则表达式

## ipv4 与ipv6
select * from T_NPORTAL_IP_REGION_INFORMATION
where IP  REGEXP '(([0-9]|[1-9][0-9]|1[0-9][0-9]|2([0-4][0-9]|5[0-5]))\\.?){4}|(([0-9a-fA-F]){0,4}:?){6}';
## ipv6
select * from T_NPORTAL_IP_REGION_INFORMATION
where IP  REGEXP '(([0-9a-fA-F]){0,4}:){4}';

9.update  case when then end

UPDATE T_NPORTAL_CMS_CONTENT SET RCID = CASE ID WHEN 1622481887637934080 THEN 2 WHEN 1622481887986061312 THEN 2 WHEN 1622484093913141248 THEN 2 WHEN 1621414114493075456 THEN 2 WHEN 1623260342503739392 THEN 2 WHEN 1623260342730231808 THEN 2 WHEN 1623225142071660544 THEN 2 WHEN 1622824842273886208 THEN 2 WHEN 1623139484523171840 THEN 2 END,DETAIL = CASE ID WHEN 1622481887637934080 THEN JSON_SET(DETAIL,"$.preWebShow",2) WHEN 1622481887986061312 THEN JSON_SET(DETAIL,"$.preWebShow",2) WHEN 1622484093913141248 THEN JSON_SET(DETAIL,"$.preWebShow",2) WHEN 1621414114493075456 THEN JSON_SET(DETAIL,"$.preWebShow",2) WHEN 1623260342503739392 THEN JSON_SET(DETAIL,"$.preWebShow",2) WHEN 1623260342730231808 THEN JSON_SET(DETAIL,"$.preWebShow",2) WHEN 1623225142071660544 THEN JSON_SET(DETAIL,"$.preWebShow",2) WHEN 1622824842273886208 THEN JSON_SET(DETAIL,"$.preWebShow",2) WHEN 1623139484523171840 THEN JSON_SET(DETAIL,"$.preWebShow",2) END, DETAIL_ES = CASE ID WHEN 1622481887637934080 THEN JSON_SET(DETAIL_ES,"$.es_integer_preWebShow",2) WHEN 1622481887986061312 THEN JSON_SET(DETAIL_ES,"$.es_integer_preWebShow",2) WHEN 1622484093913141248 THEN JSON_SET(DETAIL_ES,"$.es_integer_preWebShow",2) WHEN 1621414114493075456 THEN JSON_SET(DETAIL_ES,"$.es_integer_preWebShow",2) WHEN 1623260342503739392 THEN JSON_SET(DETAIL_ES,"$.es_integer_preWebShow",2) WHEN 1623260342730231808 THEN JSON_SET(DETAIL_ES,"$.es_integer_preWebShow",2) WHEN 1623225142071660544 THEN JSON_SET(DETAIL_ES,"$.es_integer_preWebShow",2) WHEN 1622824842273886208 THEN JSON_SET(DETAIL_ES,"$.es_integer_preWebShow",2) WHEN 1623139484523171840 THEN JSON_SET(DETAIL_ES,"$.es_integer_preWebShow",2) END, UPDATE_DATE = "2023-02-09 09:06:39.125" WHERE ID IN (1622481887637934080,1622481887986061312,1622484093913141248,1621414114493075456,1623260342503739392,1623260342730231808,1623225142071660544,1622824842273886208,1623139484523171840) AND TENANT_ID = 1700092244 AND TID IN (109)

10.@rownum 与 批量insert

INSERT INTO LOW_CODE_CMS.T_NPORTAL_CMS_FIELD (TENANT_ID, TID, FID, CODE, NAME, STATUS, TYPE, SORT, SYSTAG, ELEMENT,

RULE, CREATE_DATE, UPDATE_DATE, SYSTAG_DATE)

SELECT a.TENANT_ID,

a.tid,

(SELECT t.ID FROM T_NPORTAL_CMS_FIELD t WHERE 1 = 1 ORDER BY t.ID DESC LIMIT 1) +

(@rownum := @rownum + 1) AS FID,

'preInitialVisits',

'浏览量',

4,

529,

0,

1,

'[{"eid": 4, "code": "preInitialVisits", "name": "初始浏览量", "type": "float", "etype": 529, "status": 1, "showStatus": 1, "sortStatus": 2, "searchStatus": 2}]',

'{"enable": 1, "showStatus": 1, "sortStatus": 2, "searchStatus": 2}',

'2022-04-15 11:30:08.156',

'2022-04-15 11:30:08.156',

'2022-04-15 11:30:08.156'

FROM (SELECT t.TENANT_ID, t.tid, t1.TID AS _TID

FROM T_NPORTAL_CMS_TEMPLATE t

LEFT JOIN (SELECT DISTINCT f.TENANT_ID, f.TID

FROM T_NPORTAL_CMS_FIELD f

WHERE f.CODE = 'preInitialVisits' AND f.TYPE = '529') t1

ON t.TENANT_ID = t1.TENANT_ID AND t.TID = t1.TID

WHERE t.TYPE = 104

AND t.TENANT_ID = $tenantId

AND t1.TID IS NULL) a,

(SELECT @rownum := 0) r;

11.修改json

update T_NPORTAL_CMS_CONTENT set DETAIL=JSON_SET(DETAIL, '$.ncjqx',

JSON_REPLACE(JSON_EXTRACT(DETAIL,'$.ncjqx'),

JSON_UNQUOTE(JSON_SEARCH(JSON_EXTRACT(DETAIL,'$.ncjqx'), 'one', 'B')),'F')

),

DETAIL_ES=JSON_SET(DETAIL_ES, '$.es_multi_ncjqx',

JSON_REPLACE(JSON_EXTRACT(DETAIL_ES,'$.es_multi_ncjqx'),

JSON_UNQUOTE(JSON_SEARCH(JSON_EXTRACT(DETAIL_ES,'$.es_multi_ncjqx'), 'one', 'B')),'F')

)

where TENANT_ID=1700092353 and TID=1645964894759686144 AND

JSON_CONTAINS(JSON_EXTRACT(DETAIL,'$.ncjqx'), '"B"');

12.json类型判断

select * from T_NPORTAL_CMS_CONTENT_TEXT where JSON_TYPE(CONTENT) = "ARRAY" \G;

13.json字段in

SELECT *

FROM T_NPORTAL_CMS_CONTENT

WHERE TENANT_ID=311285 and STATUS=1 AND TID = 5

AND JSON_UNQUOTE(JSON_EXTRACT(DETAIL,CONCAT('$.','pictureFullName_symbol'))) in

('y8.jpg','y9.jpg','y10.jpg')


14.JSON_TYPE使用

SELECT DETAIL, ID, TID, CID, RCID, STATUS, TYPE, VERSION, CREATE_DATE, UPDATE_DATE, SYSTAG, SYSTAG_DATE, TOP, SORT

FROM T_NPORTAL_CMS_CONTENT WHERE 1=1

AND TENANT_ID=1700091456 AND TID IN (41032)

AND DETAIL->>'$.text_2PVENH84'='北京路顺鑫加油站有限公司' AND DETAIL->>'$.STATUS'='1' AND ((CASE

JSON_TYPE(JSON_EXTRACT( DETAIL, '$.category_6d5k7017' ))

WHEN 'ARRAY' THEN

JSON_CONTAINS(DETAIL -> '$.category_6d5k7017',JSON_ARRAY( '1136985488309243904' ))

ELSE

JSON_CONTAINS( DETAIL ->> '$.category_6d5k7017', '1136985488309243904' )END) )

ORDER BY TOP DESC, CREATE_DATE DESC LIMIT 0,10;

(0)

相关推荐

  • zerotier简明教程

    最近使用zerotier替换了frp来实现内网穿透,zerotier是一个软交换机,使用zerotier可以让多台内网机器组成一个局域网. 首先要安装 zerotier-one 这个软件包,如果是Ar ...

  • JsonPatch学习

    网址 http://jsonpatch.com/ 参考资料   https://docs.microsoft.com/zh-cn/aspnet/core/web-api/jsonpatch?view= ...

  • MySQL中JSON使用

    文章目录 前言 1.创建表和插入数据: 2.查询json中的使用字段: 3.json科普: 4.mysql中操作json的函数: 4.1 JSON_ARRAY:生成json数组. 4.2 JSON_O ...

  • Excel中通过SQL语句实现多工作表快速合并到主表中,简单高效!

    Excel中通过SQL语句实现多工作表快速合并到主表中,简单高效!

  • MySQL中的SQL Mode及其作用

    与其它数据库不同,MySQL可以运行在不同的SQL Mode下.SQL Mode定义MySQL应该支持什么样的SQL语法,以及它应该执行什么样的数据验证检查.SQL Mode可以设置为一组应做检查的代 ...

  • Excel中利用SQL语句实现工作表数据的实时自动筛选,简单实用!

    Excel中利用SQL语句实现工作表数据的实时自动筛选,简单实用!

  • 工作中发挥影响力的3个策略?

    你为满足工作描述中的行为并按时完成任务而感到自豪吗?这是一个很好的开始,但是如果你想在工作中脱颖而出,不仅仅是完成你的工作要求了. 在当今的就业市场中,许多合格的申请人竞争有限的职位空缺,即使你确实有 ...

  • 工作中,这5种忙不能帮,帮了就害了自己!

    特别喜欢一句话:"聪明是一种天赋,善良是一种选择." 选择善良是一件好事,有选择性地与人为善,则是一件又好又正确的事. 心存良善,更应懂得自保:择善而行,更应懂得把善良用对事给对人 ...

  • 古人在日常生活、工作中,练习“心智系统转换”的高明方法

    "无为"常常被人等同于"不为",其实是一种严重的误解.实际上,"无为"中蕴含着对现代人的工作.生活具有重要指导作用的现实意义. 老子还原了& ...

  • 这些Excel操作技巧,除了最后1个,其他都是工作中常用的

    [温馨提示]亲爱的朋友,阅读之前请您点击[关注],您的支持将是我最大的动力! 今天分享几个简单实用的的Excel操作技巧,几乎天天都用到.如果你是Excel高手,可以忽略这篇教程,毕竟是一些能提高工作 ...

  • 警务工作中的决策

    引言 1 基本原理 1.1 结构 1.2 系统方法 1.3 连贯的决策系统 1.4 情报在决策过程中的作用 1.5 情报在主动性与反应性类别的警务活动中的作用 1.6 决策过程的陷阱 1.7 总结及要 ...

  • 42个产品经理日常工作中常见的英文缩写

    本文将在产品经理工作中常见的42个英文缩写进行了归类:产品文档相关团队相关数据相关广告相关行业.平台相关功能相关开发相关产品文档相关:BRD:Business Requirements Documen ...