MariaDB SQL 踩坑 (Version: 10.4.12/CentOS 7)

一、IF(expr1,expr2,expr3)

https://mariadb.com/kb/en/if-function/

预期返回 int,结果返回 string。

MariaDB [(none)]> select if(1=1, 1, "2"), @@version;
+-----------------+----------------------------------------+
| if(1=1, 1, "2") | @@version                              |
+-----------------+----------------------------------------+
| 1               | 10.4.12-MariaDB-1:10.4.12+maria~bionic |
+-----------------+----------------------------------------+

MariaDB [(none)]> select if(1=1, 1, 2), @@version;
+---------------+----------------------------------------+
| if(1=1, 1, 2) | @@version                              |
+---------------+----------------------------------------+
|             1 | 10.4.12-MariaDB-1:10.4.12+maria~bionic |
+---------------+----------------------------------------+

返回类型,由 expr2, expr3 决定,如果 expr2, expr3 任一参数为string,即使另一参数是 int,也返回 string。注意:返回值 "1",如果是数字,显示时右对齐。

 

二、JSON_SEARCH(json_doc, return_arg, search_str[, escape_char[, path] ...])

https://mariadb.com/kb/en/json_search/

预期返回可直接使用的 path string,结果返回的 path string 外头又包了双引号。

MariaDB [(none)]> SET @jsn = '[1,2,3,4,5]';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SET @path = JSON_SEARCH(@jsn, 'one', 2);
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SET @v1 = JSON_REMOVE(@jsn, @path);
Query OK, 0 rows affected, 1 warning (0.000 sec)

MariaDB [(none)]> SET @v2 = JSON_REMOVE(@jsn, json_value(@path,'$'));
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SELECT @path,json_value(@path,'$'), @v1, @v2;
+--------+-----------------------+------+--------------+
| @path  | json_value(@path,'$') | @v1  | @v2          |
+--------+-----------------------+------+--------------+
| "$[1]" | $[1]                  | NULL | [1, 3, 4, 5] |
+--------+-----------------------+------+--------------+

三、JSON_TYPE(json_val),JSON_VALUE(json_doc, path)

https://mariadb.com/kb/en/json_type/

https://mariadb.com/kb/en/json_value/

预期返回 STRING,结果返回 INTEGER / NULL

MariaDB [(none)]> SET @jsn = '["1",1,"4K","KK"]';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SET @v1 = JSON_VALUE(@jsn, '$[0]');
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SET @v2 = json_value(@jsn, '$[1]');
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SET @v3 = json_value(@jsn, '$[2]');
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SET @v4 = json_value(@jsn, '$[3]');
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SELECT @v1, JSON_TYPE(@v1),@v2, JSON_TYPE(@v2),@v3, JSON_TYPE(@v3),@v4, JSON_TYPE(@v4), JSON_TYPE('"4"');
+------+----------------+------+----------------+------+----------------+------+----------------+------------------+
| @v1  | JSON_TYPE(@v1) | @v2  | JSON_TYPE(@v2) | @v3  | JSON_TYPE(@v3) | @v4  | JSON_TYPE(@v4) | JSON_TYPE('"4"') |
+------+----------------+------+----------------+------+----------------+------+----------------+------------------+
| 1    | INTEGER        | 1    | INTEGER        | 4K   | INTEGER        | KK   | NULL           | STRING           |
+------+----------------+------+----------------+------+----------------+------+----------------+------------------+

头疼,json 数据的解析思路跟不上,为什么 json_value 返回的数据,究竟是怎么处理双引号的。

四、JSON_MERGE(json_doc, json_doc[, json_doc] ...)

https://mariadb.com/kb/en/json_merge/

JSON_MERGE has been deprecated since MariaDB 10.2.25, MariaDB 10.3.16 and MariaDB 10.4.5. JSON_MERGE_PATCH is an RFC 7396-compliant replacement, and JSON_MERGE_PRESERVE is a synonym.

文档中的这句话,没看仔细,以为是用  JSON_MERGE_PATCH 代替已在新版本废弃的 JSON_MERGE。而实际上不是,替代者是后面提及的异名函数 JSON_MERGE_PRESERVE

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 撸撸猫 设计师:C马雯娟 返回首页