php 我可以只在 MYSQL 中选择一列而不是全部，以使其更快吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10404194/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can I just SELECT one column in MYSQL instead of all, to make it faster?
提问by eric01
I want to do something like this:
我想做这样的事情：
$query=mysql_query("SELECT userid FROM users WHERE username='$username'");
$the_user_id= .....
Because all I want is the user ID that corresponds to the username.
因为我想要的只是与用户名对应的用户 ID。
The usual way would be that:
通常的方法是：
$query=mysql_query("SELECT * FROM users WHERE username='$username'");
while ($row = mysql_fetch_assoc($query))
$the_user_id= $row['userid'];
But is there something more efficient that this? Thanks a lot, regards
但是还有比这更有效的方法吗？非常感谢，问候
回答by Daan
You've hit the nail right on the head: what you want to do is SELECT userid FROM users WHERE username='$username'. Try it - it'll work :)
你一针见血：你想做的是SELECT userid FROM users WHERE username='$username'。尝试一下 - 它会起作用:)
SELECT *is almost always a bad idea; it's much better to specify the names of the columns you want MySQL to return. Of course, you'll stil need to use mysql_fetch_assocor something similar to actually get the data into a PHP variable, but at least this saves MySQL the overhead of sending all these columns you won't be using anyway.
SELECT *几乎总是一个坏主意；最好指定您希望 MySQL 返回的列的名称。当然，您仍然需要使用mysql_fetch_assoc或类似的东西来实际将数据放入 PHP 变量中，但至少这为 MySQL 节省了发送所有这些列的开销，您无论如何都不会使用。
As an aside, you may want to use the newer mysqli library or PDO instead. These are also much more efficient than the old mysql library, which is being deprecated.
顺便说一句，您可能希望改用较新的 mysqli 库或 PDO。这些也比旧的 mysql 库更有效，后者正在被弃用。
回答by Sampson
Not only can you, but you should. Unfortunately too many young developers get in the habit of selecting far too much data. Once you run your query, you don't need to do a while loop since you only have one record coming back. Instead, just use the following:
不仅你可以，而且你应该。不幸的是，太多的年轻开发人员养成了选择过多数据的习惯。运行查询后，您无需执行 while 循环，因为只有一条记录返回。相反，只需使用以下内容：
$sql = sprintf( "SELECT userid FROM users WHERE username = '%s'", $username );
$result = mysql_query( $sql ) or die( mysql_error() );
$data = mysql_result( $result, 0 );
if ( $data ) echo $data; // first userid returned
The second parameter of mysql_result()is the row index you would like to retrieve. 0 is first.
的第二个参数mysql_result()是您要检索的行索引。0 是第一位的。
回答by a_horse_with_no_name
The "usual" way is to onlyselect the columns that you need.
“通常”的方法是只选择您需要的列。
Any column that is part of the select and is not needed will slow down the retrieval. Partly because of network traffic and partly because the database engine can apply certain optimizations if only some columns are selected.
任何属于选择且不需要的列都会减慢检索速度。部分是因为网络流量，部分是因为如果只选择了一些列，数据库引擎可以应用某些优化。
And select *is usually frowned upon in production code.
并且select *在生产代码中通常不受欢迎。
回答by Mark Byers
Your query is correct:
您的查询是正确的：
$query = mysql_query("SELECT userid FROM users WHERE username='$username'");
This will work if done correctly. Note that $usernamemust be correctly escaped with mysql_real_escape_string. I'd recommend looking at parameterized queries to reduce the risk of introducing an SQL injection invulnerability.
如果操作正确，这将起作用。请注意，$username必须使用 正确转义mysql_real_escape_string。我建议查看参数化查询以降低引入 SQL 注入无懈可击的风险。
One other thing you can change is to use mysql_resultinstead of your loop:
您可以更改的另一件事是使用mysql_result而不是循环：
$the_user_id = mysql_result($result, 0);
This assumes that know you'll get only one row.
这假设知道你只会得到一行。
回答by Shomz
Yes, that's a great optimization practice!
是的，这是一个很好的优化实践！
Also, if you want to go a step further and make it super-optimized, try adding a LIMIT, like this: $query=mysql_query("SELECT userid FROM users WHERE username='$username' LIMIT 1");
此外，如果您想更进一步并使其超级优化，请尝试添加一个 LIMIT，如下所示： $query=mysql_query("SELECT userid FROM users WHERE username='$username' LIMIT 1");
This, of course, assumes you expect only one userid to be returned and will make the database engine stop once it finds it.
当然，这假设您希望只返回一个用户 ID，并且一旦找到它就会使数据库引擎停止。
But, the most important thing: ALWAYS ESCAPE/SANITIZE YOUR INPUT DATA! (can't stress this well enough)
但是，最重要的是：始终对您的输入数据进行转义/清理！（不能很好地强调这一点）
回答by niq
You can do this with a single line of code
你可以用一行代码做到这一点
echo array_values(mysqli_fetch_array($mysqli->query("SELECT name FROM user WHERE id='$userid'")))[0];
You must connect to the database as shown below before using this
在使用此之前，您必须按如下所示连接到数据库
$mysqli = new mysqli('HOST', 'USERNAME', 'PASSWORD', 'DATABASE');
Credits to @samuel t thomas
