CREATE TABLE `stream_history` ( `itemid` bigint(20) unsigned NOT NULL, `clock` int(11) NOT NULL DEFAULT '0', `value` double(16,4) NOT NULL DEFAULT '0.0000', KEY `pk_stream_history` (`itemid`,`clock`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into testa values (1,'88');
mysql> select * from stream_history;
+--------+-------+--------+
| itemid | clock | value |
+--------+-------+--------+
| 1 | 0 | 0.0000 |
+--------+-------+--------+
1 row in set (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'bodaodao'@'192.168.23.29' IDENTIFIED BY PASSWORD '*9C03831AA1B0E9CECE4AE76C226CCF9952C0BDAF';
mysql> select host,user,password from mysql.user;
+-----------------+-------------+-------------------------------------------+
| host | user | password |
+-----------------+-------------+-------------------------------------------+
| localhost | root | |
| mic1.oracle.com | root | |
| 127.0.0.1 | root | |
| ::1 | root | |
| localhost | | |
| mic1.oracle.com | | |
| 192.168.56.% | chris | *27D57276AC96BEA22D05B92B91E2CDB3FD0A2226 |
| % | common_user | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | common_user | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 192.168.56.173 | user2 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 192.168.56.173 | developer | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 192.168.56.171 | tell | *E7D81E1AF4AF511842764613D47F7A7537D21E92 |
| 192.168.23.29 | bodaodao | *9C03831AA1B0E9CECE4AE76C226CCF9952C0BDAF |
+-----------------+-------------+-------------------------------------------+
13 rows in set (0.00 sec)
创建事件,每30分钟清空表stream_history
SET session SQL_MODE='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'; --如果不加,show create event event_truncate_stream\G;sql_mode处为空
CREATE DEFINER=`bodaodao`@`192.168.23.29` EVENT `event_truncate_stream` ON SCHEDULE EVERY 30 MINUTE STARTS '2014-07-11 12:20:00' ON COMPLETION NOT PRESERVE ENABLE DO truncate table stream_history;
mysql> show create event event_truncate_stream\G;
*************************** 1. row ***************************
Event: event_truncate_stream
sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
time_zone: SYSTEM
Create Event: CREATE DEFINER=`bodaodao`@`192.168.23.29` EVENT `event_truncate_stream` ON SCHEDULE EVERY 30 MINUTE STARTS '2013-06-26 15:10:00' ON COMPLETION NOT PRESERVE ENABLE DO truncate table stream_history
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
ERROR:
No query specified
insert into testa values (1,'88');
mysql> select * from stream_history;
+--------+-------+--------+
| itemid | clock | value |
+--------+-------+--------+
| 1 | 0 | 0.0000 |
+--------+-------+--------+
1 row in set (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'bodaodao'@'192.168.23.29' IDENTIFIED BY PASSWORD '*9C03831AA1B0E9CECE4AE76C226CCF9952C0BDAF';
mysql> select host,user,password from mysql.user;
+-----------------+-------------+-------------------------------------------+
| host | user | password |
+-----------------+-------------+-------------------------------------------+
| localhost | root | |
| mic1.oracle.com | root | |
| 127.0.0.1 | root | |
| ::1 | root | |
| localhost | | |
| mic1.oracle.com | | |
| 192.168.56.% | chris | *27D57276AC96BEA22D05B92B91E2CDB3FD0A2226 |
| % | common_user | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | common_user | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 192.168.56.173 | user2 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 192.168.56.173 | developer | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| 192.168.56.171 | tell | *E7D81E1AF4AF511842764613D47F7A7537D21E92 |
| 192.168.23.29 | bodaodao | *9C03831AA1B0E9CECE4AE76C226CCF9952C0BDAF |
+-----------------+-------------+-------------------------------------------+
13 rows in set (0.00 sec)
创建事件,每30分钟清空表stream_history
SET session SQL_MODE='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'; --如果不加,show create event event_truncate_stream\G;sql_mode处为空
CREATE DEFINER=`bodaodao`@`192.168.23.29` EVENT `event_truncate_stream` ON SCHEDULE EVERY 30 MINUTE STARTS '2014-07-11 12:20:00' ON COMPLETION NOT PRESERVE ENABLE DO truncate table stream_history;
mysql> show create event event_truncate_stream\G;
*************************** 1. row ***************************
Event: event_truncate_stream
sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
time_zone: SYSTEM
Create Event: CREATE DEFINER=`bodaodao`@`192.168.23.29` EVENT `event_truncate_stream` ON SCHEDULE EVERY 30 MINUTE STARTS '2013-06-26 15:10:00' ON COMPLETION NOT PRESERVE ENABLE DO truncate table stream_history
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
ERROR:
No query specified