CodingForums.com - Native Integer Incrementing (Existing Value)

CodingForums.com (http://www.codingforums.com/index.php)

- MySQL (http://www.codingforums.com/forumdisplay.php?f=7)

- - Native Integer Incrementing (Existing Value) (http://www.codingforums.com/showthread.php?t=282249)

Custard7A

11-15-2012 02:14 PM

Native Integer Incrementing (Existing Value)

Hi guys, I have a rather simple issue. I have a SMALLINT column, and I want this value to rise by one each-time I use that row (Basically this is recording the number of views on that row).
At the moment I am doing this (Stripped-down for the example):

PHP Code:


		
			


 // After selecting the row I want, with an associative array.



 $new_value = ++$result['views']; // Increment with php, and call an UPDATE..



 $mysqli->query("UPDATE table_name SET views = $new_value WHERE row_id = $variable");

This seems like something might exists that can do this within MySQL. So, my questions would be..

1. Is there a way to increment an existing column without specifically calling UPDATE on it?

.. else..

2. Is there a way to increment it with SQL, so I can just do this in the query?

Fumigator

11-15-2012 04:35 PM

There's nothing like that built-in to my knowledge. Your method is fine.

Fou-Lu

11-15-2012 08:34 PM

Quote:

Originally Posted by Fumigator (Post 1292281)

There's nothing like that built-in to my knowledge. Your method is fine.

Not to mine either. Unlike sequences in oracle (which I'm pretty sure can be applied as I see fit, although I'd still see issues getting it to do it during a select), mysql doesn't have an auto-increment except for insertion id's on surrogate keys.
Pedant or Guelphdad may be able to verify; a custom procedure should let you get away with doing that. But then you need to execute via call, so I'm not sure if that would be more ideal or not.

Old Pedant

11-15-2012 09:11 PM

OF COURSE you should use a STORED PROCEDURE!

Trivial.

Example:

Code:

mysql> select * from foo;

+-----------+-------+

| viewcount | name  |

+-----------+-------+

|         0 | adam  |

|         0 | bob   |

|         0 | candy |

+-----------+-------+



mysql> delimiter //



mysql> create procedure getAndBumpCount( nm varchar(20) )

    -> begin

    ->     update foo set viewcount = viewcount + 1 where name = nm;

    ->     select * from foo where name = nm;

    -> end;

    -> //



mysql> delimiter ;



mysql> call getAndBumpCount ('candy');

+-----------+-------+

| viewcount | name  |

+-----------+-------+

|         1 | candy |

+-----------+-------+



mysql> call getAndBumpCount ('candy');

+-----------+-------+

| viewcount | name  |

+-----------+-------+

|         2 | candy |

+-----------+-------+



mysql> call getAndBumpCount('adam');

+-----------+------+

| viewcount | name |

+-----------+------+

|         1 | adam |

+-----------+------+



mysql> select * from foo;

+-----------+-------+

| viewcount | name  |

+-----------+-------+

|         1 | adam  |

|         0 | bob   |

|         2 | candy |

+-----------+-------+

How hard is that?

Why do you thing executing via CALL would be a bad thing, FouLu? Most DB experts would tell you that you should do *ALL* work via Stored Procedures and not give the casual DB client permissions to use anything *except* Stored Procs!

Old Pedant

11-15-2012 09:19 PM

Incidentally, the Stored Procedure will be roughly twice as efficient as making the two separate calls ( SELECT and then UPDATE ) as proposed in the first post.

That's because your PHP code only needs to make one interprocess invocation of MySQL. People don't realize how very expensive interprocess calls are.

The actual time spent in the DB is going to be roughly the same. The first query (whether it be the SELECT as first proposed or the UPDATE in my procedure) is going to ensure that the relevant record is fetched from disk to memory and so will be cached in memory for the second operation in both cases. So the disk-to-memory overhead will be the same and you are thus saving one interprocess call. Nothing to be sneezed at.

Custard7A

11-16-2012 12:05 AM

Heey, that looks nice. I want to use stored procedures for everything it looks simple!

Quote:

update foo set viewcount = viewcount + 1 where..

That's valid SQL, preforming addition on an integer field?

Thanks for the tips too. I usually assume everything to do with the database is resource expensive, so things like doing one query instead of two if I can is usually in the back of my mind.

That last comment about caching, do you mean that — in your example, for example — the select query will use the incremented viewcount from memory of the first line?

Old Pedant

11-16-2012 01:16 AM

The SELECT would use the incremented value even if caching wasn't involved. SQL guarantees that operations will be done in the order requested.

The remark about caching has to do with the fact that, like most modern DB engines, MySQL makes use of a *HUGE* in-memory cache. Many many megabytes. (We actually had one system where we "tuned" MySQL to use 75% of available memory. Yes, you can do that. By default, though, it uses a lot less than that.)

So when you make a query of any kind on a given record, the disk *BLOCK* that contains that record is loaded into memory. And the DB engine kind of assumes that the most recent record fetches are going to be needed again, so it KEEPS that block in memory as long as it can (that is, it caches it).

In a typical MySQL installation, you can pretty much count on any record that was fetched till being in the cache many seconds or even a minute or two after it was fetched. And clearly if you do an UPDATE immediately followed by a SELECT as I do in the Procedure--or even if you do two separate queries, SELECT followed by UPDATE as in your first post--the record *WILL* still be in memory. So the second operation doesn't involve use of the disk at all. MySQL just remembers where it put that block in its memory cache and ZAM! It's all done in memory.

(Of course, after an UPDATE, eventually that disk block needs to be written back out to the disk, but MySQL does a lazy update, trying to schedule the writes when no other user-requested operations are taking place at a higher priority.)

Old Pedant

11-16-2012 01:19 AM

And of course you can increment a field as shown there.

You can do a lot more than that:

Code:

UPDATE sometable SET field1 = field1 + 1 + IF(field2 > 3, 7, -12 )

WHERE id = 9102 AND field1 < 1000000

and so on and so on.

Heck, you can even "tack on" to text fields:

Code:

UPDATE sometable SET ticks = CONCAT( ticks,'*') WHERE id = 1817

That would make the ticks field grow by one "*" every time the UPDATE is executed.

Custard7A

11-16-2012 12:45 PM

Great, this should all make my scripts nice and neater! But, I see maybe a problem..

Can I use PHP variables in stored procedures? I couldn't get them to work in views..

Old Pedant

11-16-2012 08:27 PM

You can't use PHP variables *DIRECTLY*, but you can pass them in as parameters.

For example, using the getAndBumpCount SP that I created, you would invoke it from PHP via:

Code:

$sql = "call getAndBumpCount ('$name')";

$result = mysql_query( $sql );

You can pass in as many arguments as you wish to a Stored Procedure and then use them by name in the body of the procedure.

In my SP, I used

Code:

create procedure getAndBumpCount( nm varchar(20) )

so the argument $name would end up in the MySQL variable nm and you can then use nm wherever you want in the SP.

Some people use a naming convention for parameters. Such as P_NM, P_ID, P_Address, etc. Where the "P_" makes it clear that this variable is a "P"arameter to the stored procedure.

Custard7A

11-17-2012 03:13 AM

Brilliant, OK, now to put it all to good use. Thanks for the expert advice Old Pedant, I feel a little more confident in my database operations now.

All times are GMT +1. The time now is 03:47 AM.