因此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,为了区分两种情况。


发布了189 篇原创文章 · 获赞 64 · 访问量 47万+

CodeIgniter affected_rows()返回-1


<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="">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官方博客