[SQL chapter -- DATABASE/EVENTS], -- databaseevents

[SQL chapter -- DATABASE/EVENTS], -- databaseevents
[SQL chapter] [SQL statement sorting: -- Based on MySQL5.6] [sorted: DATABASE/EVENTS] [will be improved] directory:1. Data Definition Statements:1.1 create database, alter database, show databases 1.2 create event, alter event, show events1. Data Definition Statements:1.1Create database Format:

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

　　

Example:

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

View:

SHOW DATABASES;

Alter database format:

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

　　

Case operation: <1> --> db1: utf8

ALTER DATABASE db1 CHARACTER SET = utf8;

View:

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;

　　

View:

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> -- Create Table t5: <db1: latin1>

CREATE TABLE t5(id int);

View the character set of the created table

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)

Test found:

1. After the character of db changes, collation changes accordingly. 2. The character of the old table in db will not change. The new character table is the same as the current db by default.

　　

Show databases; format: SHOW {DATABASES | SCHEMAS} [LIKE 'pattern' | WHERE expr] drop database; format:

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

1. if exists: the database does not exist, and 2.DB drop is not reported. The corresponding physical directory is also deleted. However, if there are other files in the directory corresponding to the DB, the drop DB operation cannot be performed and an error is returned.

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

　　

Delete non-database directories or files:

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

　　

1.2Create event format:

CREATE[DEFINER = { user | CURRENT_USER }]EVENT[IF NOT EXISTS]event_nameON 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}

　　

Note:

• To create an event, you must have the "event" and "super" permissions. After the event is created, it must be in the "enable" state.
• On schedule: determines the time or interval for execution.
• DO: contains the SQL statement executed by event

By default, EVENT support is not started. You can view the status using the following command:

Select @@event_scheduler;

　　

If OFF is returned, run the following command to start:

SET GLOBAL event_scheduler = ON;

　　

Okay. Although the EVENT was started above, the EVENT was not automatically started after mysql was restarted every time. How can we make it automatically started? Method 1: Find the currently used. cnf File

[mysqld] event_scheduler=1

　　

Method 2: Add -- event_scheduler = 1 when starting mysql.

mysql start --event_scheduler=1

　　

Example: 1. Run only once

CREATEDEFINER = CURRENT_USEREVENT IF NOT EXISTS myeventON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECONDDOINSERT INTO db1.t1 VALUES(1);

　　

2. Run the command once every second.

CREATEDEFINER = CURRENT_USEREVENT IF NOT EXISTS myeventON SCHEDULE EVERY 1 SECONDDOINSERT INTO db1.t1 VALUES(1);

　　

3. The event execution time is past. When it is created, it is dropped. The currently created event is not displayed.

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: 1588Message: 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 resolution: 1. 'Two minutes and three seconds from rights'

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. The INTERVAL value has the same frequency. You can use EVERY instead of '+ interval'.

ON SCHEDULE EVERY 6 WEEK

 

STARTS: followed by timestamp, which indicates the start time of repeating. You can use: + INTERVAL interval to indicate the time after which repeating will be executed. eg1: 'Every three months, beginning one week from now ': the repeat operation started every other month after one week.

EVERY 3 MONTH STARTS CURRENT_TIMESTAMP + INTERVAL 1 WEEK means

Eg2: 'every two weeks, beginning six hours and fifteen minutes from rights'

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

ENDS: opposite to starts, it indicates the time at which repeating is stopped. Usage is the same as startseg: '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

 

Note: STARTS and ENDS can be used or used at the same time. You can also use a complex time unit [on completion [NOT] PRESERVE] to parse: Test Case: eg:

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

After creation, view the event again:

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 on slave] parsing; ENABLE event by setting ENABLE; DISABLE event. It is often used in alter event. Disable on slave: indicates the event status of the SLAVE database When configuring master-SLAVE replication. Event will be created on the master and copied to the slave database, but will not be executed on the slave database. Test example:

CREATE DEFINER=CURRENT_USER EVENT IF NOT EXISTS myeventON SCHEDULE EVERY 1 SECOND STARTS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 3 MINUTEON COMPLETION PRESERVE ENABLEDOINSERT INTO db1.t1(id) VALUES(1);

Observe the event structure on the master and slave:

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 ;

Run the system table EVENTS to view the status of 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'] parsing:

Description EVENT, which can contain up to 64 characters and is enclosed by quotation marks. DO parsing: Specifies the action performed by the EVENT, which can be any SQL statement. SQL _MODE parsing:

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

　　

When the create or alter operation is performed on the EVENT, the SQL _MODE system variable stored in MYSQL is always set forcibly. Regardless of the current server SQL mode when the event begins executing. DO uses the composite statement select. show has no effect in the event. But you can use: select... insert, insert into... select compound statement in the event, you can use EBGIN, END Keyword: 1.

DELIMITER $$CREATE EVENT e_count1ON SCHEDULEEVERY 4 SECONDCOMMENT 'Saves total number of tb1'DOBEGININSERT INTO t2(TIME,total) SELECT CURRENT_TIMESTAMP,COUNT(*) FROM t1;DELETE FROM t1;END $$DELIMITER ;

　　

2. The following events are applied: local variables, error handling, and flow control structure.

DELIMITER $$ CREATE EVENT eON SCHEDULEEVERY 5 SECONDDOBEGINDECLARE v INTEGER;DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;SET v = 0; WHILE v < 5 DOINSERT INTO t3 VALUES(0);UPDATE t3 SET s1 = s1 + 1;SET v = v + 1;END WHILE;END $$DELIMITER ;

　　

The stored procedure is called in the EVENT.

CREATE EVENT e_call_myprocON SCHEDULEAT CURRENT_TIMESTAMP + INTERVAL 1 DAYDO CALL myproc(5, 27);

Alter event; format:

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]

 

Case operation: Trunk statement: alter event event_name; 1. Prerequisite EVENT must exist

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

　　

2.

ALTER EVENT myeventON SCHEDULEEVERY 2 SECOND STARTS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE ENABLEDOINSERT INTO t2(TIME,total) VALUES(CURRENT_TIMESTAMP,5);

　　

3. Stop the event

ALTER EVENT myevent DISABLE;

　　

4. Rename EVENT

ALTER EVENT olddb.myevent RENAME TO newdb.myevent;

　　

Show events: format:

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

　　

Example: <1> show events; list all events in the current DB.

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: 5character_set_client: utf8collation_connection: utf8_general_ci  Database Collation: utf8_general_ci1 row in set (0.07 sec)

<2> list events in a specified database

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

　　

<3> keyword parsing of show events output: Type: Repeated EVENT execution Type: one time (transient) or RECURRING (repeating ). execute: Run Once event display: AT repeatedly executed event display NULLInterval value: 2 the interval from the execution to the next execution. Interval field: SECOND execution Interval unit Status: event Status Originator MySQL server ID <4>

SHOW CREATE EVENT event_name;

　　

mysql> show create event myevent\G*************************** 1. row ***************************Event: myeventsql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTIONtime_zone: SYSTEMCreate 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: utf8collation_connection: utf8_general_ciDatabase Collation: utf8_general_ci1 row in set (0.00 sec)

　　

Drop events format:

DROP EVENT [IF EXISTS] event_name

　　

1. delete an EVENT that does not exist. An error is returned.

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

　　

2. EVENT does not exist. Eliminate the error

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

　　

3. The EVENT exists and is deleted normally.

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