0%

数据库中定时处理数据+常用操作

发表于 更新于 分类于 阅读次数：
本文字数： 4.6k 阅读时长 ≈ 4 分钟

2017年6月28日 下午10:08

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
创建存储过程
mysql> create procedure insert_user()
    -> begin
    -> insert into user(username) values('czh');
    -> end
    -> //


delimiter  //
create procedure insert_user()
begin
insert into user(username) values('czh');
select now();
end
//
delimiter ;


创建事件
mysql> create event e_insert
    -> on schedule every 10 second starts '2016-5-14 20:00:00'
    -> on completion preserve
    -> do call insert_user();


create event e_insert
on schedule every 10 second starts '2016-5-14 20:00:00'
on completion preserve
do call insert_user();

建数据表
mysql> create table table1(
    -> id int primary key auto_increment,
    -> time_start date not null,
    -> time_long int not null
    -> );
对表中的数据进行date类型的操作（增加）
select date_add((select time_start from table1 where id=1),interval(select time_long from table1 where id=1) day);

比较两个date类型的数据
mysql> set @d1=now();
Query OK, 0 rows affected (0.00 sec)

mysql> select @d1;
+---------------------+
| @d1                 |
+---------------------+
| 2016-05-14 21:15:41 |
+---------------------+
1 row in set (0.00 sec)

mysql> set @d2=date_add(@d1, interval 30 day);
Query OK, 0 rows affected (0.00 sec)

mysql> select @d2;
+---------------------+
| @d2                 |
+---------------------+
| 2016-06-13 21:15:41 |
+---------------------+
1 row in set (0.00 sec)

mysql> @d1>@d2;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@d1>@d2' at line 1

mysql> select @d1>@d2;
+---------+
| @d1>@d2 |
+---------+
|       0 |
+---------+
1 row in set (0.18 sec)

mysql> select @d1<@d2;
+---------+
| @d1<@d2 |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)


mysql> delimiter //
mysql> create procedure proc1()
    -> begin
    -> declare no_more_record int default 0;
    -> declare time_start1 date;
    -> declare time_long1 int;
    -> declare id1 int;
    -> declare if_date date;
    -> declare cur_record cursor for select time_start,time_long,id from table1;
    -> DECLARE CONTINUE HANDLER FOR NOT FOUND  SET  no_more_record = 1;      
    -> open cur_record;
    -> fetch cur_record into time_start1,time_long1,id1;
    ->
    -> while no_more_record !=1 do
    -> 	set if_date=date_add(time_start1,interval time_long day);
    -> 		if if_date>now() then
    -> 			update table1 set status=1 where id=id1;
    -> 		end if;
    -> 	fetch cur_record into time_start1,time_long1,id1;
    -> end while;
    ->
    -> close cur_record;
    ->
    -> end;
    -> //


delimiter //
create procedure proc1()
begin
declare no_more_record int default 0;
declare time_start1 date;
declare time_long1 int;
declare id1 int;
declare time_add date;
declare time_now date;
declare cur_record cursor for select time_start,time_long,id from table1;
DECLARE CONTINUE HANDLER FOR NOT FOUND  SET  no_more_record = 1;
open cur_record;
fetch cur_record into time_start1,time_long1,id1;
set time_now=now();

select time_now;
select time_start1;
select time_long1;
select id1;

while no_more_record !=1 do
set time_add=date_add(time_start1,interval time_long1 second);

select time_add;

if time_add<time_now then
update table1 set status=1 where id=id1;
end if;
fetch cur_record into time_start1,time_long1,id1;
end while;
close cur_record;
end
//
delimiter ;

create event e_proc1
on schedule every 1 second starts '2016-5-14 20:00:00'
on completion preserve
do call proc1();

//如何进行对存储过程中的变量进行测试
delimiter //
CREATE  PROCEDURE test()
begin

  declare a varchar(10);

  set a = "test";

  select a;

  select a;

end
//
delimiter ;


procedure中传参的区别
Ⅰ. IN参数例子-----------------------IN 输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
创建:
mysql > DELIMITER //  
mysql > CREATE PROCEDURE demo_in_parameter(IN p_in int)  
-> BEGIN   
-> SELECT p_in;   
-> SET p_in=2;   
-> SELECT p_in;   
-> END;   
-> //  
mysql > DELIMITER ; 

执行结果:
mysql > SET @p_in=1;  
mysql > CALL demo_in_parameter(@p_in);  
+------+  
| p_in |  
+------+  
|   1  |   
+------+  
 
+------+  
| p_in |  
+------+  
|   2  |   
+------+  
 
mysql> SELECT @p_in;  
+-------+  
| @p_in |  
+-------+  
|  1    |  
+-------+  

以上可以看出，p_in虽然在存储过程中被修改，但并不影响@p_id的值
 
Ⅱ.OUT参数例子-----------------------------OUT 输出参数:该值可在存储过程内部被改变，并可返回
创建:
mysql > DELIMITER //  
mysql > CREATE PROCEDURE demo_out_parameter(OUT p_out int)  
-> BEGIN 
-> SELECT p_out;  
-> SET p_out=2;  
-> SELECT p_out;  
-> END;  
-> //  
mysql > DELIMITER ; 

执行结果:
mysql > SET @p_out=1;  
mysql > CALL sp_demo_out_parameter(@p_out);  
+-------+  
| p_out |   
+-------+  
| NULL  |   
+-------+  
 
+-------+  
| p_out |  
+-------+  
|   2   |   
+-------+  
 
mysql> SELECT @p_out;  
+-------+  
| p_out |  
+-------+  
|   2   |  
+-------+