【SQL篇章--DATABASE/EVENTS】，--databaseevents

【SQL篇章--DATABASE/EVENTS】，--databaseevents

【SQL篇章】【SQL語句梳理 :--基於MySQL5.6】【已梳理：DATABASE/EVENTS】【會堅持完善】   目錄: 1. Data Definition Statements: 　　1.1 create database, alter database, show databases 　　1.2 create event, alter event, show events     1. Data Definition Statements: 1.1 CREATE DATABASE 格式：

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] create_specification :
　　[DEFAULT] CHARACTER SET [=] charset_name
　　[DEFAULT] COLLATE [=] collation_name

　　

事例 :

CREATE DATABASE db2;
CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET latin1 */

  查看：

SHOW DATABASES;

  ALTER DATABASE 格式：

ALTER {DATABASE | SCHEMA} [db_name]
　　alter_specification ...
ALTER {DATABASE | SCHEMA} db_name
　　UPGRADE DATA DIRECTORY NAME
 
alter_specification:
　　[DEFAULT] CHARACTER SET [=] charset_name  | [DEFAULT] COLLATE [=] collation_name

　　

  事例操作： <1> -->db1:utf8

ALTER DATABASE db1 CHARACTER SET = utf8;

  查看：

mysql> SELECT * FROM information_schema.`SCHEMATA` WHERE schema_name='db1';
+--------------+-------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+-------------+----------------------------+------------------------+----------+
| def          | db1         | utf8                       | utf8_general_ci        | NULL     |
+--------------+-------------+----------------------------+------------------------+----------+
1 row in set (0.04 sec)

　　

<2> -->db1:utf8-->latin1

ALTER DATABASE db1 CHARACTER SET = latin1;

　　

查看：

mysql> SELECT * FROM information_schema.`SCHEMATA` WHERE schema_name='db1';
+--------------+-------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+-------------+----------------------------+------------------------+----------+
| def          | db1         | latin1                     | latin1_swedish_ci      | NULL     |
+--------------+-------------+----------------------------+------------------------+----------+
1 row in set (0.00 sec)

　　

<3> --創建表t5:  <db1:latin1>

CREATE TABLE t5(id int);

　　查看所建立表的字符集

mysql> SHOW CREATE TABLE db1.t5;
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table                                                                           |
+-------+----------------------------------------------------------------------------------------+
| t5    | CREATE TABLE `t5` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

　　測試發現：

　　1.db的character改變後，collation隨之改變。
　　2.db中舊表character不會改變。新建表character默認為與當前db相同。

　　

  SHOW DATABASES; 格式： SHOW {DATABASES | SCHEMAS} [LIKE 'pattern' | WHERE expr]   DROP DATABASE; 格式：

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

  　　1.IF EXISTS : DB不存在，不會報錯 　　2.DB drop掉，對應的物理目錄也會刪除。但是DB對應目錄下有其它文件，無法執行drop DB的操作，報錯

mysql> drop database wb;
ERROR 1010 (HY000): Error dropping database (can't rmdir './wb/', errno: 17)

　　

刪除非數據庫目錄或文件：

mysql> drop database wb;
Query OK, 0 rows affected (0.00 sec)
 

　　

1.2 CREATE EVENT 格式：

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}

　　

注意事項：

• 創建event需要：event，super權限，建好之後，必須是enable狀態。
• ON SCHEDULE ： 決定什麼時間或者多長時間，時間執行一次
• DO :包含被event執行的SQL語句

默認 EVENT 支持是沒有啟動的，可以通過下面的命令來查看狀態：

Select @@event_scheduler;

　　

如果返回 OFF ，則需要執行下面的命令啟動：

SET GLOBAL event_scheduler = ON;

　　

好了，上面雖然啟動了 EVENT ，但是每次重啟 mysql 之後 EVENT 並沒有自動啟動，那麼如何讓它自動啟動呢？ 方法一：找到當前使用的 .cnf 文件

[mysqld] 
event_scheduler=1

　　

方法二：啟動 mysql 的時候增加 --event_scheduler=1

mysql start --event_scheduler=1

　　

事例： 1.只執行一次

CREATE
DEFINER = CURRENT_USER
EVENT IF NOT EXISTS myevent
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND
DO
INSERT INTO db1.t1 VALUES(1);

　　

2.每隔一秒執行一次

CREATE
DEFINER = CURRENT_USER
EVENT IF NOT EXISTS myevent
ON SCHEDULE EVERY 1 SECOND
DO
INSERT INTO db1.t1 VALUES(1);

　　

3.event的執行時間是過去時，在創建時，就drop掉了，當前創建的event是沒有顯示的。

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2016-11-16 10:01:39 |
+---------------------+
1 row in set (0.00 sec)

mysql> CREATE EVENT event_1
    -> ON SCHEDULE AT '2006-02-10 23:59:00'
    -> DO INSERT INTO test.totals VALUES (NOW());
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1588
Message: Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation.
1 row in set (0.00 sec)

  ON SCHEDULE 解析： 1. 'two minutes and three seconds from now'

AT CURRENT_TIMESTAMP + INTERVAL '2:3' MINUTE_SECOND

'three weeks and two days from now'

AT CURRENT_TIMESTAMP + INTERVAL 3 WEEK + INTERVAL 2 DAY

  2. 間隔值頻率相同，可以用EVERY，不與'+INTERVAL'同時存在

ON SCHEDULE EVERY 6 WEEK

 

STARTS: 後面緊接timestamp,指示從什麼時間開始執行repeating，可以用：+ INTERVAL interval 指示：從現在開始經多長時間後開始執行repeating. eg1:'every three months, beginning one week from now':從現在開始1周之後，開始執行，每隔3月的重復操作。

EVERY 3 MONTH STARTS CURRENT_TIMESTAMP + INTERVAL 1 WEEK means

eg2:'every two weeks, beginning six hours and fifteen minutes from now'

EVERY 2 WEEK STARTS CURRENT_TIMESTAMP + INTERVAL '6:15' HOUR_MINUTE.

  ENDS : 同starts相反，指示從什麼時間停止執行repeating。用法同starts eg: 'every twelve hours, beginning thirty minutes from now, and ending four weeks from now'

EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 30 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 4 WEEK

 

備注：STARTS,ENDS,可以同時使用，也可用其一，也可以使用復雜的時間單元   [ON COMPLETION [NOT] PRESERVE] 解析： 測試事例： eg:

CREATE DEFINER=CURRENT_USER EVENT IF NOT EXISTS myevent
ON SCHEDULE EVERY 1 SECOND STARTS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 3 MINUTE
DO
INSERT INTO db1.`t1`(id) VALUES(5);

  創建完成，再次查看event：

DELIMITER $$
ALTER DEFINER=`admin`@`%` EVENT `myevent` 
ON SCHEDULE EVERY 1 SECOND STARTS '2016-11-30 16:42:02' ENDS '2016-11-30 16:44:02' 
ON COMPLETION NOT PRESERVE ENABLE 
DO INSERT INTO db1.`t1`(id) VALUES(5)$$
DELIMITER ;

　　

[ENABLE | DISABLE | DISABLE ON SLAVE]解析； 通過設置：ENABLE：啟動event；DISABLE：停止event。在ALTER EVENT時，很常用。 DISABLE ON SLAVE : 設置主從復制時，標示從庫的event的狀態。event會在master上創建，並復制到從庫，但是不會在從庫上執行。   測試事例：

CREATE DEFINER=CURRENT_USER EVENT IF NOT EXISTS myevent
ON SCHEDULE EVERY 1 SECOND STARTS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 3 MINUTE
ON COMPLETION PRESERVE ENABLE
DO
INSERT INTO db1.t1(id) VALUES(1);

觀察master 和 slave上event的結構：　　

MASTER:

DELIMITER $$
ALTER DEFINER=`admin`@`%` EVENT `myevent` 
ON SCHEDULE EVERY 1 SECOND STARTS '2016-11-16 01:35:40' ENDS '2016-11-16 01:37:40' 
ON COMPLETION PRESERVE ENABLE DO INSERT INTO db1.t1(id) VALUES(1)$$
DELIMITER ;

　　

SLAVE:

DELIMITER $$
ALTER DEFINER=`admin`@`%` EVENT `myevent` 
ON SCHEDULE EVERY 1 SECOND STARTS '2016-11-16 01:35:40' ENDS '2016-11-16 01:37:40' 
ON COMPLETION PRESERVE DISABLE ON SLAVE DO INSERT INTO db1.t1(id) VALUES(1)$$
DELIMITER ;

  通過系統表EVENTS查看狀態 MASTER:

mysql> SELECT EVENT_SCHEMA,EVENT_NAME,STATUS FROM `information_schema`.`EVENTS` WHERE EVENT_SCHEMA='db1';
+--------------+------------+----------+
| EVENT_SCHEMA | EVENT_NAME | STATUS   |
+--------------+------------+----------+
| db1          | myevent    | DISABLED |
+--------------+------------+----------+
1 row in set (0.00 sec)　

SLAVE:

mysql> SELECT EVENT_NAME,STATUS FROM `information_schema`.`EVENTS`;
+------------+--------------------+
| EVENT_NAME | STATUS             |
+------------+--------------------+
| myevent    | SLAVESIDE_DISABLED |
+------------+--------------------+

 

[COMMENT 'comment']解析：

描述EVENT,最多64字符，用引號引起來。   DO 解析： 指定EVENT所執行的動作，可以是任何SQL。   SQL_MODE 解析:

mysql> SELECT SQL_MODE FROM information_schema.`EVENTS`;
+--------------------------------------------+
| SQL_MODE |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+

　　

對EVENT執行create ，alter操作時，MYSQL存儲的SQL_MODE系統變量總是被強制設置。 regardless of the current server SQL mode when the event begins executing.   DO 中使用復合語句 select,show 在event中是沒有效果的。但是可以使用：select. . . insert, insert into . . . select 復合語句在event中，可以使用EBGIN,END關鍵字： 1.

DELIMITER $$
CREATE EVENT e_count1
ON SCHEDULE
EVERY 4 SECOND
COMMENT 'Saves total number of tb1'
DO
BEGIN
INSERT INTO t2(TIME,total) SELECT CURRENT_TIMESTAMP,COUNT(*) FROM t1;
DELETE FROM t1;
END $$
DELIMITER ;

　　

  2.下面的event中應用了：本地變量、錯誤處理、流控制結構

DELIMITER $$
 
CREATE EVENT e
ON SCHEDULE
EVERY 5 SECOND
DO
BEGIN
DECLARE v INTEGER;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
SET v = 0;
 
WHILE v < 5 DO
INSERT INTO t3 VALUES(0);
UPDATE t3 SET s1 = s1 + 1;
SET v = v + 1;
END WHILE;
END $$
DELIMITER ;

　　

EVENT中調用存儲過程

CREATE EVENT e_call_myproc
ON SCHEDULE
AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
DO CALL myproc(5, 27);

  ALTER EVENT; 格式：

ALTER
[DEFINER = { user | CURRENT_USER }]
EVENT event_name
[ON SCHEDULE schedule]
[ON COMPLETION [NOT] PRESERVE]
[RENAME TO new_event_name]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'comment']
[DO event_body]

 

事例操作： 主干語句：ALTER EVENT event_name； 1.前提EVENT必須存在

mysql> alter event event1 on schedule every '2:3' DAY_HOUR ;
ERROR 1539 (HY000): Unknown event 'event1'

　　

2.

ALTER EVENT myevent
ON SCHEDULE
EVERY 2 SECOND STARTS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE ENABLE
DO
INSERT INTO t2(TIME,total) VALUES(CURRENT_TIMESTAMP,5);

　　

3.停止event

ALTER EVENT myevent DISABLE;

　　

4.EVENT改名

ALTER EVENT olddb.myevent RENAME TO newdb.myevent;

　　

SHOW EVENTS: 格式：

SHOW EVENTS [{FROM | IN} schema_name] [LIKE 'pattern' | WHERE expr]

　　

事例： <1> show events; 列出當前DB中所有events。

mysql> select current_user(),schema();
+-----------------+----------+
| current_user() | schema() |
+-----------------+----------+
| admin@localhost | db1 |
+-----------------+----------+
1 row in set (0.00 sec)

　　

mysql> show events\G
*************************** 1. row ***************************
                  Db: db1
                Name: myevent
             Definer: admin@%
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 2
      Interval field: SECOND
              Starts: 2016-11-16 08:37:26
                Ends: 2016-11-16 01:44:11
              Status: DISABLED
          Originator: 5
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.07 sec)

  <2> 列出指定DB下的events

SHOW EVENTS FROM wb;
SHOW EVENTS FROM wb like '%wb';

　　

<3> show events輸出關鍵詞解析：   Type: EVENT的重復執行類型 ONE TIME (transient) or RECURRING (repeating). Execute：  執行一次的event顯示：AT 　　　　　　重復執行的event顯示NULL Interval value: 2 一次執行完畢到下次執行時的間隔。 Interval field: SECOND 執行間隔時間單位 Status: event的狀態 Originator MySQL server 的 ID   <4>

SHOW CREATE EVENT event_name;

　　

mysql> show create event myevent\G
*************************** 1. row ***************************
Event: myevent
sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
time_zone: SYSTEM
Create Event: CREATE DEFINER=`admin`@`%` EVENT `myevent` 
ON SCHEDULE EVERY 2 SECOND STARTS '2016-11-16 08:37:26' ENDS '2016-11-16 01:44:11' 
ON COMPLETION PRESERVE DISABLE 
DO insert into t2(Time,total) values(current_timestamp,5)
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)

　　

DROP EVENTS 格式：

DROP EVENT [IF EXISTS] event_name

　　

1.刪除不存在的EVENT,報錯

mysql> DROP EVENT E;
ERROR 1539 (HY000): Unknown event 'E'

　　

2.EVENT不存在，消除報錯

mysql> DROP EVENT IF EXISTS E;
Query OK, 0 rows affected, 1 warning (0.00 sec)

　　

3.EVENT存在，正常刪除

mysql> DROP EVENT e_1;
Query OK, 0 rows affected (0.00 sec)