1.
create procedure sp()
begin if exists(select 1 information_schema.TABLES where table_schema='test' and table_name='test') then insert into test values(1); end if; end;
------------------------------------------
2.
可以用IF EXISTS 选项 ;
drop table 表名 if exists 表名;
SHOW TABLES LIKE 'TB1' ;
SELECT table_name FROM information_schema.TABLES WHERE table_name ='yourname';
------------------------------------------
3.
DROP PROCEDURE IF EXISTS mydb.Clear_DB;
CREATE PROCEDURE mydb.`Clear_DB`( ) BEGIN DECLARE done INT DEFAULT 0; #游标的标志位 DECLARE a varchar(20); DECLARE b varchar(20); DECLARE cur1 CURSOR FOR SELECT table_name FROM information_schema.TABLES WHERE table_name like 'http_200909%' ; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; OPEN cur1; REPEAT FETCH cur1 INTO a; IF NOT done THEN set b=concat(b,'select * from ',a,' where name=http://scholar.google.com.hk union all'); # 拼 命令 END IF; UNTIL done END REPEAT; CLOSE cur1; SET @E=mid(b,1,length(b)-9); # 去掉最后一个UNION ALL PREPARE stmt1 FROM @E; EXECUTE stmt1; # 执行命令 DEALLOCATE PREPARE stmt1; #释放对象 END; --------------------------------------
5. ×××××××××××××××××××有用的×××××××××××××××××××××
如何判断mysql数据库中是否存在某张表方法1、DROP TABLE IF EXISTS tablename; 方法2、做一个sql查询,比如:select * from tablename/select count(*) from tablename,如果返回值为空,则表不存在。 方法3、查询:SHOW TABLES LIKE table1,判断返回值。如: if(mysql_num_rows(mysql_query("SHOW TABLES LIKE '".$table."'")==1) { echo "Table exists"; } else { echo "Table does not exist"; } 方法4、CREATE TABLE IF NOT EXISTS `yourdb`.`yourtable` (...)
drop database if exists db_name;
(2) 判断数据表存在, 则删除:
drop table if exists table_name;
|