Create a view with column num_rows - MySQL

I found a solution for this:

First create a function:

delimiter //

CREATE FUNCTION `func_inc_var_session`() RETURNS int
    NO SQL
    NOT DETERMINISTIC
     begin
      SET @var := @var + 1;
      return @var;
     end
     //

delimiter ;

Then set @var to the number you want to start with. In this case zero.

SET @var=0;

Then create the view as following:

CREATE OR REPLACE VIEW myview (place, name, hour, price, counter) 
AS SELECT place, name, hour, price, func_inc_var_session() 
FROM yourtable
WHERE input_conditions_here;

The trick here is that you may see NULL on the counter column. If this happens please set @var again to your number and then do the SELECT * again and you'll see the counter column properly populated.

I tried the example of the func_inc_var_session function.

There was a small problem of session variable initialization that I solved using the mysql IFNULL function.

Below the enhanced func_inc_var_session function.

CREATE DEFINER=`root`@`localhost` FUNCTION `func_inc_var_session`() RETURNS int(11)
begin
  SET @var := IFNULL(@var,0) + 1;
  return @var;
end

When using the solution from @dazito you might encounter an issue with the counter continually incrementing from query to query, for example when your application reuses a session, like with JPA / Hibernate. For example:

Query 1:

| country | name | price | row_num |
------------------------------------
| US      | john |  20   |    1    |
| France  | Anne |  10   |    2    |
| Sweden  | Alex |  5    |    3    |

Query 2:

| country | name | price | row_num |
------------------------------------
| US      | john |  20   |    4    |
| France  | Anne |  10   |    5    |
| Sweden  | Alex |  5    |    6    |

etc.

One solution to this is to join the main query with a (one-time) call to the counter function and parameterize the function (the 'reset' parameter below) to let it know it is the first call.

delimiter //
CREATE FUNCTION `func_inc_var_session`(reset BIT) RETURNS int
    NO SQL
    NOT DETERMINISTIC
     begin
      IF reset THEN
        SET @var := 0;
      ELSE
        SET @var := IFNULL(@var,0) + 1;
      END IF;
      return @var;
     end
     //
delimiter ;

Now you can call the function in your view query with the reset parameter set to 1 to set the function's counter variable back to 0, and with 0 to increment the counter. The function will only get called once with 1 as a parameter when joining with it as below:

CREATE OR REPLACE VIEW country_view (country, name, price, row_num) 
AS SELECT country, name, price, func_inc_var_session(0) 
FROM country
JOIN (SELECT func_inc_var_session(1)) r

Now you are guaranteed row number 1, 2, 3 every time.