Shulou(Shulou.com)06/01 Report--

The following brings you about how to use stored procedures and events in Mysql to automatically create database tables. If you are interested, let's take a look at this article. I believe it will be of some help to you after reading how to use stored procedures and events to automatically create database tables in Mysql.

Business requirements, the app user boot into a log table app_open_log.

In the seven months since it was launched, there have been 740000 records.

It is now considered to divide the database and table. Create a database table prefixed with app_open_log_ and suffixed with date, year and month at the beginning of each month, such as app_open_log_201807.

The idea of realization is:

How does Mysql automatically build tables every month?

First, a new event calls a stored procedure every month

Second, create tables in the stored procedure

1. Get the current time and convert the string

2. Build tables by stitching sql statements

Realization method

Copy the following two paragraphs to sql and execute them.

First create a stored procedure:

DELIMITER / / CREATE PROCEDURE create_table_app_open_log_month () BEGINDECLARE `@ suffix` VARCHAR (15); DECLARE` @ sqlstr` VARCHAR (2560); SET `@ suffix` = DATE_FORMAT (DATE_ADD (NOW (), INTERVAL 1 MONTH),'_% Y% m') SET @ sqlstr = CONCAT ("CREATE TABLE jz_app_open_log", `@ suffix`, "(`id` int (11) NOT NULL AUTO_INCREMENT, `equipment_ type` varchar (45) DEFAULT NULL, `equipment_ version` varchar (45) DEFAULT NULL, `rom`varchar (45) DEFAULT NULL, `cpu` varchar (45) DEFAULT NULL, `mac`varchar (100) DEFAULT NULL, `ip`varchar (50) DEFAULT NULL, `version_ code` varchar (10) DEFAULT NULL, `client`varchar (45) DEFAULT'', `create_ time` int (10) DEFAULT NULL `version_ name` varchar (45) DEFAULT', `v`varchar (10) DEFAULT'', PRIMARY KEY (`id`), UNIQUE KEY `id_ UNIQUE` (`id`) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC "); PREPARE stmt FROM @ sqlstr;EXECUTE stmt;END

Then create an event to execute the above stored procedure on the 1st of each month:

DELIMITER $$SET GLOBAL event_scheduler = 1 create EVENT event_create_table_every_monthON SCHEDULE EVERY 1 MONTHSTARTS '2018-07-01 00:00:00'ON COMPLETION PRESERVEENABLEDOBEGINCALL create_table_app_open_log_month (); END

You can see it through Navicate.

This is OK!

Extended knowledge: what is a stored procedure?

In some languages, there is the concept of "procedure", procedure, and "function", function

There are no procedures in PHP, only functions.

Procedure: several language names are encapsulated and these wrappers are executed when called.

Function: is a process with a return value.

Procedure: a function that does not return a value.

We encapsulate a number of sql and name them,-process.

Store this procedure in the database,-stored procedure.

Stored procedure creation syntax:

Definition:

Create procedure procedureName ()

Begin

-- sql statement

End$

View:

Show procedure status

Call:

Call procedure ()

Stored procedures are programmable

That is, you can use variables, expressions, control statements to complete complex functions.

In a stored procedure, declare variables with declare

Format: declare variable name variable type.

Example:

Create procedure p2 () begin declare age int default 18 height int default 180 select concat ('age is', age,' height is', height); end$

If you have variables, you can operate, and if you have operations, you can control them.

The result of the operation, how to assign values to variables.

Set variable name: = variable value

Example:

Create procedure p3 () begindeclare age int default 18 set age:=age+20;select concat ('20 years later age is', age); end$

-- if/else control structure

If condition then

Statement

Else

End if

-- P5 passes parameters to the stored procedure

In parentheses of a stored procedure, you can declare parameters

Syntax is [in/out/inout] parameter name parameter type

Example:

Create procedure p5 (width int,height int) begin select concat ('your area is', width*height) as area;if width > height thenselect 'you are fat'; else if width

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.