Mysql time operation functions and stored procedures, mysql function stored procedures

Mysql time operation functions and stored procedures, mysql function stored procedures

Because the business needs to collect a batch of data, mysql time operation functions and stored procedures are used, and the problem has been basically solved. Record the process as follows:

1. mysql statements do not support direct use of loops. loops can only be used in stored procedures;

2. When writing as a file, pay attention to some hidden characters, resulting in syntax errors. In this example, the annotation contains some invisible characters and cannot be found.

3. Use as many statements as possible during the stored procedure. In this example, the drop clause is not written at first, which leads to errors.

4. The time function is very powerful and can avoid some work. Http://www.cnblogs.com/ggjucheng/p/3352280.html

5. Optimization of SQL statements is very important. This example only solves the problem, but the storage process has been running for a long time because the data volume is too large. We hope someone can help optimize the while statement.

The following code is pasted:

-- * Convert the table test. the data in the transport4040901 table is separated every five minutes, count the number of traffic flows at each intersection r -- * @ start_time the start time is the five-minute interval of the whole point time, for example, 00:20:00 -- * @ end_time the end time is the five-minute interval of the whole point and is later than start_time, for example, 01:00:00 -- * The Statistical range includes the start time, but does not include the end time delimiter $ drop procedure transport_status; create procedure transport_status (start_time datetime, end_time datetime) begin declare mid_start_time datetime; declare mid_end_time datetime; set mid_start_time; set mid_end_time = date_add (start_time, interval 5 minute); lab: while mid_start_time <end_time do insert into test. transport_status (stamp, stamp_time, address, car_count) (select FLOOR (UNIX_TIMESTAMP (time)/300) as stmp, date_format (mid_end_time, '% Y-% m-% d % H: % I: % s') as tm, address, count (address) as cnt from test. transport20140901 where time> date_add (mid_start_time, interval-1 second) and time <mid_end_time group by address); set mid_start_time = date_add (mid_start_time, interval 5 minute ); set mid_end_time = date_add (mid_end_time, interval 5 minute); end while lab; end $ delimiter; call transport_status ("00:00:00", "00:00:00 ");