Комплексное представление MySQL
Я пытаюсь обернуть голову вокруг просмотров. Я думаю, что они лучше всего подойдут для того, что мне нужно. Здесь я создам три примера таблиц, но они должны проиллюстрировать то, что я пытаюсь сделать.
Во-первых, таблица, содержащая список всех пользователей Discord, которые использовали или искали мой продукт:
mysql> DESCRIBE discord_users;
+----------------+--------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+-------------------+-----------------------------------------------+
| no | int | NO | PRI | NULL | auto_increment |
| discord_user | varchar(19) | NO | UNI | NULL | |
| is_banned | tinyint | NO | | 0 | |
| is_whitelisted | varchar(45) | NO | | 0 | |
| note | varchar(256) | YES | | NULL | |
| timestamp | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+----------------+--------------+------+-----+-------------------+-----------------------------------------------+
mysql> SELECT * FROM discord_users;
+----+--------------------+-----------+----------------+---------------+---------------------+
| no | discord_user | is_banned | is_whitelisted | note | timestamp |
+----+--------------------+-----------+----------------+---------------+---------------------+
| 1 | 228937760390643713 | 1 | 0 | Stev#6666 | 2023-04-14 06:14:39 |
| 2 | 253725613439975426 | 0 | 1 | Harsh#3028 | 2023-04-14 06:14:39 |
| 3 | 533022512372645916 | 0 | 1 | ItsBranK#1337 | 2023-04-14 06:14:39 |
+----+--------------------+-----------+----------------+---------------+---------------------+
Во-вторых, таблица со списком всех пользователей Epic Games, которые использовали мой продукт или искали его:
mysql> DESCRIBE epic_games_users;
+-----------------+--------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+-------------------+-----------------------------------------------+
| no | int | NO | PRI | NULL | auto_increment |
| epic_games_user | varchar(32) | NO | UNI | NULL | |
| is_banned | tinyint | NO | | 0 | |
| is_whitelisted | tinyint | NO | | 0 | |
| note | varchar(256) | YES | | NULL | |
| timestamp | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-----------------+--------------+------+-----+-------------------+-----------------------------------------------+
mysql> SELECT * FROM epic_games_users;
+----+----------------------------------+-----------+----------------+-----------+---------------------+
| no | epic_games_user | is_banned | is_whitelisted | note | timestamp |
+----+----------------------------------+-----------+----------------+-----------+---------------------+
| 1 | d8603dd248ad45bcb47305a68841d676 | 0 | 0 | RL.Plus | 2023-04-14 06:15:25 |
| 2 | 0274508828c347ddaff7bb9051d2303d | 0 | 1 | EquinoxRL | 2023-04-14 06:15:25 |
+----+----------------------------------+-----------+----------------+-----------+---------------------+
Наконец, у меня есть таблица пользователей Discord и Epic Games, которые связали свои учетные записи. Внешние ключи — это discord_user и epic_games_user в таблицах выше.
mysql> DESCRIBE links;
+-----------------+--------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+-------------------+-----------------------------------------------+
| no | int | NO | PRI | NULL | auto_increment |
| discord_user | varchar(19) | NO | UNI | NULL | |
| epic_games_user | varchar(32) | NO | UNI | NULL | |
| note | varchar(256) | NO | | NULL | |
| timestamp | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-----------------+--------------+------+-----+-------------------+-----------------------------------------------+
mysql> SELECT * FROM links;
+----+--------------------+----------------------------------+------+---------------------+
| no | discord_user | epic_games_user | note | timestamp |
+----+--------------------+----------------------------------+------+---------------------+
| 1 | 228937760390643713 | d8603dd248ad45bcb47305a68841d676 | NULL | 2023-04-14 06:16:07 |
+----+--------------------+----------------------------------+------+---------------------+
Примечание. Не каждый пользователь в таблицах discord_users и epic_games_users появится в links.
Я хотел бы создать представление, похожее на следующее - лучший способ, которым я могу его описать:
+--------------------+----------------------------------+-----------+----------------+
| discord_user | epic_games_user | is_banned | is_whitelisted |
+--------------------+-------------+--------------------+-----------+----------------+
| 228937760390643713 | d8603dd248ad45bcb47305a68841d676 | 1 | 0 |
| 253725613439975426 | NULL | 0 | 1 |
| 533022512372645916 | NULL | 0 | 1 |
| NULL | 0274508828c347ddaff7bb9051d2303d | 0 | 1 |
+--------------------+----------------------------------+-----------+----------------+
В нем должны быть перечислены все discord_user и epic_games_user рядом со связанной учетной записью, если она у них есть. Если у него нет связанной учетной записи, другой столбец пользователя, будь то discord_user или epic_games_user, должен быть null.
is_banned и is_whitelisted должны быть истинными, если эти столбцы истинны в таблицах discord_users или epic_games_users. Очевидно, что если они не появляются в links, это будет зависеть только от таблицы, в которой они существуют.
Честно говоря, я понятия не имею, с чего начать, и я был бы признателен за любую помощь. Я считаю, что могу легко перечислить тех, кто появляется в links, и забанены ли они или внесены в белый список. Хитрость заключается в том, чтобы включить discord_user и epic_games_user, которых нет в links, и предотвратить дублирование.
Спасибо за помощь!
Привет, @P.Salmon! Да, если либо discord_user, либо epic-games_user имеет is_banned=1, тогда результат должен быть 1.
Ответы 1
Первый запрос получает всех пользователей в discord_users и, если он существует, связанного пользователя в epic_games_users. Второй запрос возвращает пользователей, которые существуют только в epic_games_users.
SELECT
du.discord_user,
egu.epic_games_user,
(du.is_banned OR IFNULL(egu.is_banned, 0)) AS is_banned,
(du.is_whitelisted OR IFNULL(egu.is_whitelisted, 0)) AS is_whitelisted
FROM discord_users du
LEFT JOIN links l ON du.discord_user = l.discord_user
LEFT JOIN epic_games_users egu ON l.epic_games_user = egu.epic_games_user
UNION ALL
SELECT
NULL,
egu.epic_games_user,
egu.is_banned,
egu.is_whitelisted
FROM epic_games_users egu
LEFT JOIN links l ON egu.epic_games_user = l.epic_games_user
WHERE l.epic_games_user IS NULL
выходы:
а затем просто предварите приведенный выше запрос CREATE VIEW:
CREATE VIEW `your_view_name` AS
...
Вы посланы Богом! Я очень рад вернуться домой с работы и действительно посмотреть на это и изучить ваш подход и то, как он работает. Большое спасибо!
В желаемом вами результате что означает is_banned и is_whitelisted? если в этом отношении существует конфликт между discord_users и epic_users, который должен иметь приоритет - вы настроены пессимистично и говорите, что если любой из is_banned = 1, то результат должен быть 1?