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

How to clean up the Mysql database specific table useless data stored procedures, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

When I play with stored procedures for the first time, I encounter a lot of problems. Record them for future reference.

Note: logtable is a temporary table used to call the trial, there are "index,log1,log2,log3,log4,log5,log6,log7,log8" nine fields.

CREATE TABLE IF NOT EXISTS logtable (

`index` double NOT NULL AUTO_INCREMENT

`log1` varchar (255) DEFAULT NULL

`log2` varchar (255) DEFAULT NULL

`log3` varchar (255) DEFAULT NULL

`log4` varchar (255) DEFAULT NULL

`log5` varchar (255) DEFAULT NULL

`log6` varchar (255) DEFAULT NULL

`log7` varchar (255) DEFAULT NULL

PRIMARY KEY (`index`)

);

# define stored procedures

# define function

DROP PROCEDURE IF EXISTS insertAsSelectProducts

CREATE PROCEDURE insertAsSelectProducts ()

BEGIN

DECLARE tablesNameVar VARCHAR (100)

DECLARE done INT DEFAULT 0

DECLARE datatime VARCHAR (15) DEFAULT '201910010000'

DECLARE columnVar VARCHAR (100) DEFAULT'0'

DECLARE loginfo VARCHAR (100) DEFAULT'0'

# SET datatime = '201910010000'

# create a log table if it does not exist

# find out the specific table on which you want to clean the data

DECLARE tableName CURSOR FOR SELECT table_name FROM information_schema.TABLES

WHERE table_schema ='26 area'

AND table_name NOT LIKE'% setting Table'

# AND table_name LIKE'% Daily Statistics%'

AND table_name NOT LIKE'% Real time%'

AND table_name 'logtable'

AND table_name NOT LIKE'% Patrol%'

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1

# Open the cursor

OPEN tableName

Group_loop: LOOP

FETCH tableName INTO tablesNameVar

IF done = 1 THEN

# Log

INSERT INTO logtable (log1) SELECT 'done==1 so leave loop'

LEAVE group_loop

END IF

# copy the data needed by stitching sql query to × _ copy table

# Log

INSERT INTO logtable (log1) SELECT tablesNameVar

SET @ columnflag = 0

# determine whether there is a datatime column in the table

SET @ columnVar1 = 0

SET @ var_sql = concat (

'SELECT count (*) into @ columnVar1'

"FROM information_schema.columns WHERE table_schema = DATABASE () AND table_name ='"

TablesNameVar

"'AND column_name =' datatime'"

);

PREPARE s FROM @ var_sql

EXECUTE s

DEALLOCATE PREPARE s

IF @ columnVar11 THEN

# determine whether there is a 'statistical date' column in the table

SET @ columnVar1 = 0

SET @ var_sql = concat (

'SELECT count (*) into @ columnVar1'

"FROM information_schema.columns WHERE table_schema = DATABASE () AND table_name ='"

TablesNameVar

"'AND column_name =' Statistical date'"

);

PREPARE s FROM @ var_sql

EXECUTE s

DEALLOCATE PREPARE s

IF @ columnVar1=1 THEN

SET @ columnflag = 2

ELSE

SET @ columnflag = 3

END IF

ELSE

SET @ columnflag = 1

END IF

# Log

UPDATE logtable SET log5 = @ columnVar1 WHERE log1 = tablesNameVar

IF @ columnflag=1 THEN

SET @ var_sql1 = concat (

'CREATE TABLE'

TablesNameVar

'_ COPY'

'SELECT * FROM'

TablesNameVar

'WHERE DATATIME >'

Datatime

);

SET @ var_sql1_count = concat (

'SELECT count (*) into @ columnVar1_count FROM'

TablesNameVar

'WHERE DATATIME >'

Datatime

);

ELSEIF @ columnflag=2 THEN

SET @ var_sql1 = concat (

'CREATE TABLE'

TablesNameVar

'_ COPY'

'SELECT * FROM'

TablesNameVar

'date of WHERE statistics >'

LEFT (datatime,8)

);

SET @ var_sql1_count = concat (

'SELECT count (*) into @ columnVar1_count FROM'

TablesNameVar

'date of WHERE statistics >'

LEFT (datatime,8)

);

ELSE

SET @ var_sql1 = concat (

'CREATE TABLE'

TablesNameVar

'_ COPY'

'SELECT * FROM'

TablesNameVar

'Dat WHERE >'

LEFT (datatime,8)

);

SET @ var_sql1_count = concat (

'SELECT count (*) into @ columnVar1_count FROM'

TablesNameVar

'Dat WHERE >'

LEFT (datatime,8)

);

END IF

# determine whether the × _ copy table exists, and delete it if it exists

SET @ sql_Exit = concat (

'drop table if exists'

TablesNameVar

'_ COPY'

);

PREPARE s1_exit FROM @ sql_Exit

EXECUTE s1_exit

DEALLOCATE PREPARE s1_exit

PREPARE s1_count FROM @ var_sql1_count

EXECUTE s1_count

DEALLOCATE PREPARE s1_count

UPDATE logtable SET log7 = @ columnVar1_count WHERE log1 = tablesNameVar

# determine whether the table has data to be backed up, empty the table directly without data to be backed up, and enter the next cycle

IF @ columnVar1_count

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.