If you use "INSERT INTO ... ON DUPLICATE KEY UPDATE" syntax, mysql_affected_rows() will return you 2 if the UPDATE was made (just as it does with the "REPLACE INTO" syntax) and 1 if the INSERT was.
So if you use one SQL request to insert several rows at a time, and some are inserted, some are just updated, you won't get the real count.
PHP - Manual: mysql_affected_rows
2024-11-14
mysql_affected_rows
(PHP 4, PHP 5)
mysql_affected_rows — 取得前一次 MySQL 操作所影响的记录行数
警告
本扩展自 PHP 5.5.0 起已废弃，并在自 PHP 7.0.0 开始被移除。应使用 MySQLi 或 PDO_MySQL 扩展来替换之。参见 MySQL：选择 API 指南来获取更多信息。用以替代本函数的有：
说明
mysql_affected_rows(resource
$link_identifier = NULL): int
取得最近一次与 link_identifier 关联的
INSERT，UPDATE 或 DELETE 查询所影响的记录行数。
参数
-
link_identifier -
MySQL 连接。如不指定连接标识，则使用由 mysql_connect() 最近打开的连接。如果没有找到该连接，会尝试不带参数调用 mysql_connect() 来创建。如没有找到连接或无法建立连接，则会生成
E_WARNING级别的错误。
返回值
执行成功则返回受影响的行的数目，如果最近一次查询失败的话，函数返回 -1。
如果最近一次操作是没有任何条件（WHERE）的 DELETE 查询，在表中所有的记录都会被删除，但本函数返回值在 4.1.2 版之前都为 0。
当使用 UPDATE 查询，MySQL 不会将原值和新值一样的列更新。这样使得 mysql_affected_rows() 函数返回值不一定就是查询条件所符合的记录数，只有真正被修改的记录数才会被返回。
REPLACE 语句首先删除具有相同主键的记录，然后插入一个新记录。本函数返回的是被删除的记录数加上被插入的记录数。
"INSERT ... ON DUPLICATE KEY UPDATE" 这种情况的查询，当执行了一次 INSERT 返回的值会是 1；如果是对已经存在的记录执行一次 UPDATE 将返回 2。
范例
示例 #1 mysql_affected_rows() 例子
<?php
$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db('mydb');
/* 本例返回被删除记录的准确数目 */
mysql_query('DELETE FROM mytable WHERE id < 10');
printf("Records deleted: %d\n", mysql_affected_rows());
/* 对于非真值的 WHERE 子句，应返回 0 */
mysql_query('DELETE FROM mytable WHERE 0');
printf("Records deleted: %d\n", mysql_affected_rows());
?>
以上例程的输出类似于：
Records deleted: 10 Records deleted: 0
示例 #2 使用事务处理的 mysql_affected_rows() 例子
<?php
$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db('mydb');
/* Update records */
mysql_query("UPDATE mytable SET used=1 WHERE id < 10");
printf ("Updated records: %d\n", mysql_affected_rows());
mysql_query("COMMIT");
?>
以上例程的输出类似于：
Updated Records: 10
注释
注意: 事务处理
如果使用事务处理（transactions），需要在 INSERT，UPDATE 或 DELETE 查询后调用 mysql_affected_rows() 函数，而不是在 COMMIT 命令之后。
注意: SELECT 语句
要获取 SELECT 所返回的行数，可以用 mysql_num_rows()。
注意: Cascaded 外键
mysql_affected_rows() does not count rows affected implicitly through the use of ON DELETE CASCADE and/or ON UPDATE CASCADE in foreign key constraints.
add a note
User Contributed Notes 11 notes
HMax ¶
14 years ago
Ome Ko ¶
10 years ago
There are no rows affected by an update with identical data.
So here is one very ugly solution for these cases:
<?
function mysql_matched_rows() {
$_kaBoom=explode(' ',mysql_info());
return $_kaBoom[2];
}
?>
temp02 at flexis dot com dot br ¶
16 years ago
SCENARIO
1. You're using MySQL 4.1x with foreign keys.
2. You have table t2 linked to table t1 by a CASCADE ON DELETE foreign key.
3. t2 has a UNIQUE key so that duplicate records are unacceptable.
3. You have a REPLACE query on t1 followed by an INSERT query on t2 and expect the second query to fail if there's an attempted insert of a duplicate record.
PROBLEM
You notice that the second query is not failing as you had expected even though the record being inserted is an exact duplicate of a record previously inserted.
CAUSE
When the first query (the REPLACE query) deletes a record from t1 in the first stage of the REPLACE operation, it cascades the delete to the record that would be duplicated in t2. The second query then does not fail because the "duplicate" record is no longer a duplicate, as the original one has just been deleted.
dobrys at abv dot bg ¶
15 years ago
I see that when try to use mysql_affected_rows() with "mysql_pconnect(...)" without link indetifier as param in "mysql_affected_rows()" the result is allways -1.
When use link identifier "mysql_affected_rows($this_sql_connection)" - everything is Fine. This is is on PHP Version 5.2.0
Hope that this was helpfull for somebody
deponti A_T tiscalinet D0T it ¶
18 years ago
It works also for REPLACE query,returning:
0 if the record it's already updated (0 record modified),
1 if the record it's new (1 record inserted),
2 if the record it's updated (2 operations: 1 deletion+ 1 insertion)
vitospericolato at gmail dot com ¶
5 years ago
calling mysql_affected_rows(null)
is not the same that calling mysql_affected_rows()
So, if you have a $link variable that could be null, you must write
if($link)
$n=mysql_affected_rows($link);
else
$n=mysql_affected_rows();
gtisza at gmail dot com ¶
2 years ago
Note that when the CLIENT_FOUND_ROWS connection flag was used, affected_rows returns the number of rows matched by the WHERE condition of an UPDATE query, even if the query doesn't actually change those rows. I.e. for
INSERT INTO t(id, val) VALUES (1, 'x');
UPDATE t SET val = 'x' WHERE id = 1;
the number of affected rows will be 0 normally but 1 with CLIENT_FOUND_ROWS.
ahmd ¶
6 years ago
I was just testing "INSERT INTO ... ON DUPLICATE KEY UPDATE" syntax, on PHP 5.3.29 and mysql_affected_rows() was returning either 2 for updated row, 1 for inserted new row, and also 0, which was not documented, evidently when nothing was inserted. I was inserting a single row.
brian at smitherconsulting dot com ¶
5 years ago
In the case of INSERT where a row/slot had been previously deleted, making an uncollapsed hole in the table, and the record being inserted fills that empty row/slot, that is to say, the inserted data did not create a new row/slot/space, then this may explain why a zero result is returned by this function.
steffen at showsource dot dk ¶
17 years ago
Using OPTIMIZE TABLE will also return true.
So, if you want to check the numbers of deleted records, use mysql_affected_rows() before OPTIMIZE TABLE
sean at adtools dot co dot uk ¶
13 years ago
Here's a little function I've been using for a while now, pass it two parameters (action command (1 or 0 see notes)) and a sql statement.
It returns a simple line which shows the length of time taken to action the query, the status of the query (0= query not actioned, you can set this value for testing, 1=success qry executed successfully, -1= failed, there was a problem with the sql statement) the number of lines affected by that query and the sql statement itself.
I've found this invaluable when trying to tie down large amounts of updates to a table, using this you can easily see where a query was successfully executed and the number of rows are affected, or where there are problems and a statement has failed for example.
<?php
function dosql($action,$sql){
# assuming you have setup a link to your database entitled $link
# action = 1 run this query
# action = 0 don't run, just return sql statement
$start = getmtime();
if($action==1){
$result = mysql_query($sql);
$affectedrows = "[".mysql_affected_rows($link)."]";
}
return "[".number_format((getmtime()-$start),3)."][$action]: $sql\n";
mysql_free_result($result);
}
?>
Example output:
[0.072][1][80]: UPDATE MYTABLE SET FIELD = 1;
[0.106][1][758]: UPDATE ANOTHERTABLE SET FIELD = 2;
[0.006][-1][0]: UPDATER ANOTHERTABLE SET FIELD = 2;
The output shows:
[Timetaken][result]][lines affected]
The result will be either -1, 0 or 1, -1 means there's a problem with the sql statement, 1 means it executed correctly, 0 means it wasn't executed.
官方地址：https://www.php.net/manual/en/function.mysql-affected-rows.php
IT
PHP
编程语言
Linux
开发编程
科技
Elasticsearch
HTML/CSS/XML
网络
JAVA
NoSQL
数据库
面试
C/C++
Golang
Git
算法
操作系统
正则表达式
Redis
互联网
MySql
JavaScript
运维
软件
国际
架构设计
Mac OS
TCP/IP
Excel
Vim
Windows
Socket
Oracle
VR
MongoDB
Python
运营
MemCache
商业
硬件
电子
娱乐
设计
nginx
摄影
游戏
WordPress
HTTP
团建
数码电器
广告
--
php7 安装fileinfo扩展
preg_split — 通过一个正则表达式分隔字符串
php位值,解决 PHP 中 usort 在值相同时改变原始位置的问题
[PHP] inet_pton/inet_ntop IP地址转换函数
array_multisort() 多字段排序
PHPMailer设置utf8 PHPMailer字符集CharSet
ADORecordSet对象
adodb手册
PHP 8.1
[鸟哥]PHP_INT_MIN 和 -9223372036854775808
opcache预加载
Composer的Packagist资源
mysql面试题
[鸟哥]PHP FFI详解 - 一种全新的PHP扩展方式
PHP7添加redis扩展
PHP mkdir()写出来的权限与mode值不符合
Packagist / Composer 中国全量镜像
php中几种注释规范
利用php soap实现web service
PHP获取指定函数定义在哪个文件中及行号