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;