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');