Mysql
LEFT JOIN 未按預期工作
我正在使用 mysql 8.0.21。我正在嘗試加入兩個表，以便可以找到外鍵的索引名稱。這是我的查詢：
SELECT KEY_COLUMN_USAGE.CONSTRAINT_SCHEMA, KEY_COLUMN_USAGE.TABLE_SCHEMA, KEY_COLUMN_USAGE.REFERENCED_TABLE_SCHEMA, KEY_COLUMN_USAGE.TABLE_NAME, KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME, KEY_COLUMN_USAGE.COLUMN_NAME, KEY_COLUMN_USAGE.REFERENCED_COLUMN_NAME, KEY_COLUMN_USAGE.CONSTRAINT_NAME, STATISTICS.INDEX_NAME FROM information_schema.KEY_COLUMN_USAGE LEFT JOIN information_schema.STATISTICS ON STATISTICS.TABLE_NAME = KEY_COLUMN_USAGE.TABLE_NAME AND STATISTICS.COLUMN_NAME = KEY_COLUMN_USAGE.COLUMN_NAME WHERE KEY_COLUMN_USAGE.CONSTRAINT_NAME <> 'PRIMARY' AND KEY_COLUMN_USAGE.REFERENCED_TABLE_SCHEMA IS NOT NULL AND KEY_COLUMN_USAGE.TABLE_SCHEMA NOT IN('mysql','performance_schema','sys') AND STATISTICS.INDEX_NAME <> 'PRIMARY';輸出：
+-------------------+-------------------+-------------------------+------------+-----------------------+-------------+------------------------+-------------------+------------+ | CONSTRAINT_SCHEMA | TABLE_SCHEMA | REFERENCED_TABLE_SCHEMA | TABLE_NAME | REFERENCED_TABLE_NAME | COLUMN_NAME | REFERENCED_COLUMN_NAME | CONSTRAINT_NAME | INDEX_NAME | +-------------------+-------------------+-------------------------+------------+-----------------------+-------------+------------------------+-------------------+------------+ | StudentAttendance | StudentAttendance | StudentAttendance | ATTENDANCE | STUDENT | RollNumber | RollNumber | ATTENDANCE_ibfk_1 | RollNumber | | StudentAttendance | StudentAttendance | StudentAttendance | STUDENT | GUARDIAN | GUID | GUID | STUDENT_ibfk_1 | GUID | | technastic | technastic | technastic | branch | employee | mgr_id | emp_id | branch_ibfk_1 | mgr_id | | technastic | technastic | technastic | employee | branch | branch_id | branch_id | employee_ibfk_1 | branch_id | | technastic | technastic | technastic | employee | employee | super_id | emp_id | employee_ibfk_2 | super_id | | technastic | technastic | technastic | client | branch | branch_id | branch_id | client_ibfk_1 | branch_id | | technastic | technastic | technastic | works_with | client | client_id | client_id | works_with_ibfk_2 | client_id | | OFFICE | OFFICE | OFFICE | EMPLOYEE | DEPARTMENT | DeptId | DeptId | EMPLOYEE_ibfk_1 | DeptId | +-------------------+-------------------+-------------------------+------------+-----------------------+-------------+------------------------+-------------------+------------+ 8 rows in set (0.02 sec)但我期待的輸出不是這個。我會解釋我的情況。
我用來查找所有外鍵的查詢是這樣的：
SELECT KEY_COLUMN_USAGE.CONSTRAINT_SCHEMA, KEY_COLUMN_USAGE.TABLE_SCHEMA, KEY_COLUMN_USAGE.REFERENCED_TABLE_SCHEMA, KEY_COLUMN_USAGE.TABLE_NAME, KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME, KEY_COLUMN_USAGE.COLUMN_NAME, KEY_COLUMN_USAGE.REFERENCED_COLUMN_NAME, KEY_COLUMN_USAGE.CONSTRAINT_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE KEY_COLUMN_USAGE.CONSTRAINT_NAME <> 'PRIMARY' AND KEY_COLUMN_USAGE.REFERENCED_TABLE_SCHEMA IS NOT NULL AND KEY_COLUMN_USAGE.TABLE_SCHEMA NOT IN('mysql','performance_schema','sys') ORDER BY KEY_COLUMN_USAGE.TABLE_NAME ASC, KEY_COLUMN_USAGE.COLUMN_NAME ASC;輸出：
+-------------------+-------------------+-------------------------+-----------------+-----------------------+-------------+------------------------+------------------------+ | CONSTRAINT_SCHEMA | TABLE_SCHEMA | REFERENCED_TABLE_SCHEMA | TABLE_NAME | REFERENCED_TABLE_NAME | COLUMN_NAME | REFERENCED_COLUMN_NAME | CONSTRAINT_NAME | +-------------------+-------------------+-------------------------+-----------------+-----------------------+-------------+------------------------+------------------------+ | StudentAttendance | StudentAttendance | StudentAttendance | ATTENDANCE | STUDENT | RollNumber | RollNumber | ATTENDANCE_ibfk_1 | | OFFICE | OFFICE | OFFICE | EMPLOYEE | DEPARTMENT | DeptId | DeptId | EMPLOYEE_ibfk_1 | | StudentAttendance | StudentAttendance | StudentAttendance | STUDENT | GUARDIAN | GUID | GUID | STUDENT_ibfk_1 | | technastic | technastic | technastic | branch | employee | mgr_id | emp_id | branch_ibfk_1 | | technastic | technastic | technastic | branch_supplier | branch | branch_id | branch_id | branch_supplier_ibfk_1 | | technastic | technastic | technastic | client | branch | branch_id | branch_id | client_ibfk_1 | | technastic | technastic | technastic | employee | branch | branch_id | branch_id | employee_ibfk_1 | | technastic | technastic | technastic | employee | employee | super_id | emp_id | employee_ibfk_2 | | technastic | technastic | technastic | works_with | client | client_id | client_id | works_with_ibfk_2 | | technastic | technastic | technastic | works_with | employee | emp_id | emp_id | works_with_ibfk_1 | +-------------------+-------------------+-------------------------+-----------------+-----------------------+-------------+------------------------+------------------------+ 10 rows in set (0.01 sec)我還通過另一個查詢檢查輸出是否顯示了所有外鍵：
select * from referential_constraints;輸出：
+--------------------+-------------------+------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+-----------------+-----------------------+ | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | UNIQUE_CONSTRAINT_CATALOG | UNIQUE_CONSTRAINT_SCHEMA | UNIQUE_CONSTRAINT_NAME | MATCH_OPTION | UPDATE_RULE | DELETE_RULE | TABLE_NAME | REFERENCED_TABLE_NAME | +--------------------+-------------------+------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+-----------------+-----------------------+ | def | StudentAttendance | ATTENDANCE_ibfk_1 | def | StudentAttendance | PRIMARY | NONE | NO ACTION | NO ACTION | ATTENDANCE | STUDENT | | def | StudentAttendance | STUDENT_ibfk_1 | def | StudentAttendance | PRIMARY | NONE | NO ACTION | NO ACTION | STUDENT | GUARDIAN | | def | technastic | branch_ibfk_1 | def | technastic | PRIMARY | NONE | NO ACTION | SET NULL | branch | employee | | def | technastic | employee_ibfk_1 | def | technastic | PRIMARY | NONE | NO ACTION | SET NULL | employee | branch | | def | technastic | employee_ibfk_2 | def | technastic | PRIMARY | NONE | NO ACTION | SET NULL | employee | employee | | def | technastic | client_ibfk_1 | def | technastic | PRIMARY | NONE | NO ACTION | SET NULL | client | branch | | def | technastic | works_with_ibfk_1 | def | technastic | PRIMARY | NONE | NO ACTION | CASCADE | works_with | employee | | def | technastic | works_with_ibfk_2 | def | technastic | PRIMARY | NONE | NO ACTION | CASCADE | works_with | client | | def | technastic | branch_supplier_ibfk_1 | def | technastic | PRIMARY | NONE | NO ACTION | CASCADE | branch_supplier | branch | | def | OFFICE | EMPLOYEE_ibfk_1 | def | OFFICE | PRIMARY | NONE | NO ACTION | NO ACTION | EMPLOYEE | DEPARTMENT | +--------------------+-------------------+------------------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+-----------------+-----------------------+ 10 rows in set (0.00 sec)由於表中的所有約束名稱
referential_constraints也在key_column_usage表中，我們可以繼續。現在我想找到所有外鍵的索引名稱。由於沒有完美的查詢來查找所有外鍵的索引名稱，我想出了這個查詢：
SELECT STATISTICS.TABLE_SCHEMA, STATISTICS.INDEX_SCHEMA, STATISTICS.TABLE_NAME, STATISTICS.COLUMN_NAME, STATISTICS.INDEX_NAME FROM information_schema.STATISTICS WHERE STATISTICS.INDEX_NAME <> 'PRIMARY' AND STATISTICS.TABLE_SCHEMA NOT IN('mysql','performance_schema','sys') ORDER BY STATISTICS.TABLE_NAME ASC, STATISTICS.COLUMN_NAME ASC;輸出：
+-------------------+-------------------+------------+-------------+------------+ | TABLE_SCHEMA | INDEX_SCHEMA | TABLE_NAME | COLUMN_NAME | INDEX_NAME | +-------------------+-------------------+------------+-------------+------------+ | StudentAttendance | StudentAttendance | ATTENDANCE | RollNumber | RollNumber | | OFFICE | OFFICE | EMPLOYEE | DeptId | DeptId | | StudentAttendance | StudentAttendance | GUARDIAN | GPhone | GPhone | | StudentAttendance | StudentAttendance | STUDENT | GUID | GUID | | technastic | technastic | branch | mgr_id | mgr_id | | technastic | technastic | client | branch_id | branch_id | | technastic | technastic | employee | branch_id | branch_id | | technastic | technastic | employee | super_id | super_id | | technastic | technastic | works_with | client_id | client_id | +-------------------+-------------------+------------+-------------+------------+ 9 rows in set (0.01 sec)這個查詢的問題是它不能只為外鍵找到索引名稱。在輸出中，GPhone列不是外鍵；這是一個獨特的鑰匙。
現在我想加入這兩個表（具體來說是 LEFT OUTER JOIN），以便輕鬆辨識各個外鍵的索引名稱。我期望的輸出是這樣的：
請讓我知道我哪裡出錯了，正確的查詢是什麼。
經典的 SQL 問題！
"LEFT JOIN" + WHERE condition(s) on "right" table => "INNER JOIN"重組您的查詢，以便“正確”表上的條件進入連接子句，而不是where子句：
SELECT . . . FROM information_schema.KEY_COLUMN_USAGE kcu LEFT JOIN information_schema.STATISTICS s ON s.TABLE_NAME = kcu.TABLE_NAME AND s.COLUMN_NAME = kcu.COLUMN_NAME AND s.INDEX_NAME <> 'PRIMARY' <-- Condition on "right" table WHERE kcu.CONSTRAINT_NAME <> 'PRIMARY' AND kcu.REFERENCED_TABLE_SCHEMA <> 'NULL' <-- 'NULL' or NULL ? BIG difference. AND kcu.TABLE_SCHEMA NOT IN ( 'mysql', 'performance_schema', 'sys' ) ORDER BY . . . ;