mysql json数据类型在数组内搜索值

Question

我的 MySQL 表中有一个 json 类型的列，该列中的值是一个简单的一维数组(例如 [1,2,3,4,5])。如何使用 json_contains 函数返回 json 数组中包含值“5”的所有行？

Answer 1

尝试:

mysql> DROP TABLE IF EXISTS `tbl`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `tbl` (
    ->   `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->   `json` JSON NOT NULL
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `tbl`
    ->   (`json`)
    -> VALUES
    ->   ('[1,2,3,4,5]'),
    ->   ('[1,2,3,4]'), 
    ->   ('[1,2]'),
    ->   ('[1]'),
    ->   ('[1,5]');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT
    ->   `id`,
    ->   `json`
    -> FROM
    ->   `tbl`
    -> WHERE
    ->   JSON_CONTAINS(`json`, '5');
+----+-----------------+
| id | json            |
+----+-----------------+
|  1 | [1, 2, 3, 4, 5] |
|  5 | [1, 5]          |
+----+-----------------+
2 rows in set (0.00 sec)

参见db-fiddle .

更新

mysql> DROP TABLE IF EXISTS `tbl`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `tbl` (
    ->   `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->   `json` JSON NOT NULL
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `tbl`
    ->   (`json`)
    -> VALUES
    ->   ('["1","2","3","4","5"]'),
    ->   ('["1","2","3","4"]'), 
    ->   ('["1","2"]'),
    ->   ('["1"]'),
    ->   ('["1","5"]');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT
    ->   `id`,
    ->   `json`
    -> FROM
    ->   `tbl`
    -> WHERE
    ->   JSON_CONTAINS(`json`, '"5"');
+----+---------------------------+
| id | json                      |
+----+---------------------------+
|  1 | ["1", "2", "3", "4", "5"] |
|  5 | ["1", "5"]                |
+----+---------------------------+
2 rows in set (0.00 sec)

参见db-fiddle .