# IFNULL 函数
select IFNULL(a,b) from c
如果a为null 则返回b
# in 查询后 按in里的顺序排序
`WHERE id IN($id) ORDER BY find_in_set(id ,'{$id}')`
# 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