php mysql 更新查询中的增量值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2259155/
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
Increment value in mysql update query
提问by Karem
I have made this code for giving out +1 point, but it doesn't work properly.
我已经制作了这个代码来给出 +1 分，但它不能正常工作。
mysql_query("
UPDATE member_profile
SET points= ' ".$points." ' + 1
WHERE user_id = '".$userid."'
");
the $points variable is the user′s points right now.. I want it to plus one to it.. so example if he had like 5 points, it should be 5+1 = 6.. but it doesnt, it just changes to 1
在$点变量是用户的点，现在..我希望它加一个吧..所以举例来说，如果他有一个像5分，应该是5 + 1 = 6 ..但它不，它只是改变到 1
What have i done wrong? thank you
我做错了什么？谢谢你
回答by Tomas Markauskas
You could also just do this:
你也可以这样做：
mysql_query("
UPDATE member_profile
SET points = points + 1
WHERE user_id = '".$userid."'
");
回答by Daan
You can do this without having to query the actual amount of points, so it will save you some time and resources during the script execution.
您可以在无需查询实际点数的情况下执行此操作，因此它将在脚本执行期间为您节省一些时间和资源。
mysql_query("UPDATE `member_profile` SET `points`= `points` + 1 WHERE `user_id` = '".intval($userid)."'");
Else, what you were doing wrong is that you passed the old amount of points as a string (points='5'+1), and you can't add a number to a string. ;)
否则，您做错的是将旧的点数作为字符串 ( points='5'+1)传递，并且您无法向字符串添加数字。;)
回答by user272563
Hope I'm not going offtopic on my first post, but I'd like to expand a little on the casting of integer to string as some respondents appear to have it wrong.
希望我的第一篇文章不会跑题，但我想稍微扩展一下整数到字符串的转换，因为一些受访者似乎错了。
Because the expression in this query uses an arithmetic operator (the plus symbol +), MySQL will convert any strings in the expression to numbers.
由于此查询中的表达式使用算术运算符（加号 +），MySQL 会将表达式中的任何字符串转换为数字。
To demonstrate, the following will produce the result 6:
为了演示，以下将产生结果 6：
SELECT ' 05.05 '+'.95';
String concatenation in MySQL requires the CONCAT() function so there is no ambiguity here and MySQL converts the strings to floats and adds them together.
MySQL 中的字符串连接需要 CONCAT() 函数，因此这里没有歧义，MySQL 将字符串转换为浮点数并将它们加在一起。
I actually think the reason the initial query wasn't working is most likely because the $points variable was not in fact set to the user's current points. It was either set to zero, or was unset: MySQL will cast an empty string to zero. For illustration, the following will return 0:
我实际上认为初始查询不起作用的原因很可能是因为 $points 变量实际上并未设置为用户的当前点数。它要么设置为零，要么未设置：MySQL 会将空字符串转换为零。为了说明，以下将返回 0：
SELECT ABS('');
Like I said, I hope I'm not being too off-topic. I agree that Daan and Tomas have the best solutions for this particular problem.
就像我说的，我希望我不会太离题。我同意 Daan 和 Tomas 为这个特定问题提供了最佳解决方案。
回答by bushkonst
Also, to "increment" string, when update, use CONCAT
此外，要“增加”字符串，更新时，请使用 CONCAT
update dbo.test set foo=CONCAT(foo, 'bar') where 1=1
回答by Mark Byers
"UPDATE member_profile SET points = points + 1 WHERE user_id = '".$userid."'"
回答by Rodolfo Souza
Who needs to update string and numbers
SET @a = 0;
UPDATE obj_disposition SET CODE = CONCAT('CD_', @a:=@a+1);
谁需要更新字符串和数字
SET @a = 0;
UPDATE obj_disposition SET CODE = CONCAT('CD_', @a:=@a+1);
回答by Sébastien Gicquel
You should use PDO to prevent SQL injection risk.
您应该使用 PDO 来防止 SQL 注入风险。
You can connect to DB like this :
您可以像这样连接到数据库：
try {
$pdo_options[PDO::ATTR_ERRMODE] = PDO::ERRMODE_EXCEPTION;
$bdd = new PDO('mysql:host=xxxx;dbname=xxxx', 'user', 'password', $pdo_options);
$bdd->query('SET NAMES "utf8"');
} catch (PDOException $e) {
exit('Error');
}
No need to query DB to get the number of points. You can increment directly in the update query (points = points + 1).
无需查询 DB 即可获取点数。您可以在更新查询 ( points = points + 1) 中直接递增。
(note : Also, it's not a good idea to increment the value with PHP because you need to select first the data and the value can changed if other users are updated it.)
（注意：另外，用 PHP 增加值不是一个好主意，因为您需要先选择数据，如果其他用户更新了该值，则该值可能会更改。）
$req = $bdd->prepare('UPDATE member_profile SET
points = points + 1
WHERE user_id = :user_id');
$req->execute(array(
'user_id' => $userid
));
回答by Amirshk
Remove the 'around the point:
删除'周围的point：
mysql_query("UPDATE member_profile SET points=".$points."+1 WHERE user_id = '".$userid."'");
You are "casting" an integer value to string in your original query...
您正在原始查询中将整数值“转换”为字符串...
回答by Petr Peller
Why don't you let PHP do the job?
为什么不让 PHP 来完成这项工作？
"UPDATE member_profile SET points= ' ". ($points+1) ." ' WHERE user_id = '".$userid."'"
