如何在 PHP 中将整数和数字列从 MySQL 返回为整数和数字? [英] How do I return integer and numeric columns from MySQL as integers and numerics in PHP?

问题描述

问题是 DB 查询应该在 PHP 中为整数列返回整数数据类型.相反，查询将每一列作为字符串类型返回.

The problem is that a DB query should return integer data types in PHP for integer columns. Instead the query returns every column as a string type.

我已确保PDO::ATTR_STRINGIFY_FETCHES"if false 只是为了确保结果不会被转换为字符串.

I've ensured that "PDO::ATTR_STRINGIFY_FETCHES" if false just to make sure results aren't being cast to string.

我看过的答案:

• 这是不可能的
  • 不，它可以在安装了 PHP/MySQL 的 Mac OS X 上运行
  • 不，我不会那样做
  • 我的数据输出为 JSON 并被许多其他服务使用，有些需要正确格式的数据

  根据我的研究，我了解到这是一个驱动程序实现问题.

  From my research I understand that this is a driver implementation issue.

  许多消息来源声称 MySQL 本机驱动程序不支持返回数字类型.这似乎不是真的，因为它适用于 Mac OS X.除非他们的意思是说Linux 上的 MySQL 本机驱动程序不支持该功能".

  Many sources claim that the MySQL native driver does not support returning numeric types. This doesn't seem true since it works on Mac OS X. Unless they mean to say that "the MySQL native driver on Linux doesn't support the feature".

  这意味着我在 Mac OS X 上安装的驱动程序/环境有一些特别之处.我一直试图找出差异以应用修复程序，但我对如何检查的知识有限这些东西.

  This implies that there is something special about the driver/environment I have installed on Mac OS X. I've been trying to identify the differences in order to apply a fix but I'm limited by my knowledge of how to check these things.

  • OS X 上的 PHP 是通过 Home Brew 编译和安装的
  • Ubuntu 上的 PHP 是通过apt-get install php5-dev"安装的
  • OS X 上的 PHP 正在连接到同样在 OS X 上运行的 MySQL 服务器
    • 服务器版本:5.1.71-log 源码分发
    • 服务器版本:5.1.66-0+squeeze1 (Debian)

    • 版本:10.04.1

    • Version: 10.04.1

    PHP 5.4.21-1+debphp.org~lucid+1 (cli)(构建时间:2013 年 10 月 21 日 08:14:37)

    PHP 5.4.21-1+debphp.org~lucid+1 (cli) (built: Oct 21 2013 08:14:37)

    php -i

    pdo_mysql

    用于 MySQL 的 PDO 驱动程序 =>启用客户端 API 版本 =>5.1.72

    PDO Driver for MySQL => enabled Client API version => 5.1.72

    • 10.7.5

    • 10.7.5

    PHP 5.4.16 (cli)(构建时间:2013 年 8 月 22 日 09:05:58)

    PHP 5.4.16 (cli) (built: Aug 22 2013 09:05:58)

    php -i

    pdo_mysql

    用于 MySQL 的 PDO 驱动程序 =>启用客户端 API 版本 =>mysqlnd 5.0.10 - 20111026 - $Id: e707c415db32080b3752b232487a435ee0372157 $

    PDO Driver for MySQL => enabled Client API version => mysqlnd 5.0.10 - 20111026 - $Id: e707c415db32080b3752b232487a435ee0372157 $

    PDO::ATTR_CASE => PDO::CASE_NATURAL,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_ORACLE_NULLS => PDO::NULL_NATURAL,
    PDO::ATTR_STRINGIFY_FETCHES => false,
    PDO::ATTR_EMULATE_PREPARES => false,
    

    推荐答案

    解决方案是确保您使用的是 mysqlnd php 驱动程序.

    The solution is to ensure that you are using the mysqlnd driver for php.

    查看php -i 时，不会 提到mysqlnd".pdo_mysql 部分将包含如下内容:

    When viewing php -i, there will be no mention of "mysqlnd". The pdo_mysql section will have something like this:

    pdo_mysql
    
    PDO Driver for MySQL => enabled Client API version => 5.1.72
    

    你如何安装它?

    大多数 L/A/M/P 安装指南都建议 apt-get install php5-mysql 但 MySQL 的本机驱动程序由不同的包安装:php5-mysqlnd.我发现这在 ppa:ondrej/php5-oldstable 中可用.

    How do you install it?

    Most installation guides for L/A/M/P suggest apt-get install php5-mysql but the native driver for MySQL is installed by a different package: php5-mysqlnd. I found that this was available with the ppa:ondrej/php5-oldstable.

    要切换到新驱动程序(在 Ubuntu 上):

    To switch to the new driver (on Ubuntu):

    • 删除旧驱动程序:
      apt-get 删除 php5-mysql
    • 安装新驱动程序:
      apt-get install php5-mysqlnd
    • 重启apache2:
      服务apache2重启

    现在 php -i 将在 pdo_mysql 部分明确提及mysqlnd":

    Now php -i will mention "mysqlnd" explicitly in the pdo_mysql section:

    pdo_mysql
    
    PDO Driver for MySQL => enabled
    Client API version => mysqlnd 5.0.10 - 20111026 - $Id:      e707c415db32080b3752b232487a435ee0372157 $
    

    PDO 设置

    确保 PDO::ATTR_EMULATE_PREPARES 为 false(检查您的默认值或设置它):
    $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

    PDO settings

    Ensure that PDO::ATTR_EMULATE_PREPARES is false (check your defaults or set it):
    $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

    确保 PDO::ATTR_STRINGIFY_FETCHES 为 false(检查您的默认值或设置它):
    $pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);

    Ensure that PDO::ATTR_STRINGIFY_FETCHES is false (check your defaults or set it):
    $pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);

    • 浮点类型(FLOAT、DOUBLE)作为 PHP 浮点数返回.
    • 整数类型(INTEGER、INT、SMALLINT、TINYINT、MEDIUMINT、BIGINT †)作为 PHP 整数返回.
    • 定点类型(DECIMAL、NUMERIC)作为字符串返回.

    † 值大于 64 位有符号整数 (9223372036854775807) 的 BIGINT 将作为字符串返回(或 32 位系统上的 32 位)

    † BIGINTs with a value greater than a 64 bit signed int (9223372036854775807) will return as a string (or 32 bits on a 32 bit system)

        object(stdClass)[915]
          public 'integer_col' => int 1
          public 'double_col' => float 1.55
          public 'float_col' => float 1.5
          public 'decimal_col' => string '1.20' (length=4)
          public 'bigint_col' => string '18446744073709551615' (length=20)
    

    这篇关于如何在 PHP 中将整数和数字列从 MySQL 返回为整数和数字?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！