MariaDB SQL 踩坑（Version: 10.4.12/CentOS 7）

最新推荐文章于 2022-07-23 17:45:37 发布

mostone

最新推荐文章于 2022-07-23 17:45:37 发布

阅读量990

点赞数

分类专栏： DB

本文链接：https://blog.csdn.net/mostone/article/details/107989562

版权

DB 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

一、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。