Есть MVA поле (visit_company_ids), нужно найти все кроме где значение (visit_company_ids) не равное указанного значения (5). Пробую так: select id, visit_company_ids, geo_place_id from jobResume where geo_place_id=1501321 and any(visited_company_ids)<>5
так: select id, visit_company_ids, geo_place_id from jobResume where geo_place_id=1501321 and visited_company_ids not any('1');
есть ли возможность найти все кроме указанного значения?
SHOW WARNINGS; показывает
±--------±-----±---------------------------------------------------------------------+
| Level | Code | Message |
±--------±-----±---------------------------------------------------------------------+
| warning | 1000 | index jobResumeIndex: no such filter attribute ‘visited_company_ids’ |
±--------±-----±---------------------------------------------------------------------+
Но:
mysql> DESCRIBE jobResumeIndex;
±---------------------±-------±-----------+
| Field | Type | Properties |
±---------------------±-------±-----------+
| id | bigint | |
| post | text | indexed |
| about_me | text | indexed |
…
| portfolio_ids | mva | |
| course_ids | mva | |
| visit_company_ids | mva | | <<<<<<<<<<<<<<<<<<<< Это поле существует
±---------------------±-------±-----------+
54 rows in set (0.00 sec)
mysql> drop table if exists jobresumeindex; create table jobresumeindex(f text, visit_company_ids multi); insert into jobresumeindex(id, visit_company_ids) values (1,(1,2)), (2,(5,6)); select * from jobresumeindex; select * from jobresumeindex where all(visit_company_ids) not in (1); select * from jobresumeindex where all(visit_company_ids) not in (5); select * from jobresumeindex where all(visit_company_ids) not in (1,5); select * from jobresumeindex where any(visit_company_ids) in (5); select * from jobresumeindex where all(
--------------
drop table if exists jobresumeindex
--------------
Query OK, 0 rows affected (0.00 sec)
--------------
create table jobresumeindex(f text, visit_company_ids multi)
--------------
Query OK, 0 rows affected (0.01 sec)
--------------
insert into jobresumeindex(id, visit_company_ids) values (1,(1,2)), (2,(5,6))
--------------
Query OK, 2 rows affected (0.00 sec)
--------------
select * from jobresumeindex
--------------
+------+-------------------+------+
| id | visit_company_ids | f |
+------+-------------------+------+
| 1 | 1,2 | |
| 2 | 5,6 | |
+------+-------------------+------+
2 rows in set (0.00 sec)
--------------
select * from jobresumeindex where all(visit_company_ids) not in (1)
--------------
+------+-------------------+------+
| id | visit_company_ids | f |
+------+-------------------+------+
| 2 | 5,6 | |
+------+-------------------+------+
1 row in set (0.00 sec)
--------------
select * from jobresumeindex where all(visit_company_ids) not in (5)
--------------
+------+-------------------+------+
| id | visit_company_ids | f |
+------+-------------------+------+
| 1 | 1,2 | |
+------+-------------------+------+
1 row in set (0.00 sec)
--------------
select * from jobresumeindex where all(visit_company_ids) not in (1,5)
--------------
Empty set (0.00 sec)
--------------
select * from jobresumeindex where any(visit_company_ids) in (5)
--------------
+------+-------------------+------+
| id | visit_company_ids | f |
+------+-------------------+------+
| 2 | 5,6 | |
+------+-------------------+------+
1 row in set (0.00 sec)
--------------
select * from jobresumeindex where all(visit_company_ids) in (5,6)
--------------
+------+-------------------+------+
| id | visit_company_ids | f |
+------+-------------------+------+
| 2 | 5,6 | |
+------+-------------------+------+
1 row in set (0.00 sec)
--------------
select * from jobresumeindex where all(visit_company_ids) in (5,7)
--------------
Empty set (0.00 sec)