mysql中的affected_rows

在操作mysql语句时,有时需要通过affected_rows来判断语句执行的情况。

例如在事务操作中,就可以通过affected_rows来判断事务是否执行成功,以进一步执行事务的提交或者回滚操作。

对于SELECT操作,mysql_affected_rows()等价于mysql_num_rows(),即查询结果的行数,但是显示使用mysql_num_rows()更加合适。

因此mysql_affected_rows()一般用来在DELETE, INSERT , REPLACE , UPDATE语句执行完成之后判断数据表中变化的行数(如果数据表没有变化,则行数为0)。

  1. DELETE语句执行成功,返回删除的行数,INSERT INTO TABLE VALUES 或者 INSERT INTO TABLES SET 都是返回插入成功的行数,这些是比较明确的。

  2. UPDATE语句执行成功时,则有可能也为0。如果要更新的值与原来的值相同,则affected_rows为0;否则,为更新的行数。

  3. INSERT INTO TABLE VALUES 或者 INSERT INTO TABLES SET 都是返回插入成功的行数,插入成功则返回1,否则返回0 。

  4. INSERT INTO TABLE VALUES … ON DUPLICATE KEY UPDATE … 语句执行成功后,则会有3种情况,当不存在唯一索引冲突时,执行INSERT操作,affected_rows结果为1;当存在主键冲突时,执行UPDATE操作,如果要更新的值与原来的相同,则affected_rows为0,否则为2。

  5. REPLACE INTO TABLE VALUES执行成功 ,如果没有存在唯一索引的冲突,则与INSERT操作没有什么区别affected_rows为1 ;如果存在主键冲突,则会DELETE再INSERT,所以affected_rows的值为2 。


INSERT INTO TABLE VALUES … ON DUPLICATE KEY UPDATE ,当存在唯一索引重复,并成功更新数据之后,受到影响的行数实际上是1,但是affected_rows的值为2。这个数值是mysql手册上规定的,个人猜测应该是因为该语句直接INSERT操作时的affected_rows是1,为了区分两种情况。

参考资料:

http://php.net/manual/zh/function.mysql-affected-rows.php
https://dev.mysql.com/doc/refman/5.7/en/mysql-affected-rows.html

发布了189 篇原创文章 · 获赞 64 · 访问量 47万+
展开阅读全文

CodeIgniter affected_rows()返回-1

04-22

<div class="post-text" itemprop="text"> <p>I have some functions of insert/update in my Codeigniter project, and I check that they succeed with the function <code>affected_rows()</code>. I have tested it locally and it works perfect, but when I uploaded to my server it doesn't work anymore, it always returns -1 though in my database it DOES insert/update. </p> <p>What could be my problem?</p> <p>Thanks in advance (:</p> <p>An example of my code:</p> <pre><code>$this->db->insert('permisos', $permiso); return $this->db->affected_rows() > 0; </code></pre> <p>UPDATE 1:</p> <p>I tried with <code>echo $this->db->affected_rows()</code> and it returned -1, so my statement returns <code>FALSE</code>.</p> <p>I'm working with MySQL 5.6.16 and PHP 5.5.11 in development; MySQL 5.0.77 and PHP 5.2.9 in production. My Codeigniter version is 2.1.4.</p> <p>UPDATE 2:</p> <p>I found out what's my problem, I was using mysqli driver and apparently my server does not support it, so I changed it back to mysql and now this is working, but... I was set it to mysqli because I had troubles with some stored procedures (<a href="https://stackoverflow.com/questions/1200193/cant-return-a-result-set-in-the-given-context">this</a>)... so... I need another workaround xD.</p> <p>Somewhere I saw something like this </p> <pre><code>$insert = $this->db->insert('permisos', $permiso); if($insert){ return TRUE; }else{ return FALSE; } </code></pre> <p>And it seems to work (with mysqli driver) but I'm not sure what it does exactly or if it's a reliable way... Is it?</p> </div> 问答

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 编程工作室 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览