Какой самый элегантный способ выполнить этот рекурсивный поиск в базе данных "многие ко многим"?

У меня есть таблица MySQL, которая содержит данные о пользователях "многие-ко-многим":

username (PK)     ip (PK)          machine_id (PK)
--------------------------------------------------
tester1           1.2.3.4          ABC
tester1           6.6.6.6          BBB
tester2           1.2.3.4          ZZZ
tester3           2.3.4.5          ABC
tester4           2.3.4.5          OOO
tester5           1.2.3.4          XYZ
tester5           8.9.7.6          BBB
tester5           1.2.3.4          OOO
tester6           9.9.9.9          ZZZ

Он использует комбинированный первичный ключ, чтобы ограничить строки уникальными комбинациями всех трех столбцов.

Как видите, один и тот же пользователь может использовать комбинацию нескольких IP-адресов и нескольких машин для доступа к системе. Для моих целей это один и тот же пользователь, если у них одинаковый IP или идентификатор машины.

Мне нужен способ найти все возможные связи между данными, чтобы я мог идентифицировать каждую возможную комбинацию имени пользователя / IP / ID, которую кто-то использовал.

Пример

Если бы я хотел узнать, какие псевдонимы использовал tester1, это было бы просто: SELECT * FROM users WHERE username LIKE '%tester1%' что дало бы мне:

tester1      1.2.3.4     ABC
tester1      6.6.6.6     BBB

Затем я могу связать IP-адреса 1.2.3.4 и 6.6.6.6 с этим пользователем, и если я посмотрю на оба этих IP-адреса, я вижу, что они использовали несколько других имен и идентификаторов компьютеров:

tester2      1.2.3.4     ZZZ
tester5      1.2.3.4     XYZ
tester5      1.2.3.4     OOO
tester1      6.6.6.6     BBB

Затем я перехожу к поиску всех идентификаторов компьютеров пользователя, что дает нам еще один его псевдоним (из идентификатора компьютера OOO):

tester4      2.3.4.5     OOO

Посмотрев OOO, я нахожу еще один IP, 2.3.4.5, который дает мне еще одно соединение:

tester3      2.3.4.5     ABC

Поскольку мы уже искали идентификатор машины ABC, нет необходимости искать его снова.

Теперь я определил все возможные псевдонимы этого пользователя и получил полный список IP-адресов, идентификаторов компьютеров и имен пользователей, которые он использовал. Все это происходило из рекурсивного поиска данных, по результатам которого затем выполнялся другой рекурсивный поиск, и так далее.

У меня вопрос: как перевести эту логику на PHP / SQL?

Есть ли способ извлечь все "связанные" данные напрямую через запрос, или потребуется некоторая обработка PHP, и если да, то что?


mysql php oop recursion recursive-query
person WackGet    schedule 30.07.2013    source источник
comment
mysql не выполняет рекурсивные запросы. вы можете несколько имитировать это, выполнив серию самосоединений, но это работает только для определенной глубины рекурсии, это не будет продолжаться до конца. он также очень быстро становится чрезвычайно уродливым. Итак, да ... требуется обработка на стороне клиента.   -  person Marc B    schedule 30.07.2013
comment
Я так и думал. Я просто зациклился на логике PHP.   -  person WackGet    schedule 30.07.2013
comment
в основном цикл while (), который просто продолжает копать все глубже и глубже, пока вы не достигнете нижней (или верхней) части дерева.   -  person Marc B    schedule 30.07.2013
comment
Вы также можете эмулировать рекурсивный запрос с вызовом рекурсивной хранимой процедуры (например, предложенной в этом другом ответе) который заполняет временную таблицу. Теоретически это должно быть быстрее, чем выполнение повторяющихся запросов из PHP, но также немного сложнее писать.   -  person RandomSeed    schedule 31.07.2013

Ответы (1)


arrow_upward
0
arrow_downward

В основном вы можете использовать запрос выбора и два самостоятельных соединения в столбце идентификатора машины. Например:

      select * from users left join users u1 on users. mid=u1.mid  left join users u2 on u1. mid=u2. mid where  name like "%tester1%"
person Gigamegs    schedule 30.07.2013