🔥码云GVP开源项目 12k star Uniapp+ElementUI 功能强大 支持多语言、二开方便! 广告
# 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