如何整合SQL获取MySQL用户表及关联表的全部相关数据？

核心思路 #

先找出所有通过外键关联user表的表，再针对每个表筛选出与目标用户组（名称含test）相关的数据，最终整合导出为.sql文件。

步骤1：获取所有关联user的表及对应外键列 #

不同表的外键列名可能不同（比如user_id、creator_id），所以需要先明确关联关系：

SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE REFERENCED_TABLE_SCHEMA = 'db_name'  -- 替换为你的数据库名
AND REFERENCED_TABLE_NAME = 'user';

该语句会返回所有直接关联user表的表名，以及对应指向user.id的外键列名。

步骤2：批量生成查询或导出语句 #

针对返回的每个表，可选择手动处理或自动化脚本导出：

方法A：用存储过程批量查询数据

创建存储过程循环遍历关联表，自动执行查询并返回数据（如需写入文件可结合INTO OUTFILE）：

DELIMITER //
CREATE PROCEDURE ExportUserRelatedData(IN dbName VARCHAR(255), IN userNamePattern VARCHAR(255))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tblName VARCHAR(255);
    DECLARE fkColName VARCHAR(255);
    DECLARE relTables CURSOR FOR 
        SELECT TABLE_NAME, COLUMN_NAME
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
        WHERE REFERENCED_TABLE_SCHEMA = dbName 
        AND REFERENCED_TABLE_NAME = 'user';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN relTables;
    table_loop: LOOP
        FETCH relTables INTO tblName, fkColName;
        IF done THEN
            LEAVE table_loop;
        END IF;
        SET @query = CONCAT(
            'SELECT * FROM `', tblName, '` ',
            'WHERE `', fkColName, '` IN (',
                'SELECT id FROM `user` WHERE name LIKE "', userNamePattern, '"',
            ');'
        );
        PREPARE stmt FROM @query;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE relTables;
END //
DELIMITER ;

-- 调用存储过程，替换参数为你的数据库名和用户组匹配规则
CALL ExportUserRelatedData('db_name', '%test%');

方法B：用Shell脚本批量导出为.sql文件

如果需要直接生成带INSERT语句的导出文件，用mysqldump配合脚本更高效：

#!/bin/bash
# 配置参数
DB_NAME="db_name"
USER="your_mysql_username"
PASSWORD="your_mysql_password"
USER_PATTERN="%test%"
OUTPUT_FILE="user_related_data.sql"

# 清空输出文件
> $OUTPUT_FILE

# 获取所有关联表和外键列（格式：表名:外键列名）
RELATIONSHIPS=$(mysql -u $USER -p$PASSWORD -D $DB_NAME -s -e "SELECT CONCAT(TABLE_NAME, ':', COLUMN_NAME) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = '$DB_NAME' AND REFERENCED_TABLE_NAME = 'user';")

# 遍历导出每个关联表的匹配数据
for REL in $RELATIONSHIPS; do
    TBL=$(echo $REL | cut -d: -f1)
    FK_COL=$(echo $REL | cut -d: -f2)
    echo "导出表 $TBL 的数据..."
    mysqldump -u $USER -p$PASSWORD $DB_NAME $TBL --where="$FK_COL IN (SELECT id FROM user WHERE name LIKE '$USER_PATTERN')" >> $OUTPUT_FILE
done

# 导出目标用户组本身的数据
echo "导出user表中匹配的用户数据..."
mysqldump -u $USER -p$PASSWORD $DB_NAME user --where="name LIKE '$USER_PATTERN'" >> $OUTPUT_FILE

echo "所有数据已导出到 $OUTPUT_FILE"

注意事项 #

• 上述方法仅处理直接关联user的表，若需要间接关联数据（比如comments关联posts，posts关联user），需额外递归查询表的依赖关系。
• 执行操作时确保MySQL账号拥有足够权限（SELECT、FILE、EXECUTE等）。

内容的提问来源于stack exchange，提问作者Sergi