mysql_query是否保留返回时在表中创建的数据类型?

时间:2021-01-28 00:11:21

I have a table in mysql:

我在mysql中有一个表:

CREATE TABLE user (id INT, name VARCHAR(250));

I query the table:

我查询表:

$result = mysql_query("SELECT id,name FROM user");

I gather the results:

我收集结果:

while($row = mysql_fetch_assoc($result) $rv[] = $row;

And I return the data:

我返回数据:

echo json_encode($rv);

The problem is that the id returns as string and not int, even before the json_encode. If i force $row['id'] = (int)$row['id']; - it works fine.

问题是id返回为字符串而不是int,甚至在json_encode之前。如果我强制$ row ['id'] =(int)$ row ['id']; - 它工作正常。

How can i force mysql to return the correct data type in the $row?

我如何强制mysql在$行中返回正确的数据类型?

10x.

10X。

2 个解决方案

#1


2  

PDO->bindColumn can this

PDO-> bindColumn就可以了

$stmt = $dbh->prepare($sql);

$stmt->bindColumn('int_col', $intVar, PDO::PARAM_INT);
$stmt->bindColumn('string_col', $strVar, PDO::PARAM_STR);

$stmt->execute();

$stmt->fetch(PDO::FETCH_BOUND);

$intVar will be automatically converted to int

$ intVar将自动转换为int

#2


3  

Unfortunatly, with PHP <= 5.2, the MySQL functions/classes (i.e. mysql_*, mysqli_*, and PDO) are based on libmysql -- a C library which provides support for communication with the MySQL server.

不幸的是,在PHP <= 5.2的情况下,MySQL函数/类(即mysql_ *,mysqli_ *和PDO)基于libmysql--一个C库,它为与MySQL服务器的通信提供支持。

Functions using that library always return strings, even for integer and float columns -- and there is nothing you can do about that : the only solution is to have some "mapping layer" (like an ORM) that "knows", on the PHP side, which type each column should be, and will do the conversion each time some data is fetched from the database.

使用该库的函数总是返回字符串,即使对于整数和浮点列 - 并且没有什么可以做的:唯一的解决方案是在PHP上有一些“知道”的“映射层”(如ORM) side,每列应该是哪种类型,并且每次从数据库中提取一些数据时都会进行转换。

Note : the way you're inserting data doesn't change anything about the fact that you'll get strings when fetching data from the DB.

注意:插入数据的方式不会改变从数据库中获取数据时获取字符串的事实。


With PHP >= 5.3, functions that communicate with a MySQL server can use mysqlnd (MySQL Native Driver) instead of libmysql -- this has to be configured at compile-time, though.

在PHP> = 5.3的情况下,与MySQL服务器通信的函数可以使用mysqlnd(MySQL Native Driver)而不是libmysql - 但这必须在编译时配置。

And being able to communicate with "native" data-types, under certain circumstances, is one of the nice things that mysqlnd provides ; about that, there should be a couple of interesting informations is this article : PHP: New network traffic, CPU and memory savings with mysqlnd.

在某些情况下,能够与“本机”数据类型进行通信是mysqlnd提供的一件好事;关于这一点,本文应该有一些有趣的信息:PHP:使用mysqlnd节省新的网络流量,CPU和内存。


To makes things short :

简而言之:

  • With PHP 5.2, you'll only get strings from the database ; and you'll have to do some type-conversion yourself
  • 使用PHP 5.2,您只能从数据库中获取字符串;你必须自己做一些类型转换
  • With PHP 5.3, you might get native datatypes -- at least in some cases.
  • 使用PHP 5.3,您可能会获得本机数据类型 - 至少在某些情况下。

#1


2  

PDO->bindColumn can this

PDO-> bindColumn就可以了

$stmt = $dbh->prepare($sql);

$stmt->bindColumn('int_col', $intVar, PDO::PARAM_INT);
$stmt->bindColumn('string_col', $strVar, PDO::PARAM_STR);

$stmt->execute();

$stmt->fetch(PDO::FETCH_BOUND);

$intVar will be automatically converted to int

$ intVar将自动转换为int

#2


3  

Unfortunatly, with PHP <= 5.2, the MySQL functions/classes (i.e. mysql_*, mysqli_*, and PDO) are based on libmysql -- a C library which provides support for communication with the MySQL server.

不幸的是,在PHP <= 5.2的情况下,MySQL函数/类(即mysql_ *,mysqli_ *和PDO)基于libmysql--一个C库,它为与MySQL服务器的通信提供支持。

Functions using that library always return strings, even for integer and float columns -- and there is nothing you can do about that : the only solution is to have some "mapping layer" (like an ORM) that "knows", on the PHP side, which type each column should be, and will do the conversion each time some data is fetched from the database.

使用该库的函数总是返回字符串,即使对于整数和浮点列 - 并且没有什么可以做的:唯一的解决方案是在PHP上有一些“知道”的“映射层”(如ORM) side,每列应该是哪种类型,并且每次从数据库中提取一些数据时都会进行转换。

Note : the way you're inserting data doesn't change anything about the fact that you'll get strings when fetching data from the DB.

注意:插入数据的方式不会改变从数据库中获取数据时获取字符串的事实。


With PHP >= 5.3, functions that communicate with a MySQL server can use mysqlnd (MySQL Native Driver) instead of libmysql -- this has to be configured at compile-time, though.

在PHP> = 5.3的情况下,与MySQL服务器通信的函数可以使用mysqlnd(MySQL Native Driver)而不是libmysql - 但这必须在编译时配置。

And being able to communicate with "native" data-types, under certain circumstances, is one of the nice things that mysqlnd provides ; about that, there should be a couple of interesting informations is this article : PHP: New network traffic, CPU and memory savings with mysqlnd.

在某些情况下,能够与“本机”数据类型进行通信是mysqlnd提供的一件好事;关于这一点,本文应该有一些有趣的信息:PHP:使用mysqlnd节省新的网络流量,CPU和内存。


To makes things short :

简而言之:

  • With PHP 5.2, you'll only get strings from the database ; and you'll have to do some type-conversion yourself
  • 使用PHP 5.2,您只能从数据库中获取字符串;你必须自己做一些类型转换
  • With PHP 5.3, you might get native datatypes -- at least in some cases.
  • 使用PHP 5.3,您可能会获得本机数据类型 - 至少在某些情况下。