使用CodeIgniter在postgres中受影响的行

问题描述：

I have a following query:

UPDATE "user" SET "first_name" = 'abc', "last_name" = '', "mobile" = '988988888', "comment" = 'Hello' WHERE "id" = '15'

and above data is already in db. means i submit form without changing any data.

I hit above query in terminal and it says : UPDATE 1

CI CODE:

$query = $this->db->get_where('user',array('id'=>$id),1);
if ($query->num_rows() > 0)
{
    $this->db->update('user', $data, array('id'=>$id));         
    echo $afftected_rows = $this->db->affected_rows();exit;
}

DB SCHEMA(export using psql cmmand)

CREATE TABLE "user" (
  id integer NOT NULL,
  first_name character varying(50),
  last_name character varying(50),
  mobile character varying(50),  
  comment character varying(500)
);

so what is the problem? why it is return 1, even if i don't change any data. is this the normal behaviour for postgres?

我有以下查询： p>

UPDATE“ user“SET”first_name“='abc'，”last_name“=''，”mobile“='988988888'，”comment“='Hello'WHERE”id“='15' p> blockquote >

以上数据已在db中。表示我在不更改任何数据的情况下提交表单。 p>

我在终端点击上面的查询并说：UPDATE 1 code> p>

CI CODE： code> p>

  $ query = $ this-＆gt; db-＆gt; get_where（'user'，array（'id'=＆gt;  $ id），1）; 
if（$ query-＆gt; num_rows（）＆gt; 0）
 {
 $ this-＆gt; db-＆gt; update（'user'，$ data，array（'id'  =＆GT; $ ID））;  
 echo $ afftected_rows = $ this-＆gt; db-＆gt; affected_rows（）;退出; 
} 
  code>  pre> 
 
   DB SCHEMA（使用psql导出）  cmmand） code>  p> 
 
 
  CREATE TABLE“user”（
 id整数NOT NULL，
 first_name字符变化（50），
 last_name字符变化（50）  ，
移动字符变化（50），
注释字符变化（500）
）; 
  code>  pre> 
 
 所以问题是什么？ 为什么它返回1，即使我不改变任何数据。 这是postgres的正常行为吗？ p> 
  div>

答

Yes, it's the normal behavior.

MySQL has the notion of "matched rows" and "affected rows" which might differ: when a "matched row" would be updated with the values that it already holds, it's not affected.

In PostgreSQL, only the count of "affected rows" comes back to the user, and all "matched rows" are affected.

To be complete, strictly speaking, this behavior is amendable in PostgreSQL. Skipping these updates is always possible with a fairly generic trigger.

Example:

First the baseline,default behavior, to compare against:

test=> create table update_test(val text);
CREATE TABLE
test=> insert into update_test values('abc');
INSERT 0 1
test=> update update_test set val='abc';
UPDATE 1

This UPDATE 1 indicates that 1 row was affected, even though the value is the same.

Now let's tell it to skip the rows for which values don't change.

-- generic trigger to void the update of one row
create function void_update() returns trigger language plpgsql as 
 'begin return null; end';

-- trigger affecting unchanged rows in a BEFORE UDPATE event
create trigger update_trigger 
  before update on update_test for each row
  when (old is not distinct from new)
  execute procedure void_update();

Now we get the MySQL-like behavior:

test=> update update_test set val='abc';
UPDATE 0

0 row is affected, because we're updating the only row of the table with a value it already has.

Test again with more data, some rows to skip, other rows to change:

test=> insert into update_test values('def'),('ghi');
INSERT 0 2
test=> select * from update_test ;
 val 
-----
 abc
 def
 ghi
(3 rows)

test=> update update_test set val='ghi';
UPDATE 2

Only 2 rows were affected since the last row already contained 'ghi'

Check that the update actually works:

test=> select * from update_test ;
 val 
-----
 ghi
 ghi
 ghi
(3 rows)

使用CodeIgniter在postgres中受影响的行

相关推荐