select count(1) from information_schema.tables where TABLE_SCHEMA='kungfu' and table_name=p_tablename into @cnt; #select p_tablename; if @cnt != 0 then set @ISExist=1;
else set @ISExist=0; end if; select @ISExist; END;@cnt总是为0,但是其实临时表是存在的 这是调用语句 :call fine_tmp('tmp_123',@cnt)
我要现实的功能是检查某一个临时表是否存在,如果不存在,就创建一个,并且返回0,如果已经存在就直接返回1,我是这样显示的,但是临时@cnt一直都是0,其实该临时表已经存在,我是在MySQL——front中写的,应该是同一个连接,但是我最终的目的是,通过临时表控制重复登陆,不同的连接不能检测到临时表码???CREATE DEFINER=`root`@`%` PROCEDURE `fine_tmp`(IN p_tablename varchar(50),OUT ISExist int) BEGIN declare cnt int default 0; declare stmt varchar(2000); select count(*) from information_schema.tables where table_name=p_tablename into @cnt; select @cnt; if @cnt != 0 then set @ISExist=1;
else set @ISExist=0; set @m_sql=concat('create TEMPORARY table ',p_tablename,'(id int(11))'); select @m_sql; prepare stmt from @m_sql; execute stmt; end if; select @ISExist; END;
不同的连接不能检测到临时表码 不能,你是要检测同一个临时表吧,是session级的。 MYSQL HELP: You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed. This means that two different connections can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporary table is dropped.)
你可以直接设置该用户账号的 MAX_USER_CONNECTIONS = 1 就可以了。 mysql> CREATE USER 'francis'@'localhost' IDENTIFIED BY 'frank'; mysql> GRANT ALL ON customer.* TO 'francis'@'localhost' -> WITH MAX_QUERIES_PER_HOUR 20 -> MAX_UPDATES_PER_HOUR 10 -> MAX_CONNECTIONS_PER_HOUR 5 -> MAX_USER_CONNECTIONS 2;
下面是我写的存储过程
CREATE DEFINER=`root`@`%` PROCEDURE `fine_tmp`(IN p_tablename varchar(50),OUT ISExist int)
BEGIN
declare cnt int;
declare stmt varchar(2000);
select count(1) from information_schema.tables where TABLE_SCHEMA='kungfu' and table_name=p_tablename into @cnt;
#select p_tablename;
if @cnt != 0 then
set @ISExist=1;
else
set @ISExist=0;
end if;
select @ISExist;
END;@cnt总是为0,但是其实临时表是存在的
这是调用语句 :call fine_tmp('tmp_123',@cnt)
CREATE TEMPORARY TABLE ....建立 的?
BEGIN
declare cnt int default 0;
declare stmt varchar(2000);
select count(*) from information_schema.tables where table_name=p_tablename into @cnt;
select @cnt;
if @cnt != 0 then
set @ISExist=1;
else
set @ISExist=0;
set @m_sql=concat('create TEMPORARY table ',p_tablename,'(id int(11))');
select @m_sql;
prepare stmt from @m_sql;
execute stmt;
end if;
select @ISExist;
END;
不能,你是要检测同一个临时表吧,是session级的。
MYSQL HELP:
You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed. This means that two different connections can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporary table is dropped.)
mysql> CREATE USER 'francis'@'localhost' IDENTIFIED BY 'frank';
mysql> GRANT ALL ON customer.* TO 'francis'@'localhost'
-> WITH MAX_QUERIES_PER_HOUR 20
-> MAX_UPDATES_PER_HOUR 10
-> MAX_CONNECTIONS_PER_HOUR 5
-> MAX_USER_CONNECTIONS 2;