blocks|key|1114106|text|"UPDATE+member_profile+SET+points+=+points+%2B+1+WHERE+user_id+=+'".$userid."'"|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1114107|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>"UPDATE member_profile SET points = points + 1 WHERE user_id = '".$userid."'"
</code></pre>

blocks|key|1329270|text|卸下point周围的'|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1329271|mysql_query("UPDATE+member_profile+SET+points=".$points."%2B1+WHERE+user_id+=+'".$userid."'");|code-block|syntax|javascript|1329272|在原始查询中，您正在将一个整数值“转换”为字符串...|1329273|entityMap^0|2|5|A|1|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@]|D|@]|E|$]]|$1|M|3|-4|5|6|7|V|8|@]|D|@]|E|$]]]|N|$]]

Remove the <code>'</code> around the <code>point</code>:

<pre><code>mysql_query("UPDATE member_profile SET points=".$points."+1 WHERE user_id = '".$userid."'");
</code></pre>

You are "casting" an integer value to string in your original query...

blocks|key|1329329|text|为什么不让PHP来做这件事呢？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1329330|"UPDATE+member_profile+SET+points=+'+".+($points%2B1)+."+'++WHERE+user_id+=+'".$userid."'"|code-block|syntax|javascript|1329331|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Why don't you let PHP do the job?

<pre><code>"UPDATE member_profile SET points= ' ". ($points+1) ." ' WHERE user_id = '".$userid."'"
</code></pre>

blocks|key|1329397|text|您可以在不必查询实际点数的情况下执行此操作，因此它将在脚本执行期间为您节省一些时间和资源。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1329398|mysql_query("UPDATE+`member_profile`+SET+`points`=+`points`+%2B+1+WHERE+`user_id`+=+'".intval($userid)."'");|code-block|syntax|javascript|1329399|否则，您所做的错误是将旧的点数作为字符串(points='5'%2B1)传递，并且不能将数字添加到字符串中。;)|offset|length|style|CODE|1329400|entityMap^0|0|0|L|C|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

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. 

<pre><code>mysql_query("UPDATE `member_profile` SET `points`= `points` + 1 WHERE `user_id` = '".intval($userid)."'");
</code></pre>

Else, what you were doing wrong is that you passed the old amount of points as a string (<code>points='5'+1</code>), and you can't add a number to a string. ;)

blocks|key|1329410|text|你也可以这样做：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1329411|mysql_query("
++++UPDATE+member_profile+
++++SET+points+=+points+%2B+1
++++WHERE+user_id+=+'".$userid."'
");|code-block|syntax|javascript|1329412|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You could also just do this:

<pre><code>mysql_query("
 UPDATE member_profile 
 SET points = points + 1
 WHERE user_id = '".$userid."'
");
</code></pre>

blocks|key|1114314|text|希望我不会在我的第一篇文章中离题，但我想在整数到字符串的转换上做一点扩展，因为一些受访者似乎错了。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1114315|因为此查询中的表达式使用算术运算符(加号%2B)，所以MySQL会将表达式中的任何字符串转换为数字。|1114316|为了演示，以下内容将产生结果6：|1114317|SELECT+'+05.05+'%2B'.95';|code-block|syntax|javascript|1114318|MySQL中的字符串连接需要CONCAT()函数，因此这里没有歧义，MySQL将字符串转换为浮点数并将它们相加在一起。|1114319|实际上，我认为初始查询不起作用的原因很可能是因为$points变量实际上没有设置为用户的当前点。它要么被设置为零，要么被取消设置:+MySQL会将一个空字符串转换为零。为了便于说明，下面将返回0：|1114320|SELECT+ABS('');|1114321|就像我说的，我希望我没有太离题。我同意Daan和Tomas对这个问题有最好的解决方案。|1114322|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|W|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|X|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|Y|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|Z|8|@]|9|@]|A|$]]|$1|O|3|P|5|H|7|10|8|@]|9|@]|A|$I|J]]|$1|Q|3|R|5|6|7|11|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

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.

To demonstrate, the following will produce the result 6:

<pre><code>SELECT ' 05.05 '+'.95';
</code></pre>

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. 

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:

<pre><code>SELECT ABS('');
</code></pre>

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.

blocks|key|1329522|text|此外，要在更新时“递增”字符串，请使用CONCAT|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1329523|update+dbo.test+set+foo=CONCAT(foo,+'bar')+where+1=1|code-block|syntax|javascript|1329524|entityMap^0|J|6|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

Also, to "increment" string, when update, use <code>CONCAT</code>

<pre class="lang-sql prettyprint-override"><code>update dbo.test set foo=CONCAT(foo, 'bar') where 1=1
</code></pre>

blocks|key|1329553|text|谁需要更新string和numbers+SET+@a+=+0;+UPDATE+obj_disposition+SET+CODE+=+CONCAT('CD_',+@a:=@a%2B1);|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1329554|entityMap^0|K|1Y|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

Who needs to update string and numbers
<code>SET @a = 0;
UPDATE obj_disposition SET CODE = CONCAT('CD_', @a:=@a+1);</code>

blocks|key|1114463|text|您应该使用PDO来防止SQL注入风险。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1114464|您可以像这样连接到DB：|1114465|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');
}|code-block|syntax|javascript|1114466|不需要查询DB来获得点数。可以在update查询(points+=+points+%2B+1)中直接递增。|offset|length|style|CODE|1114467|(注意:此外，使用PHP递增该值也不是一个好主意，因为您需要首先选择数据，如果其他用户更新了该值，则该值可能会更改。)|1114468|$req+=+$bdd->prepare('UPDATE+member_profile+SET+
++++++++++++points+=+points+%2B+1
++++++++++++WHERE+user_id+=+:user_id');

$req->execute(array(
++++'user_id'+=>+$userid
));|1114469|entityMap^0|0|0|0|P|J|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|W|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|X|8|@$K|Y|L|Z|M|N]]|9|@]|A|$]]|$1|O|3|P|5|6|7|10|8|@]|9|@]|A|$]]|$1|Q|3|R|5|F|7|11|8|@]|9|@]|A|$G|H]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

You should use PDO to prevent SQL injection risk.

You can connect to DB like this :

<pre><code>try {
 $pdo_options[PDO::ATTR_ERRMODE] = PDO::ERRMODE_EXCEPTION;
 $bdd = new PDO('mysql:host=xxxx;dbname=xxxx', 'user', 'password', $pdo_options);
 $bdd-&gt;query('SET NAMES "utf8"');
} catch (PDOException $e) {
 exit('Error');
}
</code></pre>

No need to query DB to get the number of points. You can increment directly in the update query (<code>points = points + 1</code>).

(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.)

<pre><code>$req = $bdd-&gt;prepare('UPDATE member_profile SET 
 points = points + 1
 WHERE user_id = :user_id');

$req-&gt;execute(array(
 'user_id' =&gt; $userid
));
</code></pre>

blocks|key|1114507|text|可接受的答案是好的，但不适用于空值，请尝试以下方法|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1114508|mysql_query("
++++UPDATE+member_profile+
++++SET+points+=+IFNULL(points,+0)+%2B+1
++++WHERE+user_id+=+'".$userid."'
");|code-block|syntax|javascript|1114509|More+info+on+IFNULL|offset|length|style|CODE|1114510|entityMap|0|LINK|mutability|MUTABLE|url|https://dev.mysql.com/doc/refman/5.6/en/flow-control-functions.html#function_ifnull^0|0|0|D|6|0|J|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@$I|X|J|Y|K|L]]|9|@$I|Z|J|10|1|11]]|A|$]]|$1|M|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]]]

The accepted answer is good but not working with null values try this
<pre><code>mysql_query(&quot;
 UPDATE member_profile 
 SET points = IFNULL(points, 0) + 1
 WHERE user_id = '&quot;.$userid.&quot;'
&quot;);
</code></pre>
<a href="https://dev.mysql.com/doc/refman/5.6/en/flow-control-functions.html#function_ifnull" rel="noreferrer">More info on <code>IFNULL</code></a>

I have made this code for giving out +1 point, but it doesn't work properly.
<pre><code>mysql_query(&quot;
 UPDATE member_profile 
 SET points= ' &quot;.$points.&quot; ' + 1 
 WHERE user_id = '&quot;.$userid.&quot;'
&quot;);
</code></pre>
The <code>$points</code> variable is the user's points right now. I want it to add one to it. So example if he had like 5 points, it should be 5+1 = 6, but it doesn't, it just changes to 1.
What have I done wrong?

Increment value in MySQL update query

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我写了这段代码来给出+1分，但它不能正常工作。mysql_query("    UPDATE member_profile     SET points= ' ".$points." ' + 1     WHERE user_id = '".$userid."'");$points变量现在是用户的点数。我想让它再加一个...

问MySQL更新查询中的增量值
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL更新查询中的增量值EN