MYSQL判断不存在时创建表或创建数据库
Create Database If Not Exists test Character Set UTF8;
mysql判断检查表存不存在
select count(1) from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA`='test' and `TABLE_NAME`='a' ;
drop table if exists a;
CREATE TABLE if not exists `a` (
`id` varchar(32) DEFAULT NULL,
`name` varchar(10) DEFAULT NULL,
`pwd` varchar(10) DEFAULT NULL,
`birthday` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE
IF NOT EXISTS `test`.`b` (
`ID` BIGINT (8) UNSIGNED PRIMARY KEY Auto_Increment,
`Name` text,
`Birthday` DateTime
);
truncate TABLE test.a;--清空表中的数据
SHOW TABLES LIKE '%a%';
mysql判断字段存不存在
SELECT count(1) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'a' AND column_name = 'name';
SELECT count(1) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='test' AND table_name='A' AND COLUMN_NAME='name';
-- 存储过程中判断
IF EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='test' AND table_name='A' AND COLUMN_NAME='name') THEN
ALTER TABLE A drop column `name`;
END IF;
IF NOT EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='test' AND table_name='A' AND COLUMN_NAME='name') THEN
ALTER TABLE A ADD `name` VARCHAR(10) NOT NULL;
END IF;
mysql判断索引存在时删除索引的方法
mysql的drop index语句不支持if exists条件，在sql中先删除索引，再创建索引，如果对于新建的数据库，库中没有该索引，就会报错，导致后面的sql不再执行。
因此需要使用存储过程来判断索引是否存在，如果存在则删除。
DROP PROCEDURE IF EXISTS del_idx;
CREATE PROCEDURE del_idx (
IN p_tablename VARCHAR (200),
IN p_idxname VARCHAR (200)
)
BEGIN
DECLARE str VARCHAR (250);
SET @str = concat(
' drop index ',
p_idxname,
' on ',
p_tablename
);
SELECT
count(*) INTO @cnt
FROM
information_schema.statistics
WHERE
table_name = p_tablename
AND index_name = p_idxname;
IF @cnt > 0 THEN
PREPARE stmt
FROM
@str;
EXECUTE stmt;
END
IF;
END;
call del_idx('a','idx_name_birthday');
ALTER TABLE a ADD INDEX idx_name_birthday (name, birthday);
select count(1) from information_schema.statistics where table_name='a' and index_name='idx_name_birthday';
插入表记录前对记录进行检查
INSERT INTO test.a (id, `name`, pwd)
SELECT REPLACE(UUID(),'-','') as id, 'IBM', '123' FROM dual
WHERE not exists (select 1 from test.a where `name` = 'IBM');
