Does MySQL Have an Equivalent to @@Rowcount Like in Mssql

Does Mysql have an equivalent to @@ROWCOUNT like in mssql?

For SELECTs you can use the FOUND_ROWS construct (documented here):

SELECT SQL_CALC_FOUND_ROWS something FROM your_table WHERE whatever;
SELECT FOUND_ROWS( ) ;

which will return the number of rows in the last SELECT query (or if the first query has a LIMIT clause, it returns the number of rows there would've been without the LIMIT).

For UPDATE/DELETE/INSERT, it's the ROW_COUNT construct

INSERT INTO your_table VALUES (1,2,3);
SELECT ROW_COUNT();

which will return the number of affected rows.

Is @@ROWCOUNT after UPDATE reliably a measure of *matching* rows?

The documentation for @@ROWCOUNT is telling you the truth because 3 rows would be reliably affected as opposed to MySQL's ROW_COUNT().

not 2 (the number of rows modified by the UPDATE — one of the three
rows already had the value 1 for b).

For UPDATE it's not important if the new and previous values are identical. It simply does what its told to: finds data source, filters rows according to provided condition, and applies 'set' changes to filtered rows.

That's the way SQL Server works without any reservations. MySQL may work different. A row counting procedure is not a part of the SQL standard. So, you have to look before you leap for those kinds of artefacts every time you switch from one RDBMS to another.

Some triggers to see actual update behaviour:

CREATE TRIGGER [dbo].[trgFooForUpd]
ON [dbo].[Foo]
FOR UPDATE 
AS begin declare @id int;
      select @id = [a] from INSERTED;
      select * from INSERTED; end;
GO
CREATE TRIGGER [dbo].[trgFooAfterUpd]
ON [dbo].[Foo]
AFTER UPDATE 
AS print 'update done for ' + cast(coalesce( @@ROWCOUNT, -1) as varchar )+'rows'

using count() in mySQL and AS

Try using having and set on SELECT the same fields that in GROUP BY:

  SELECT username, count(*) as total_count
    FROM action_log 
    WHERE response LIKE '%media%' 
    AND action_time > '2014-01-05 21:31:33' 
    AND action_time < '2014-01-07 12:30:00' 
    GROUP BY username
    HAVING count(*) > 1

SQL Trigger @@ROWCOUNT

There is no @@ROWCOUNT in MySQL.
You can read this post to find ount how to replace it.

But you dont need that. Your trigger is for each row so it will fire for every updated row. (But that doesnt mean that your rows have changed. Just that they were updated by some statement.)

ROW_NUMBER() in MySQL

I want the row with the single highest col3 for each (col1, col2) pair.

That's a groupwise maximum, one of the most commonly-asked SQL questions (since it seems like it should be easy, but actually it kind of isn't).

I often plump for a null-self-join:

SELECT t0.col3
FROM table AS t0
LEFT JOIN table AS t1 ON t0.col1=t1.col1 AND t0.col2=t1.col2 AND t1.col3>t0.col3
WHERE t1.col1 IS NULL;

“Get the rows in the table for which no other row with matching col1,col2 has a higher col3.” (You will notice this and most other groupwise-maximum solutions will return multiple rows if more than one row has the same col1,col2,col3. If that's a problem you may need some post-processing.)

---

Related Topics