MySQL主键约束相关查询SQL以及批量清除所有关联表的表中数据内容操作【测试成功】

mysql -h 127.0.0.1 -P 18954 -u root -p

SELECT VERSION();

SHOW TABLE STATUS;

SHOW VARIABLES LIKE 'foreign_key_checks';

SELECT 
    TABLE_NAME,
    COLUMN_NAME,
    CONSTRAINT_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
    REFERENCED_TABLE_NAME IS NOT NULL
    AND TABLE_SCHEMA = 'eam';

SET FOREIGN_KEY_CHECKS=0;

SET SESSION foreign_key_checks = 0;

SELECT DISTINCT
    TABLE_NAME
FROM
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
    CONSTRAINT_TYPE = 'FOREIGN KEY'
    AND TABLE_SCHEMA = 'eam';

SET FOREIGN_KEY_CHECKS = 0; -- 禁用外键约束
TRUNCATE ACT_GE_BYTEARRAY;
TRUNCATE ACT_ID_MEMBERSHIP;
TRUNCATE ACT_PROCDEF_INFO;
TRUNCATE ACT_RE_MODEL;
TRUNCATE ACT_RU_EVENT_SUBSCR;
TRUNCATE ACT_RU_EXECUTION;
TRUNCATE ACT_RU_IDENTITYLINK;
TRUNCATE ACT_RU_JOB;
TRUNCATE ACT_RU_TASK;
TRUNCATE ACT_RU_VARIABLE;
TRUNCATE ACT_SETTING;
TRUNCATE APP_VERSION;
TRUNCATE ASSETS_ADMIN;
TRUNCATE ASSETS_ALLOT_RELATION;
TRUNCATE ASSETS_BORROW_RETURN_RELATION;
TRUNCATE ASSETS_CHANGE_HIS;
TRUNCATE ASSETS_CHECK_ED_CI;
TRUNCATE ASSETS_CHECK_ED_WC;
TRUNCATE ASSETS_CHECK_EXEC;
TRUNCATE ASSETS_CHECK_EXEC_DETAIL;
TRUNCATE ASSETS_CHECK_ITEM;
TRUNCATE ASSETS_CHECK_PLAN;
TRUNCATE ASSETS_CHECK_PLAN_RELATION;
TRUNCATE ASSETS_CHECK_TEMPLATE;
TRUNCATE ASSETS_CHECK_TEMPLATE_CATEGORY;
TRUNCATE ASSETS_CHECK_TEMPLATE_RELATION;
TRUNCATE ASSETS_CHECK_WAY;
TRUNCATE ASSETS_CHECK_WAY_RELATION;
TRUNCATE ASSETS_FINANCIAL;
TRUNCATE ASSETS_INFO;
TRUNCATE ASSETS_MAINTAIN_ED_CI;
TRUNCATE ASSETS_MAINTAIN_ED_CI_STORE;
TRUNCATE ASSETS_MAINTAIN_ED_WC;
TRUNCATE ASSETS_MAINTAIN_EXEC;
TRUNCATE ASSETS_MAINTAIN_EXEC_DETAIL;
TRUNCATE ASSETS_MAINTAIN_EXEC_DETAIL_STORE;
TRUNCATE ASSETS_MAINTAIN_EXEC_DETAIL_STORE_OCCUPY;
TRUNCATE ASSETS_MAINTAIN_ITEM;
TRUNCATE ASSETS_MAINTAIN_ITEM_RELATION;
TRUNCATE ASSETS_MAINTAIN_ITEM_TYPE;
TRUNCATE ASSETS_MAINTAIN_PLAN;
TRUNCATE ASSETS_MAINTAIN_PLAN_RELATION;
TRUNCATE ASSETS_MAINTAIN_TEMPLATE;
TRUNCATE ASSETS_MAINTAIN_TEMPLATE_CATEGORY;
TRUNCATE ASSETS_MAINTAIN_TEMPLATE_RELATION;
TRUNCATE ASSETS_MAINTAIN_WAY;
TRUNCATE ASSETS_MAINTAIN_WAY_RELATION;
TRUNCATE ASSETS_OPEN_SEAL_RELATION;
TRUNCATE ASSETS_OPT_HIS;
TRUNCATE ASSETS_REPAIR;
TRUNCATE ASSETS_REPAIR_ITEM;
TRUNCATE ASSETS_REPAIR_ITEM_RELATION;
TRUNCATE ASSETS_REPAIR_ITEM_TYPE;
TRUNCATE ASSETS_REPAIR_ORDER_STORE;
TRUNCATE ASSETS_REPAIR_ORDER_STORE_OCCUPY;
TRUNCATE ASSETS_REPAIR_STORE;
TRUNCATE ASSETS_RETURN_RELATION;
TRUNCATE ASSETS_SALE_SERVICE;
TRUNCATE ASSETS_SCRAP_RELATION;
TRUNCATE ASSETS_SEGMENT;
TRUNCATE ASSETS_SEGMENT_DETAIL;
TRUNCATE ASSETS_SEGMENT_TASK;
TRUNCATE ASSETS_SEGMENT_TASK_CATEGORY;
TRUNCATE ASSETS_SEGMENT_TASK_DEPT;
TRUNCATE ASSETS_TECH;
TRUNCATE ASSETS_USE_RELATION;
TRUNCATE CHANGE_AF;
TRUNCATE CHANGE_AF_RELATION;
TRUNCATE CHANGE_ASS;
TRUNCATE CHANGE_ASSETS;
TRUNCATE CHANGE_ASSETS_RELATION;
TRUNCATE CHANGE_ASS_RELATION;
TRUNCATE CHANGE_AT_RELATION;
TRUNCATE CUS_FIELD_CATEGORY;
TRUNCATE CUS_FIELD_ITEM;
TRUNCATE DOC_COMPANY;
TRUNCATE DOC_REGION;
TRUNCATE DOC_REPAIR_PART_CATEGORY;
TRUNCATE DOC_STORE;
TRUNCATE OUTBOUND_ORDER;
TRUNCATE OUTBOUND_ORDER_RELATION;
TRUNCATE PERSONAL_DEPT;
TRUNCATE RECEIPT_DOC;
TRUNCATE RECEIPT_DOC_RELATION;
TRUNCATE REPAIR_PART;
TRUNCATE REPAIR_PART_ASSETS;
TRUNCATE REPAIR_PART_STORE;
TRUNCATE REPAIR_PART_USE;
TRUNCATE REPAIR_PART_USE_RELATION;
TRUNCATE RETURN_ORDER;
TRUNCATE RETURN_ORDER_RELATION;
TRUNCATE SYS_DEPT_USER_MANAGER;
TRUNCATE SYS_GROUP_DEPT_PER;
TRUNCATE SYS_GROUP_DIMEN_PER;
TRUNCATE SYS_SETTING_VALUE;
TRUNCATE SYS_USER_DEPT;
TRUNCATE SYS_USER_DEPT_PER;
TRUNCATE SYS_USER_PER_GROUP;
TRUNCATE SYS_USER_ROLE;
TRUNCATE WORK_CLASS;
TRUNCATE WORK_CLASS_USER;
TRUNCATE WORK_GROUP;
TRUNCATE WORK_GROUP_USER;