mysql> create table json_test(c json);
Query OK, 0 rows affected (0.40 sec)

mysql> insert into json_test values('{"pump":"0","track":"0","bats":"1","acc":"1","batl":"6","gpslev":"5"}');
Query OK, 1 row affected (0.15 sec)

mysql> select * from json_test;
+----------------------------------------------------------------------------------+
| c                                                                                |
+----------------------------------------------------------------------------------+
| {"acc": "1", "batl": "6", "bats": "1", "pump": "0", "track": "0", "gpslev": "5"} |
+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_extract(c,'$.bats') from json_test;
+--------------------------+
| json_extract(c,'$.bats') |
+--------------------------+
| "1"                      |
+--------------------------+
1 row in set (0.00 sec)

mysql> select json_extract(c,'$.bats') from json_test where json_extract(c,'$.bats') = "1";
+--------------------------+
| json_extract(c,'$.bats') |
+--------------------------+
| "1"                      |
+--------------------------+
1 row in set (0.00 sec)

如果您的mysql版本支持json，那么请尝试下面的查询。

SELECT JSON_EXTRACT(json_data, '$.bats') FROM `json_test` WHERE JSON_SEARCH(json_data, 'bats', '0%')

使用json函数：

SELECT col->"$.bats"    -- and maybe other columns you want here
FROM yourTable
WHERE col->"$.bats" = '0';

演示

编辑：
如果您的mysql版本不支持json函数，我们仍然可以尝试使用 REGEXP :

SELECT *
FROM yourTable
WHERE col REGEXP '"bats":"0"';