SQL提交规范

mingzaily / 2022-02-18

规范

插入数据不可重复，使用 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 ;