让mysql_fetch_assoc自动检测返回数据类型?

我认为一个好的策略是通过编程确定表中每个列的数据类型,并相应地转换返回的结果。这将允许您以更一致、更简单的方式与数据库交互,同时仍然可以控制变量存储正确的数据类型。

您可以使用mysql_fetch_field()获取一个包含表列元数据的对象,然后将字符串转换回所需的类型。

//run query and get field information about the row in the table
$meta = mysql_fetch_field($result, $i);

//get the field type of the current column
$fieldType = $meta->type

以下是一个完整的示例: http://us2.php.net/manual/en/function.mysql-fetch-field.php

如果使用OO(面向对象)技术,可以在setter()方法中创建具有此功能的类,这样就不必有重复的代码。

只是贡献一点 小改进 根据mastermind202的答案,可以处理更多的数据类型。感谢主脑做了这项繁重的工作!

function cast_query_results($rs) {
    $fields = mysqli_fetch_fields($rs);
    $data = array();
    $types = array();
    foreach($fields as $field) {
        switch($field->type) {
            case MYSQLI_TYPE_NULL:
                $types[$field->name] = 'null';
                break;
            case MYSQLI_TYPE_BIT:
                $types[$field->name] = 'boolean';
                break;
            case MYSQLI_TYPE_TINY:
            case MYSQLI_TYPE_SHORT:
            case MYSQLI_TYPE_LONG:
            case MYSQLI_TYPE_INT24:
            case MYSQLI_TYPE_LONGLONG:
                $types[$field->name] = 'int';
                break;
            case MYSQLI_TYPE_FLOAT:
            case MYSQLI_TYPE_DOUBLE:
                $types[$field->name] = 'float';
                break;
            default:
                $types[$field->name] = 'string';
                break;
        }
    }
    while($row=mysqli_fetch_assoc($rs)) array_push($data,$row);
    for($i=0;$i<count($data);$i++) {
        foreach($types as $name => $type) {
            settype($data[$i][$name], $type);
        }
    }
    return $data;
}   

用法示例:

$db = mysqli_connect(...);
$rs = mysqli_query($db, "SELECT ...");
$results = cast_query_results($rs);

返回具有正确类型字段的行的关联数组

您可以围绕mdb2构建一个特定于mysql的层,该层使用 SHOW COLUMNS 命令,但这有点违背了使用mdb2的目的。

还要记住,mysql支持的整数远远超出PHP的范围(UNSIGNED BIGINT为64位;PHP最多支持64位) 签署 bcmath

$rs 并获取铸造数据的assoc数组作为返回:

function cast_query_results($rs) {
    $fields = mysqli_fetch_fields($rs);
    $data = array();
    $types = array();
    foreach($fields as $field) {
        switch($field->type) {
            case 3:
                $types[$field->name] = 'int';
                break;
            case 4:
                $types[$field->name] = 'float';
                break;
            default:
                $types[$field->name] = 'string';
                break;
        }
    }
    while($row=mysqli_fetch_assoc($rs)) array_push($data,$row);
    for($i=0;$i<count($data);$i++) {
        foreach($types as $name => $type) {
            settype($data[$i][$name], $type);
        }
    }
    return $data;
}

用法示例:

$dbconn = mysqli_connect('localhost','user','passwd','tablename');
$rs = mysqli_query($dbconn, "SELECT * FROM Matches");
$matches = cast_query_results($rs);
// $matches is now a assoc array of rows properly casted to ints/floats/strings