MySQL非规范化表数据高效提取与PHP处理教程

$mysqli = new mysqli("localhost", "dbuser", "dbpass", "dbname");
$mysqli->set_charset("utf8mb4");

$fields = $mysqli->query("SELECT app_id, field_id, value FROM name_of_table");
$results = $fields->fetch_all(MYSQLI_ASSOC); // 获取所有数据

$users_data = [];
foreach ($results as $row) {
    $app_id = $row['app_id'];
    $field_id = $row['field_id'];
    $value = $row['value'];

    if (!isset($users_data[$app_id])) {
        $users_data[$app_id] = [
            'first_name' => null,
            'last_name' => null,
        ];
    }

    // 根据field_id分配值
    if ($field_id == 9) { // 假设9是First Name
        $users_data[$app_id]['first_name'] = $value;
    } elseif ($field_id == 15) { // 假设15是Last Name
        $users_data[$app_id]['last_name'] = $value;
    }
}

// 此时 $users_data 包含了所有用户的姓和名
foreach ($users_data as $app_id => $data) {
    echo "用户ID: " . $app_id . ", 姓: " . ($data['first_name'] ?? 'N/A') . ", 名: " . ($data['last_name'] ?? 'N/A') . "<br>";
}

$mysqli->close();

// 假设 $count 是最大的 app_id 或需要处理的 app_id 范围
for ($i = $count; $i >= ($count - 1000); $i--) { // 循环1000次
    // 每次循环都执行一次数据库查询
    $data = $mysqli->query("SELECT field_id, value FROM name_of_table WHERE app_id = $i AND field_id IN (9, 15)");
    $names = $data->fetch_all(MYSQLI_NUM);

    $first_name = null;
    $last_name = null;

    foreach ($names as list($field_id, $value)) {
        switch ($field_id) {
            case 9:
                $first_name = $value;
                break;
            case 15:
                $last_name = $value;
                break;
        }
    }
    // 输出或存储 $first_name, $last_name
    if ($first_name || $last_name) {
        echo "用户ID: " . $i . ", 姓: " . ($first_name ?? 'N/A') . ", 名: " . ($last_name ?? 'N/A') . "<br>";
    }
}
$mysqli->close();

SELECT
    t.app_id,
    MAX(CASE WHEN t.field_id = 9 THEN t.value ELSE NULL END) AS first_name,
    MAX(CASE WHEN t.field_id = 15 THEN t.value ELSE NULL END) AS last_name
FROM
    name_of_table t
WHERE
    t.field_id IN (9, 15) -- 仅筛选出我们关心的field_id，减少处理的数据量
GROUP BY
    t.app_id;

$mysqli = new mysqli("localhost", "dbuser", "dbpass", "dbname");
$mysqli->set_charset("utf8mb4");

$sql = "
    SELECT
        t.app_id,
        MAX(CASE WHEN t.field_id = 9 THEN t.value ELSE NULL END) AS first_name,
        MAX(CASE WHEN t.field_id = 15 THEN t.value ELSE NULL END) AS last_name
    FROM
        name_of_table t
    WHERE
        t.field_id IN (9, 15)
    GROUP BY
        t.app_id
";

$result = $mysqli->query($sql);

if ($result) {
    while ($row = $result->fetch_assoc()) {
        echo "用户ID: " . $row['app_id'] . ", 姓: " . ($row['first_name'] ?? 'N/A') . ", 名: " . ($row['last_name'] ?? 'N/A') . "<br>";
    }
    $result->free(); // 释放结果集
} else {
    echo "查询失败: " . $mysqli->error;
}

$mysqli->close();

$mysqli = new mysqli("localhost", "dbuser", "dbpass", "dbname");
$mysqli->set_charset("utf8mb4");

// 仅查询我们关心的字段和field_id
$stmt = $mysqli->prepare("SELECT app_id, field_id, value FROM name_of_table WHERE field_id IN (?, ?)");
$field_id_first_name = 9;
$field_id_last_name = 15;
$stmt->bind_param("ii", $field_id_first_name, $field_id_last_name);
$stmt->execute();
$result = $stmt->get_result(); // 获取结果集

$users_data = [];
while ($row = $result->fetch_assoc()) {
    $app_id = $row['app_id'];
    $field_id = $row['field_id'];
    $value = $row['value'];

    // 初始化用户数据结构
    if (!isset($users_data[$app_id])) {
        $users_data[$app_id] = [
            'first_name' => null,
            'last_name' => null,
        ];
    }

    // 根据field_id分配值
    if ($field_id == $field_id_first_name) {
        $users_data[$app_id]['first_name'] = $value;
    } elseif ($field_id == $field_id_last_name) {
        $users_data[$app_id]['last_name'] = $value;
    }
}

// 此时 $users_data 包含了所有用户的姓和名，可以进行后续处理
foreach ($users_data as $app_id => $data) {
    echo "用户ID: " . $app_id . ", 姓: " . ($data['first_name'] ?? 'N/A') . ", 名: " . ($data['last_name'] ?? 'N/A') . "<br>";
}

$stmt->close();
$mysqli->close();