淡泊以明志。宁静而致远。——《三国演义·第三十七回》
规范
插入数据不可重复,使用
REPLACE INTO
替代INSERT INTO
REPLACE INTO tableName(columnName, ...) VALUES(...)
添加表时,使用
IF NOT EXISTS
CREATE TABLE IF NOT EXISTS XXX
删除表时,可以使用
IF EXISTS
DROP TABLE IF EXISTS XXX
管理字段和索引时,使用存储过程
CALL AddColumnIfNotExists ('ztc_room', 'introduction', 'VARCHAR(1000) NOT NULL DEFAULT \'\' COMMENT \'房源介绍\'');
常用存储过程
过程名 | 含义 |
---|---|
AddColumnIfNotExists | 添加字段(表名,字段名,字段描述) |
UpdateColumnIfExists | 更新字段(表名,字段名,字段描述) |
DropColumnIfExists | 删除字段(表名,字段名) |
CreateIndexIfNotExists | 添加普通索引(表名,字段名) |
CreateUniqueIndexIfNotExists | 添加唯一索引(表名,字段名) |
CreateIndexIfNotExistsWithColumns | 添加组合普通索引(表名,索引名,字段名) |
CreateUniqueIndexIfNotExistsWithColumns | 添加组合唯一索引(表名,索引名,字段名) |
DropIndexIfExists | 删除索引(表名,索引名) |
DELIMITER $$
# AddColumnIfNotExists 添加字段
DROP
PROCEDURE IF EXISTS AddColumnIfNotExists$$
CREATE PROCEDURE `AddColumnIfNotExists`(
IN tableName varchar(100), IN columnName varchar(100),
IN dbType varchar(100))
BEGIN
DECLARE _tableCount INT;
DECLARE _columnCount INT;
SET
_tableCount = (
SELECT COUNT(1)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = (
SELECT SCHEMA(
)
)
AND TABLE_NAME = tableName);
SET
_columnCount = (
SELECT COUNT(1)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = (
SELECT SCHEMA(
)
)
AND TABLE_NAME = tableName
AND COLUMN_NAME = columnName);
IF _tableCount = 1
AND _columnCount = 0
THEN
SET
@_sqlText = CONCAT(' ALTER TABLE `',
tableName, '` ADD COLUMN `',
columnName, '` ',
dbType);
PREPARE stmt1
FROM
@_sqlText;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END IF;
END$$
# UpdateColumnIfExists 更新字段
DROP
PROCEDURE IF EXISTS UpdateColumnIfExists$$
CREATE PROCEDURE `UpdateColumnIfExists`(
IN tableName varchar(100), IN columnName varchar(100),
IN dbType varchar(100))
BEGIN
DECLARE _columnCount INT;
SET
_columnCount = (
SELECT COUNT(1)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = (
SELECT SCHEMA(
)
)
AND TABLE_NAME = tableName
AND COLUMN_NAME = columnName);
IF
_columnCount = 1 THEN
SET
@_sqlText = CONCAT(' ALTER TABLE `',
tableName, '` MODIFY COLUMN `',
columnName, '` ',
dbType
);
PREPARE stmt1
FROM
@_sqlText;
EXECUTE stmt1; DEALLOCATE PREPARE stmt1;
END IF;
END$$
# DropColumnIfExists 删除字段
DROP
PROCEDURE IF EXISTS DropColumnIfExists$$
CREATE PROCEDURE `DropColumnIfExists`(
IN tableName varchar(100), IN columnName varchar(100))
BEGIN
DECLARE _columnCount INT;
SET
_columnCount = (
SELECT COUNT(1)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = (
SELECT SCHEMA(
)
)
AND TABLE_NAME = tableName
AND COLUMN_NAME = columnName);
IF _columnCount = 1
THEN
SET
@_sqlText = CONCAT(' ALTER TABLE ',
tableName, ' DROP COLUMN ',
columnName, ' ;'
);
PREPARE stmt1
FROM
@_sqlText;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END IF;
END$$
# CreateIndexIfNotExists 添加普通索引
DROP
PROCEDURE IF EXISTS CreateIndexIfNotExists$$
CREATE PROCEDURE `CreateIndexIfNotExists`(
IN tableName varchar(100), IN columnName varchar(100))
BEGIN
DECLARE _tableCount INT;
DECLARE _indexCount INT;
SET
_tableCount = (
SELECT COUNT(1)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = (
SELECT SCHEMA(
)
)
AND TABLE_NAME = tableName);
SET
_indexCount = (
SELECT COUNT(1)
FROM information_schema.statistics
WHERE TABLE_SCHEMA = (
SELECT SCHEMA(
)
)
AND TABLE_NAME = tableName
AND INDEX_NAME = CONCAT('IX_',
columnName)
);
IF _tableCount = 1
AND _indexCount = 0
THEN
SET
@_sqlText = CONCAT(' CREATE INDEX `IX_',
columnName, '` ON `',
tableName, '`(`',
columnName, '` ASC);'
);
PREPARE stmt1
FROM
@_sqlText;
EXECUTE stmt1; DEALLOCATE PREPARE stmt1;
END IF;
END$$
# CreateUniqueIndexIfNotExists 添加唯一索引
DROP
PROCEDURE IF EXISTS CreateUniqueIndexIfNotExists$$
CREATE PROCEDURE `CreateUniqueIndexIfNotExists`(
IN tableName varchar(100), IN columnName varchar(100))
BEGIN
DECLARE _tableCount INT;
DECLARE _indexCount INT;
SET
_tableCount = (
SELECT COUNT(1)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = (
SELECT SCHEMA(
)
)
AND TABLE_NAME = tableName);
SET
_indexCount = (
SELECT COUNT(1)
FROM information_schema.statistics
WHERE TABLE_SCHEMA = (
SELECT SCHEMA(
)
)
AND TABLE_NAME = tableName
AND INDEX_NAME = CONCAT('IX_',
columnName)
);
IF _tableCount = 1
AND _indexCount = 0
THEN
SET
@_sqlText = CONCAT(' CREATE UNIQUE INDEX `IX_',
columnName, '` ON `',
tableName, '`(`',
columnName, '` ASC);'
);
PREPARE stmt1
FROM
@_sqlText;
EXECUTE stmt1; DEALLOCATE PREPARE stmt1;
END IF;
END$$
# CreateIndexIfNotExistsWithColumns 添加组合普通索引
DROP
PROCEDURE IF EXISTS CreateIndexIfNotExistsWithColumns$$
CREATE PROCEDURE `CreateIndexIfNotExistsWithColumns`(
IN tableName varchar(200), IN indexName VARCHAR(200),
IN columnName VARCHAR(200))
BEGIN
DECLARE _tableCount INT;
DECLARE _indexCount INT;
SET
_tableCount = (
SELECT COUNT(1)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = (
SELECT SCHEMA(
)
)
AND TABLE_NAME = tableName);
SET
_indexCount = (
SELECT COUNT(1)
FROM information_schema.statistics
WHERE TABLE_SCHEMA = (
SELECT SCHEMA(
)
)
AND TABLE_NAME = tableName
AND INDEX_NAME = CONCAT('IX_',
indexName)
);
IF _tableCount = 1
AND _indexCount = 0
THEN
SET
@_sqlText = CONCAT(' CREATE INDEX `IX_',
indexName, '` ON `',
tableName, '`(',
columnName, ');
');
PREPARE stmt1
FROM
@_sqlText;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END IF;
END$$
# CreateUniqueIndexIfNotExistsWithColumns 添加组合唯一索引
DROP
PROCEDURE IF EXISTS CreateUniqueIndexIfNotExistsWithColumns$$
CREATE PROCEDURE `CreateUniqueIndexIfNotExistsWithColumns`(
IN tableName VARCHAR(200), IN indexName VARCHAR(200),
IN columnName VARCHAR(200))
BEGIN
DECLARE _tableCount INT;
DECLARE _indexCount INT;
SET
_tableCount = (
SELECT COUNT(1)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = (
SELECT SCHEMA(
)
)
AND TABLE_NAME = tableName);
SET
_indexCount = (
SELECT COUNT(1)
FROM information_schema.statistics
WHERE TABLE_SCHEMA = (
SELECT SCHEMA(
)
)
AND TABLE_NAME = tableName
AND INDEX_NAME = CONCAT('IX_',
indexName)
);
IF _tableCount = 1
AND _indexCount = 0
THEN
SET
@_sqlText = CONCAT(' CREATE UNIQUE INDEX `IX_',
indexName, '` ON `',
tableName, '`(',
columnName, ');
');
PREPARE stmt1
FROM
@_sqlText;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END IF;
END$$
# DropIndexIfExists 删除索引
DROP
PROCEDURE IF EXISTS DropIndexIfExists$$
CREATE PROCEDURE `DropIndexIfExists`(
IN tableName varchar(100), IN indexName varchar(100))
BEGIN
DECLARE _indexCount INT;
SET
_indexCount = (
SELECT COUNT(1)
FROM information_schema.statistics
WHERE TABLE_SCHEMA = (
SELECT SCHEMA(
)
)
AND TABLE_NAME = tableName
AND INDEX_NAME = CONCAT('IX_',
indexName)
);
IF _indexCount > 0
THEN
SET
@_sqlText = CONCAT(' DROP INDEX `IX_',
indexName, '` ON `',
tableName, '`; '
);
PREPARE stmt1
FROM
@_sqlText;
EXECUTE stmt1; DEALLOCATE PREPARE stmt1;
END IF;
END$$
DELIMITER ;