# IFNULL 函数
select IFNULL(a,b) from c
如果a为null 则返回b
# in 查询后 按in里的顺序排序
`WHERE id IN($id) ORDER BY find_in_set(id ,'{$id}')`
# concat 和concat_ws 的区别
concat如果有一个字符串为null 返回null concat_ws 会忽略null
所以逗号分隔这种又想去掉null 空 做一次转换即可
https://www.cnblogs.com/shuzf/p/9933761.html
> ps json 中的null值 和字符串中的null值不等同，实用函数前需要判断类型做转换
set @code = if(json_type(@code) != 'NULL', json_unquote(@code), null);
# 5.7 json 字段的使用
> [ json 函数](https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html)
## 创建表时指定json列 和json虚拟列
```
`CREATE TABLE `nfjk`.`Untitled` (
`id` int(21) UNSIGNED NOT NULL AUTO_INCREMENT,
`apply_no` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '申请号',
`apply_date` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci GENERATED ALWAYS AS (replace(json_extract(`zhuluxiangmuxinxi`,'$."申请日"'),'"','')) STORED COMMENT '申请日' NULL,
`date` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '更新日期',
`zhuluxiangmuxinxi` json NULL COMMENT '著录项目信息',
`shenqingren` json NULL COMMENT '申请人',
`famingren` json NULL COMMENT '发明人/设计人',
`lianxiren` json NULL COMMENT '联系人',
`dailiqingkuang` json NULL COMMENT '代理情况',
`youxianquan` json NULL COMMENT '优先权',
`shenqingguojijieduan` json NULL COMMENT '申请国际阶段',
`zhuluxiangmubiangeng` json NULL COMMENT '著录项目变更',
`yingjiaofeixinxi` json NULL COMMENT '应缴费信息',
`yijiaofeixinxi` json NULL COMMENT '已缴费信息',
`tuifeixinxi` json NULL COMMENT '退费信息',
`zhinajinxinxi` json NULL COMMENT '滞纳金信息',
`shoujufawenxinxi` json NULL COMMENT '收据发文信息',
PRIMARY KEY (`id`) USING BTREE,
INDEX `apply_no`(`apply_no`) USING BTREE,
INDEX `apply_date`(`apply_date`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;`
```
## 修改表加虚拟列 和索引
```
ALTER TABLE `nfjk`.`cpquery_result`
ADD COLUMN `apply_date` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci GENERATED ALWAYS AS (replace(json_extract(`zhuluxiangmuxinxi`,'$."申请日"'),'"','')) STORED COMMENT '申请日' NULL AFTER `shoujufawenxinxi`,
ADD INDEX `apply_date`(`apply_date`);
```
## 判断json数组或对象 是否包含某值
目标格式
`["代理人撰稿","代理代交","自备稿件","自报代交"]`
~~~
JSON_CONTAINS ( pub_auth, '\"自备稿件\"', '$' ) AS allow_self_doc,
JSON_CONTAINS ( pub_auth, '\"自报代交\"', '$' ) AS allow_self_identity
~~~
返回 存在1 不存在 0
## 提取json数组对象里的值为独立字段
data.attachment 是个数组，每个数组有remark 五书独立的key 同时对应不同的附件id， 找到中文key对应的路径后，替换remark为attachment_id 获取同行的值
[{"remark": "权利要求书", "attachment_id": "5551"}]
~~~
SELECT DATA
->> "$.attachment[*]",
json_unquote(replace(replace(json_search(data->>"$.attachment", "all", '权利要求书'), 'remark', 'attachment_id'), '$', '$.attachment')),
json_extract(data, json_unquote(replace(replace(json_search(data->>"$.attachment", "all", '权利要求书'), 'remark', 'attachment_id'), '$', '$.attachment')))
FROM
dp_pizhi_case_files
~~~
## json 数组里插入值
~~~
select json_array_insert(raw3, CONCAT('$[',j,']'), cast( update_to_plat(kk, JSON_EXTRACT(obj, CONCAT('$.', kk))) as JSON )) into raw3;
~~~
注意 如果插入的值是array 或者object 插入时会是字符串， 必须 cast AS JSON 保持json类型
