| Kider |
accessible by lift 1# Posted in 2010-8-31 16:06:04 | Just look at the author | Reverse View | Reading mode
Source: mysqlpub.com
there are two functions in MySQL to calculate how many rows were affected by the previous statement, unlike Sqlserver/oracle, and do not cause functional problems because of this difference:
1, judge the number of rows obtained by select with the Found_rows () function.
2, determine the number of rows affected by update or delete with the Row_count () function to judge, it is important to note that if the value before and after the update, Row_count is 0, Unlike the @ @rowcount in SQL Server or rowcount in Oracle, the number of rows affected will be greater than 0 if the update is to a row, regardless of whether the value of the field before and after the update has changed.
Example Description:
test on MySQL (database version: 5.1.30):
1. Create a database table:
CREATE TABLE T (
ID int,
name varchar,
address varchar (+),
primary KEY (Id,name)
) engine =innodb;
2. Insert the test data:
INSERT INTO T
(id,name,address)
Values
(1, ' Yubowei ', ' Weifang '),
(2, ' Sam ', ' Qingdao ');
3. Update the test
Update T
Set address = ' Weifang '
WHERE id = 1
and name = ' Yubowei ';
at this point, view the number of rows affected:
select Row_count (); ==〉 execution result is 0;
4. Test again
Update T
Set address = ' Beijing '
WHERE id = 1
and name = ' Yubowei ';
at this point, view the number of rows affected:
select Row_count (); ==〉 execution result is 1;
from the above test it can be concluded that only when the record is really modified in MySQL, Row_count will record the number of rows affected, otherwise if the record exists but no actual modification
the update is not logged to Row_count.
This is not the same as SQL rowcount in Oracle
tests on Oracle (Database version: 10G):
1. Create a database table:
CREATE TABLE T (
ID int,
name Varchar2 (),
address varchar2 (+),
primary KEY (Id,name)
);
2. Insert the test data:
INSERT INTO T
(id,name,address)
Values
(1, ' Yubowei ', ' Weifang '),
(2, ' Sam ', ' Qingdao ');
3. Update the test
Update T
Set address = ' Weifang '
WHERE id = 1
and name = ' Yubowei ';
at this point, view the number of rows affected:
V_rowcount: = sql%rowcount; ==〉 execution result is 1;
4. Test again
Update T
Set address = ' Beijing '
WHERE id = 1
and name = ' Yubowei ';
at this point, view the number of rows affected:
V_rowcount: = sql%rowcount; ==〉 execution result is 1;
from the above test it can be concluded that in Oracle, as long as the updated records exist, the number of rows affected will be accumulated regardless of the actual data being modified.
Note: So far there are no parameters that can be set for Row_count (). If necessary, it can only be achieved by other means. |
|
