# 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類型
