Below are two methods commonly used in most php codes for fetch mysql data .
以下是大多数PHP代码中常用的两种获取mysql数据的方法。
mysql_fetch_array()
mysql_fetch_assoc()
Now, I have a big database. For fetching data in loops (while
) what's faster and better ?
现在,我有一个大数据库。为了在循环中获取数据(同时)什么更快更好?
I found this benchmark.
我找到了这个基准。
Which is your choice?
你选择哪个?
15
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_ASSOC
or MYSQL_NUM
, or by specifying MYSQL_BOTH
will return two arrays (basically what mysql_fetch_assoc()
and mysql_fetch_row()
would return) so mysql_fetch_assoc()
is faster.
因此,使用mysql_fetch_array()而不指定MYSQL_ASSOC或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]更容易理解。
1
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让它快一点
1
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.
在你的例子中示出的使用benchark 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(),并且它绝对不会是脚本中最大的开销。
1
The "size" of the query results don't matter in this case.
在这种情况下,查询结果的“大小”无关紧要。
mysql_fetch_array()
generally produces overhead by returning an associative array plus indexed 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 while
loop), this just doesn't cut it. When there's no need for "quick-extracting" results (via a list
operator) and the result has to be further processed in code, I always use mysql_fetch_assoc()
*.
对于迭代过程(如while循环),这不会削减它。当不需要“快速提取”结果(通过列表运算符)并且结果必须在代码中进一步处理时,我总是使用mysql_fetch_assoc()*。
* When forced to actually use the quite out-dated procedural data retrieval functions of PHP. There are alternatives.
*当被迫实际使用PHP的过时的程序数据检索功能时。还有其他选择。
1
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';
0
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()非常有用。
0
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).
我想这取决于你对big的定义。从该基准测试中您可以看到,只有在您执行超过1,000,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!
0
Well http://php.net/manual/en/function.mysql-fetch-assoc.php states
好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.
所以差异不应该是值得担心的。
0
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 id
whose value is set to line_items.id
and you would not be able to retrieve orders.id
from 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 SELECT
columns, giving each column a unique alias:
您可以绕过这个重复的列名称问题,并通过手动别名SELECT列继续使用fetch_assoc(),为每列提供唯一的别名:
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 id
by index instead of by key
或者您可以切换到使用fetch_array(),这将允许您通过索引而不是按键访问id
0
mysql_fetch_array() vs mysql_fetch_assoc()
mysql_fetch_array()vs 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 more mysql_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_ASSOC调用mysql_fetch_array()作为可选的第二个参数。它只返回一个关联数组。