[Resolvido] Aumentar x valor em x tempo DB

[RenatoRibeiro 0]

Bom galera, eu tenho um valor x em um valor da db, e queria que de um determinado tempo, aumentasse em 10 com um limite.

Por exemplo: O limite é 100, então se tiver 100 não irá subir o valor. Mas se tiver 20, irá somar 'valor atual+10' a cada 1 minuto até chegar no 100 que não vai subir mais.

Me enrolei todo pra explicar, rs, mas creio que entenderam.

 

Tem como fazer isso? O.o

[Andrey Knupp Vital 136]

Você pode utilizar events .. dê uma procurada no manual do MySQL, ou então:

andrey@andrey:~$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 36
Server version: 5.1.49-1ubuntu8.1 (Ubuntu)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> help 'CREATE EVENT';
Name: 'CREATE EVENT'
Description:
Syntax:
CREATE
   [DEFINER = { user | CURRENT_USER }]
   EVENT
   [iF NOT EXISTS]
   event_name
   ON SCHEDULE schedule
   [ON COMPLETION [NOT] PRESERVE]
   [ENABLE | DISABLE | DISABLE ON SLAVE]
   [COMMENT 'comment']
   DO event_body;

schedule:
   AT timestamp [+ INTERVAL interval] ...
 | EVERY interval
   [sTARTS timestamp [+ INTERVAL interval] ...]
   [ENDS timestamp [+ INTERVAL interval] ...]

interval:
   quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
             WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
             DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

This statement creates and schedules a new event. The event will not
run unless the Event Scheduler is enabled. For information about
checking Event Scheduler status and enabling it if necessary, see
http://dev.mysql.com/doc/refman/5.1/en/events-configuration.html.

CREATE EVENT requires the EVENT privilege for the schema in which the
event is to be created. It might also require the SUPER privilege,
depending on the DEFINER value, as described later in this section.

The minimum requirements for a valid CREATE EVENT statement are as
follows:

o The keywords CREATE EVENT plus an event name, which uniquely
 identifies the event within a database schema. (Prior to MySQL
 5.1.12, the event name needed to be unique only among events created
 by the same user within a schema.)

o An ON SCHEDULE clause, which determines when and how often the event
 executes.

o A DO clause, which contains the SQL statement to be executed by an
 event.

This is an example of a minimal CREATE EVENT statement:

CREATE EVENT myevent
   ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
   DO
     UPDATE myschema.mytable SET mycol = mycol + 1;

The previous statement creates an event named myevent. This event
executes once---one hour following its creation---by running an SQL
statement that increments the value of the myschema.mytable table's
mycol column by 1.

The event_name must be a valid MySQL identifier with a maximum length
of 64 characters. Event names are not case sensitive, so you cannot
have two events named myevent and MyEvent in the same schema. In
general, the rules governing event names are the same as those for
names of stored routines. See
http://dev.mysql.com/doc/refman/5.1/en/identifiers.html.

An event is associated with a schema. If no schema is indicated as part
of event_name, the default (current) schema is assumed. To create an
event in a specific schema, qualify the event name with a schema using
schema_name.event_name syntax.

URL: http://dev.mysql.com/doc/refman/5.1/en/create-event.html


mysql> 

[RenatoRibeiro 0]

CREATE EVENT life

ON SCHEDULE EVERY 1 MINUTE

DO UPDATE players SET 'life' = 'life' + 10;

 

Certo, mas como não entendo muito de mysql, como ponho um limite nesse update?

Tenho a coluna 'max' em cada player, e queria que o 'life' não ultrapasse o max.

 

Como seria?

UPDATE players SET 'life' = 'life' + 10 IF 'life' < 'max'; ?

kkkkk vai saber né.

 

Valeu cara.

[Matheus Tavares 167]

Como seria?

UPDATE players SET 'life' = 'life' + 10 IF 'life' < 'max'; ?

 

Quase lá...

O if existe no Mysql, mas uma maneira mais direcionada ao problema de resolver isso, seria assim:

 

UPDATE `players` SET `life` = +10 WHERE `life` < `max`;

ONDE `life` FOR MENOR QUE `max`

 

Porém vou ilustrar um problema-exemplo disso: Se o máximo é 100, e o usuário atualmente está com 95, ele vai ficar com 105. Podemos consertar dessa forma:

UPDATE `players` SET `life` = IF( `life`+10 > `max` , `life` + (`max` - `life`) , `life` + 10 ) WHERE `life` < `max`;

 

Dessa forma, o máximo sempre será max. Se o usuário estiver com 95, ele ficará com 100, ao mesmo tempo que se estiver com 80, ficará com 90.

Não testei o script, é possível de conter algum errinho, mas a ideia é essa.

 

Um abraço!

[RenatoRibeiro 0]

Muuito obrigado cara! Sério valeu aos 2 :P

 

Abraços.