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?
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.
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!
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
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.
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
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?
Last edited by Custard7A; 11-16-2012 at 12:13 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.)
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
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.
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
Before you post, read our: 
