You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
43 lines
1.6 KiB
43 lines
1.6 KiB
-- MySQL 5.7.21：只读核查关联字段，不查询业务明细。 |
|
-- 在 result.txt 对应的业务库执行，返回全部结果集。 |
|
SELECT VERSION(), DATABASE(), @@character_set_connection, @@collation_connection; |
|
|
|
SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, |
|
CHARACTER_SET_NAME, COLLATION_NAME, COLUMN_KEY |
|
FROM information_schema.COLUMNS |
|
WHERE TABLE_SCHEMA = DATABASE() |
|
AND ( |
|
(TABLE_NAME = 'negative' AND COLUMN_NAME IN ('id', 'involveDepartId', 'crtTime', 'discoveryTime', 'processing_status', 'checkStatus')) |
|
OR (TABLE_NAME = 'negative_blame' AND COLUMN_NAME IN ('negativeId', 'blameIdCode', 'type')) |
|
OR (TABLE_NAME = 'sup_depart' AND COLUMN_NAME IN ('id', 'pid')) |
|
OR (TABLE_NAME = 'sup_depart_police_size' AND COLUMN_NAME = 'depart_id') |
|
OR (TABLE_NAME = 'sup_police' AND COLUMN_NAME IN ('id_code', 'org_id', 'del')) |
|
) |
|
ORDER BY TABLE_NAME, ORDINAL_POSITION; |
|
|
|
-- JSON 计划中的 attached_condition 可显示隐式转换，SHOW WARNINGS 紧随 EXPLAIN 执行。 |
|
EXPLAIN FORMAT=JSON |
|
SELECT n.id, nb.blameIdCode |
|
FROM negative n |
|
LEFT JOIN negative_blame nb ON nb.negativeId = n.id |
|
WHERE n.crtTime BETWEEN '1949-01-01 00:00:00' AND NOW() |
|
LIMIT 10; |
|
SHOW WARNINGS; |
|
|
|
EXPLAIN FORMAT=JSON |
|
SELECT d.id, p.police_size |
|
FROM sup_depart d |
|
LEFT JOIN sup_depart_police_size p ON p.depart_id = d.id |
|
WHERE d.level = 3 |
|
LIMIT 10; |
|
SHOW WARNINGS; |
|
|
|
EXPLAIN FORMAT=JSON |
|
SELECT d.id |
|
FROM sup_depart d |
|
WHERE d.level = 3 AND EXISTS ( |
|
SELECT 1 FROM negative n |
|
WHERE n.involveDepartId = d.id |
|
AND n.crtTime BETWEEN '1949-01-01 00:00:00' AND NOW() |
|
); |
|
SHOW WARNINGS;
|
|
|