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

艺帆风顺 发布于 2025-04-03 50 次阅读

1、连接数据库，查询版本信息

    在MySQL客户端连接MySQL数据库并指定端口：

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

查询MySQL数据库版本信息：

SELECT VERSION();

查看MySQL数据库所使用的引擎：

SHOW TABLE STATUS;

查询是否启用外键检查：

SHOW VARIABLES LIKE 'foreign_key_checks';

2、查询特定库中的MySQL数据表、相关字段的关联关系：

    SELECT  TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAMEFROM INFORMATION_SCHEMA.KEY_COLUMN_USAGEWHERE REFERENCED_TABLE_NAME IS NOT NULL AND TABLE_SCHEMA = 'eam';

    3、禁用MySQL的外键检查

        该语句将设置foreign_key_checks变量的值为0，从而禁用了外键检查。

    SET FOREIGN_KEY_CHECKS=0;

        如果您只想在当前会话中禁用外键检查，可以使用以下命令：

    SET SESSION foreign_key_checks = 0;

        该命令仅在当前会话中禁用外键检查，并在会话结束时失效。

    4、SQL批量清除含有外键关系的所有表：

        查询数据库中哪些表带有外键关联：

      SELECT DISTINCT TABLE_NAMEFROM INFORMATION_SCHEMA.TABLE_CONSTRAINTSWHERE 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;