PHP历_批操SQL 数据库表及字段信息2

<link rel="stylesheet" href="/_/_js/layui/css/layui.css">
<?php
session_start();
define('CHAOYI',true);
require  $_SERVER['DOCUMENT_ROOT'].'/_/_php/data/database.php';
require ROOT.'_php/data/common.php';
require ROOT.'_php/data/config.php';

// 手动设置数据库名（如果自动获取有问题）
$dbName = '';
if (!empty($database_config['dbname'])) {
    $dbName = $database_config['dbname'];
}
// 如果配置文件中获取不到，手动设置（请根据实际情况修改）
// $dbName = 'your_actual_database_name';

// 调试模式
$debugMode = true;

// 分页参数
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$perPage = isset($_GET['per_page']) ? (int)$_GET['per_page'] : 5;

// 错误信息存储
$errors = [];
$tables = [];
$totalTables = 0;
$totalPages = 0;
$allTables = [];

// 检查连接
if ($conn->connect_error) {
    $errors[] = "数据库连接失败: " . $conn->connect_error;
} else {
    // 设置字符集
    if (!$conn->set_charset("utf8")) {
        $errors[] = "设置字符集失败: " . $conn->error;
    } else {
        // 1. 获取所有表名和表备注
        $tablesResult = $conn->query("SHOW TABLES");
        if (!$tablesResult) {
            $errors[] = "获取表列表失败: " . $conn->error;
        } else {
            // 存储所有表名和备注
            while ($table = $tablesResult->fetch_row()) {
                $tableName = $table[0];

                // 获取表备注
                $tableComment = '';
                $statusQuery = "SHOW TABLE STATUS LIKE '" . $conn->real_escape_string($tableName) . "'";
                $statusResult = $conn->query($statusQuery);
                if ($statusResult && $statusResult->num_rows > 0) {
                    $statusData = $statusResult->fetch_assoc();
                    $tableComment = $statusData['Comment'];
                    $statusResult->free();
                } else {
                    if ($debugMode) {
                        $errors[] = "获取表[$tableName]备注失败: " . $conn->error . " (SQL: $statusQuery)";
                    }
                }

                $allTables[] = [
                    'name' => $tableName,
                    'comment' => $tableComment
                ];
            }
            $tablesResult->free();

            // 计算总数和总页数
            $totalTables = count($allTables);
            $totalPages = ceil($totalTables / $perPage);

            // 2. 手动分页
            $offset = ($page - 1) * $perPage;
            $currentPageTables = array_slice($allTables, $offset, $perPage);

            // 3. 处理当前页的表和字段
            foreach ($currentPageTables as $tableInfo) {
                $tableName = $tableInfo['name'];
                $tableData = [
                    'name' => $tableName,
                    'comment' => $tableInfo['comment'],
                    'fields' => [],
                    'error' => ''
                ];

                // 方法1: 使用SHOW FULL COLUMNS获取字段信息和备注（推荐）
                $fieldsQuery = "SHOW FULL COLUMNS FROM `" . $conn->real_escape_string($tableName) . "`";
                $fieldsResult = $conn->query($fieldsQuery);

                if (!$fieldsResult) {
                    $tableData['error'] = "获取字段失败: " . $conn->error . " (SQL: $fieldsQuery)";

                    // 方法2: 如果方法1失败，使用DESCRIBE + 备用查询
                    $fieldsQuery2 = "DESCRIBE `" . $conn->real_escape_string($tableName) . "`";
                    $fieldsResult2 = $conn->query($fieldsQuery2);

                    if ($fieldsResult2) {
                        while ($field = $fieldsResult2->fetch_assoc()) {
                            // 备用方法获取字段备注
                            $fieldComment = '';
                            if (!empty($dbName)) {
                                $commentQuery = "SELECT column_comment 
                                                FROM information_schema.columns 
                                                WHERE table_schema = '" . $conn->real_escape_string($dbName) . "' 
                                                AND table_name = '" . $conn->real_escape_string($tableName) . "' 
                                                AND column_name = '" . $conn->real_escape_string($field['Field']) . "'";

                                $commentResult = $conn->query($commentQuery);
                                if ($commentResult && $commentResult->num_rows > 0) {
                                    $commentData = $commentResult->fetch_assoc();
                                    $fieldComment = $commentData['column_comment'];
                                    $commentResult->free();
                                } elseif ($debugMode) {
                                    $tableData['error'] .= "\n获取字段[$field[Field]]备注失败: " . $conn->error . " (SQL: $commentQuery)";
                                }
                            }

                            $field['Comment'] = $fieldComment;
                            $tableData['fields'][] = $field;
                        }
                        $fieldsResult2->free();
                    }
                } else {
                    // 从SHOW FULL COLUMNS结果中获取字段和备注
                    while ($field = $fieldsResult->fetch_assoc()) {
                        // 映射字段名以保持兼容性
                        $tableData['fields'][] = [
                            'Field' => $field['Field'],
                            'Type' => $field['Type'],
                            'Null' => $field['Null'],
                            'Key' => $field['Key'],
                            'Default' => $field['Default'],
                            'Extra' => $field['Extra'],
                            'Comment' => $field['Comment'] // 直接从结果获取备注
                        ];
                    }
                    $fieldsResult->free();
                }

                $tables[] = $tableData;
            }
        }
        // 关闭连接
        $conn->close();
    }
}
?>
<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <title>数据库表及字段信息</title>
    <style>
        body {
            font-family: Arial, sans-serif;
            margin: 20px;
            line-height: 1.6;
        }
        .pagination {
            margin: 20px 0;
            text-align: center;
        }
        .pagination a {
            display: inline-block;
            padding: 8px 16px;
            margin: 0 4px;
            border: 1px solid #ddd;
            text-decoration: none;
            color: #333;
        }
        .pagination a.active {
            background-color: #4CAF50;
            color: white;
            border: 1px solid #4CAF50;
        }
        .pagination a:hover:not(.active) {
            background-color: #ddd;
        }
        table {
            width: 100%;
            border-collapse: collapse;
            margin-bottom: 30px;
            box-shadow: 0 2px 3px rgba(0,0,0,0.1);
        }
        th, td {
            padding: 12px 15px;
            text-align: left;
            border-bottom: 1px solid #ddd;
        }
        th {
            background-color: #f2f2f2;
            font-weight: bold;
        }
        .table-header {
            background-color: #4CAF50;
            color: white;
        }
        .table-name {
            font-size: 1.2em;
            margin-top: 30px;
            color: #2c3e50;
        }
        .table-comment {
            color: #666;
            font-style: italic;
            margin: 5px 0 15px 0;
            padding: 8px;
            background-color: #f9f9f9;
            border-left: 3px solid #4CAF50;
        }
        .info {
            margin: 10px 0;
            color: #666;
        }
        .error {
            color: #dc3545;
            background-color: #f8d7da;
            padding: 10px 15px;
            border: 1px solid #f5c6cb;
            border-radius: 4px;
            margin: 10px 0;
            white-space: pre-wrap;
        }
        .debug {
            color: #0c5460;
            background-color: #d1ecf1;
            padding: 10px 15px;
            border: 1px solid #bee5eb;
            border-radius: 4px;
            margin: 10px 0;
        }
        .comment-column {
            width: 200px;
        }
    </style>
</head>
<body>
<h1>数据库表及字段信息</h1>

<?php if ($debugMode): ?>
    <div class="debug">
        <strong>调试信息:</strong><br>
        数据库: <?php echo htmlspecialchars($dbName); ?><br>
        页码: <?php echo $page; ?>, 每页数量: <?php echo $perPage; ?><br>
        总表数: <?php echo $totalTables; ?><br>
        数据库名是否设置: <?php echo empty($dbName) ? '否' : '是'; ?>
    </div>
<?php endif; ?>

<?php if (!empty($errors)): ?>
    <?php foreach ($errors as $error): ?>
        <div class="error"><?php echo htmlspecialchars($error); ?></div>
    <?php endforeach; ?>
<?php endif; ?>

<div class="info">
    总表数: <?php echo $totalTables; ?> |
    当前页: <?php echo $page; ?>/<?php echo $totalPages; ?> |
    每页显示: <?php echo $perPage; ?> 个表
</div>

<?php if (!empty($tables)): ?>
    <?php foreach ($tables as $table): ?>
        <h2 class="table-name">表名: <?php echo htmlspecialchars($table['name']); ?></h2>

        <?php if (!empty($table['comment'])): ?>
            <div class="table-comment">表备注: <?php echo htmlspecialchars($table['comment']); ?></div>
        <?php endif; ?>

        <?php if (!empty($table['error'])): ?>
            <div class="error"><?php echo htmlspecialchars($table['error']); ?></div>
        <?php endif; ?>

        <?php if (!empty($table['fields'])): ?>
            <table>
                <tr class="table-header">
                    <th>字段名</th>
                    <th>类型</th>
                    <th>是否为空</th>
                    <th>键</th>
                    <th>默认值</th>
                    <th>额外信息</th>
                    <th class="comment-column">备注</th>
                </tr>
                <?php foreach ($table['fields'] as $field): ?>
                    <tr>
                        <td><?php echo htmlspecialchars($field['Field']); ?></td>
                        <td><?php echo htmlspecialchars($field['Type']); ?></td>
                        <td><?php echo ($field['Null'] === 'YES' ? '是' : '否'); ?></td>
                        <td><?php echo htmlspecialchars($field['Key']); ?></td>
                        <td><?php echo (is_null($field['Default']) ? 'NULL' : htmlspecialchars($field['Default'])); ?></td>
                        <td><?php echo htmlspecialchars($field['Extra']); ?></td>
                        <td><?php echo htmlspecialchars($field['Comment'] ?? ''); ?></td>
                    </tr>
                <?php endforeach; ?>
            </table>
        <?php else: ?>
            <p>该表没有字段信息</p>
        <?php endif; ?>
    <?php endforeach; ?>
<?php else: ?>
    <?php if (empty($errors)): ?>
        <p>没有找到任何表</p>
    <?php endif; ?>
<?php endif; ?>

<!-- 分页导航 -->
<?php if ($totalPages > 1): ?>
    <div class="pagination">
        <?php if ($page > 1): ?>
            <a href="?page=<?php echo $page - 1; ?>&per_page=<?php echo $perPage; ?>">上一页</a>
        <?php endif; ?>

        <?php
        // 显示页码（只显示当前页附近的页码）
        for ($i = max(1, $page - 2); $i <= min($totalPages, $page + 2); $i++) {
            if ($i == $page) {
                echo '<a href="?page=' . $i . '&per_page=' . $perPage . '" class="active">' . $i . '</a>';
            } else {
                echo '<a href="?page=' . $i . '&per_page=' . $perPage . '">' . $i . '</a>';
            }
        }
        ?>

        <?php if ($page < $totalPages): ?>
            <a href="?page=<?php echo $page + 1; ?>&per_page=<?php echo $perPage; ?>">下一页</a>
        <?php endif; ?>
    </div>
<?php endif; ?>
</body>
</html>

效果图：