delimiter $$
use `csf` $$
drop procedure if exists proc_alter_engine_all $$
use `csf` $$
create procedure proc_alter_engine_all
SQL SECURITY INVOKER
begin
declare v_tbname varchar(500) default '';
declare v_flag int default 0;
declare _done int default 0;
declare cur_tbnames cursor for
select table_schema,table_name
from information_schema.tables
where table_schema not in('information_schema','mysql','test') and engine='MyISAM';
declare continue handler for not found
begin
set v_flag=1;
commit;
end;
-- handler exception
declare exit handler for sqlexception
begin
set v_flag=0;
end;
set v_flag=1;
end在MySQL Workbench里面执行报错:Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SQL SECURITY INVOKER
begin
declare v_tbname varchar(500) default '';
dec' at line 2
use `csf` $$
drop procedure if exists proc_alter_engine_all $$
use `csf` $$
create procedure proc_alter_engine_all
SQL SECURITY INVOKER
begin
declare v_tbname varchar(500) default '';
declare v_flag int default 0;
declare _done int default 0;
declare cur_tbnames cursor for
select table_schema,table_name
from information_schema.tables
where table_schema not in('information_schema','mysql','test') and engine='MyISAM';
declare continue handler for not found
begin
set v_flag=1;
commit;
end;
-- handler exception
declare exit handler for sqlexception
begin
set v_flag=0;
end;
set v_flag=1;
end在MySQL Workbench里面执行报错:Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SQL SECURITY INVOKER
begin
declare v_tbname varchar(500) default '';
dec' at line 2
use `csf` $$
DROP PROCEDURE IF EXISTS proc_alter_engine_all $$
use `csf` $$
CREATE PROCEDURE proc_alter_engine_all()
BEGIN
DECLARE v_tbname VARCHAR(500) DEFAULT '';
DECLARE v_flag INT DEFAULT 0;
DECLARE _done INT DEFAULT 0;
DECLARE cur_tbnames CURSOR FOR
SELECT table_schema,table_name
FROM information_schema.TABLES
WHERE table_schema NOT IN('information_schema','mysql','test') AND ENGINE='MyISAM';
DECLARE CONTINUE HANDLER FOR NOT FOUND
BEGIN
SET v_flag=1;
COMMIT;
END;
-- handler exception
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET v_flag=0;
END;
SET v_flag=1;
END $$
DELIMITER ;