php mysql fetch assoc VS mysql fetch 数组

mysql fetch assoc VS mysql fetch array

提问by Saimon Avazian

Below are two methods commonly used in most php codes for fetch mysql data .

以下是大多数php代码中常用的两种获取mysql数据的方法。

1. mysql_fetch_array()
2. mysql_fetch_assoc()

1. mysql_fetch_array()
2. mysql_fetch_assoc()

Now, I have a big database. For fetching data in loops (while) what's faster and better ?

现在，我有一个大数据库。为了在循环 ( while) 中获取数据，什么更快更好？

I found this benchmark.

我找到了这个基准。

Which is your choice?

哪个是你的选择？

回答by The Coder

It depends on how your tables are setup:

这取决于您的表的设置方式：

mysql_fetch_array()essentially returns two arrays one with numeric index, one with associative string index.

mysql_fetch_array()本质上返回两个数组，一个带有数字索引，一个带有关联字符串索引。

So using mysql_fetch_array()without specifying MYSQL_ASSOCor MYSQL_NUM, or by specifying MYSQL_BOTHwill return two arrays (basically what mysql_fetch_assoc()and mysql_fetch_row()would return) so mysql_fetch_assoc()is faster.

因此，在mysql_fetch_array()不指定MYSQL_ASSOCor 的情况下使用MYSQL_NUM，或通过指定MYSQL_BOTH将返回两个数组（基本上是什么mysql_fetch_assoc()和mysql_fetch_row()将返回）所以mysql_fetch_assoc()速度更快。

If you have your table setup right and query written properly mysql_fetch_assoc()is the way to go, code readability wise $result['username']is easier to understand than $result[0].

如果您的表设置正确并且查询编写正确mysql_fetch_assoc()是要走的路，那么代码可读性$result['username']比$result[0].

回答by cyrusv

I prefer fetch_assoc. It returns an "associative array" (like a python dictionary). This means your array is indexed by string (in my usual case).

我更喜欢 fetch_assoc。它返回一个“关联数组”（如 python 字典）。这意味着您的数组是按字符串索引的（在我通常的情况下）。

The default for fetch_array gives both numbered indices and associative indices.

fetch_array 的默认值提供编号索引和关联索引。

But I never use the numbered indices, so I like to get it a tiny bit faster with fetch_assoc

但我从不使用编号索引，所以我喜欢用 fetch_assoc 让它快一点

回答by Linus Kleen

The "size" of the query results don't matter in this case.

在这种情况下，查询结果的“大小”无关紧要。

mysql_fetch_array()generally produces overhead by returning an associative array plusindexed results.

mysql_fetch_array()通常通过返回关联数组和索引结果来产生开销。

One should decide on the type of query and the desired results on whether to use mysql_fetch_array()or mysql_fetch_assoc().

应该决定查询的类型以及是否使用mysql_fetch_array()或所需的结果mysql_fetch_assoc()。

If the overhead is "neglectable" and I'm sure the query succeeds and I know there's only a single result, I occasionally do:

如果开销是“可忽略的”并且我确定查询成功并且我知道只有一个结果，我偶尔会这样做：

$q = mysql_query('SELECT `column1`,`column2` FROM `table` WHERE `id` = 123');
list($column1,$column2) = mysql_fetch_array($q);
mysql_free_result($q);

For iterative proceedings (like a whileloop), this just doesn't cut it. When there's no need for "quick-extracting" results (via a listoperator) and the result has to be further processed in code, I always use mysql_fetch_assoc()*.

对于迭代程序（如while循环），这并不能解决问题。当不需要“快速提取”结果（通过list运算符）并且结果必须在代码中进一步处理时，我总是使用mysql_fetch_assoc()*.

---

---

* When forced to actually usethe quite out-dated procedural data retrieval functions of PHP. There are alternatives.

* 当被迫实际使用PHP 相当过时的程序数据检索功能时。有替代品。

回答by Mark Baker

The benchark shown in your example uses mysql_fetch_array() without any argument to specify MYSQL_ASSOC or MYSQL_NUM, so it defaults to MYSQL_BOTH... this will rather skew the result as it has to load twice as many array elements as mysql_fetch_assoc(). So I'd suggest it isn't a valid benchmark.

您的示例中显示的基准使用 mysql_fetch_array() 没有任何参数来指定 MYSQL_ASSOC 或 MYSQL_NUM，因此它默认为 MYSQL_BOTH ......这会导致结果倾斜，因为它必须加载两倍于 mysql_fetch_assoc() 的数组元素。所以我建议它不是一个有效的基准。

In reality, there should be very little to differentiate between mysql_fetch_array() with MYSQL_ASSOC and mysql_fetch_assoc(), and it cerayinly won't be the biggest overhead in your script.

实际上，mysql_fetch_array() 与 MYSQL_ASSOC 和 mysql_fetch_assoc() 之间应该没有什么区别，而且它肯定不会成为脚本中最大的开销。

回答by Oblivion Datta

This Is the result of mysql_fetch_array()

这是mysql_fetch_array()的结果

$row['fieldname'] = 'some value';

or

或者

$row[0] = some value';

This Is the result of mysql_fetch_assoc()will only return

这是mysql_fetch_assoc()的结果只会返回

$row['fieldname'] = 'some value';

回答by Randrian

Well http://php.net/manual/en/function.mysql-fetch-assoc.phpstates

那么http://php.net/manual/en/function.mysql-fetch-assoc.php状态

Note: Performance

An important thing to note is that using mysql_fetch_assoc() is not significantly slower than using mysql_fetch_row(), while it provides a significant added value.

注意：性能

需要注意的重要一点是，使用 mysql_fetch_assoc() 并不比使用 mysql_fetch_row() 慢得多，但它提供了显着的附加值。

So the difference shouldn't be something to worry about.

因此，差异不应该是值得担心的。

回答by rmirabelle

An additional point to keep in mind is that fetch_assoc()may not return every column you expect. If 2 output columns would have the same name, only the last instance of that column will be retrieved by fetch_assoc(). For example, the following query:

要记住的另一点是fetch_assoc()可能不会返回您期望的每一列。如果 2 个输出列具有相同的名称，则仅检索该列的最后一个实例fetch_assoc()。例如，以下查询：

SELECT * FROM orders LEFT JOIN line_items ON orders.id = line_items.order_id

Assuming both tables have a column called id, the resulting associative array would have a key called idwhose value is set to line_items.idand you would not be able to retrieve orders.idfrom the result!

假设两个表都有一个名为 的列id，生成的关联数组将有一个名为id的键，其值设置为line_items.id并且您将无法orders.id从结果中检索！

You can circumvent this duplicate column names issue and continue to use fetch_assoc()by manually aliasing your SELECTcolumns, giving each column a unique alias:

您可以绕过这个重复的列名问题，并fetch_assoc()通过手动为您的SELECT列设置别名来继续使用，为每列提供一个唯一的别名：

SELECT 
o.id AS order_id, #unique alias
i.id AS line_item_id, #unique alias
o.date,
i.qty,
i.price
FROM orders o 
LEFT JOIN line_items i ON i.order_id = o.id

Or you can switch to using fetch_array(), which will allow you to access either idby index instead of by key

或者您可以切换到 using fetch_array()，这将允许您id通过索引而不是键访问

回答by Nimes

mysql_fetch_array() vs mysql_fetch_assoc()

mysql_fetch_array() 与 mysql_fetch_assoc()

mysql_fetch_array(): Returns an array that corresponds to the fetched row and moves the internal data pointer ahead. for better documentation click here! to learn moremysql_fetch_assoc(): is equivalent to calling mysql_fetch_array() with MYSQL_ASSOC for the optional second parameter. It only returns an associative array.

mysql_fetch_array()：返回一个对应取到的行的数组，并将内部数据指针向前移动。 要获得更好的文档，请单击此处！了解更多mysql_fetch_assoc(): 相当于调用 mysql_fetch_array() 和 MYSQL_ASSOC 作为可选的第二个参数。它只返回一个关联数组。

回答by Ryan P

mysql_fetch_assoc()would probably be the better choice. There is a slight performance hit (the mysql extension needs to look up the column names, but that should only happen once, no matter how many rows you have), but probably not enough to justify using mysql_fetch_array()instead. mysql_fetch_array()can be useful if you're only selecting one column though.

mysql_fetch_assoc()可能是更好的选择。有轻微的性能损失（mysql 扩展需要查找列名，但这应该只发生一次，无论您有多少行），但可能不足以证明使用它是合理的mysql_fetch_array()。mysql_fetch_array()如果您只选择一列，则可能很有用。

回答by christophmccann

I suppose it depends on your definition of big. From that benchmark you can see that it is only before you perform upwards of 1,000,000 million fetches that you actually get a measurable difference? Is your database that big? If not, then go with what is easier for you to use (which is probably to fetch an associative array).

我想这取决于你对大的定义。从该基准测试中您可以看到，只有在执行超过 1,000,000 百万次提取之前，您才能真正获得可测量的差异？你的数据库有那么大吗？如果没有，那么使用更容易使用的方法（这可能是获取关联数组）。

Another point to consider, if it is so big that there is a measurable difference then I would be getting a way from using functions like that all together. Consider using MySQLi or even better use PDO!

要考虑的另一点是，如果它是如此之大以至于存在可测量的差异，那么我将有办法将这些功能一起使用。考虑使用 MySQLi 甚至更好地使用 PDO！