未来世界
代码

SQL提交规范

by Yuming, 2022-02-18


规范

  1. 插入数据不可重复，使用 REPLACE INTO 替代 INSERT INTO

    REPLACE INTO tableName(columnName, ...) VALUES(...)

  2. 添加表时，使用 IF NOT EXISTS

    CREATE TABLE IF NOT EXISTS XXX

  3. 删除表时，可以使用 IF EXISTS

    DROP TABLE IF EXISTS XXX

  4. 管理字段和索引时，使用存储过程

    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 ;

作者： Yuming

分类目录
一言一句

淡泊以明志。宁静而致远。——《三国演义·第三十七回》

标签
面经 数据库 Go PHP 面试
归档
粤ICP备19153093号
2023 © typecho & elise