数据库脚本参考

• 存储过程模版

  • 此模版适用于大多数应用存储过程的情况, 且支持命令行执行

    SP模版

    /*

    ** SAAS-0000

    ** by

    ** Description:

    */

    DROP PROCEDURE IF EXISTS `example_procedure`;

    DELIMITER //

    CREATE PROCEDURE `example_procedure`(

      given_integer BIGINT(20),

      given_string VARCHAR(50) CHARSET 'utf8',

      given_byte TINYINT(4)

    )

    BEGIN

       

      ...

       

    END;//

    DELIMITER ;

    CALL `example_procedure`(10000, '使用CHARSET', 0);

    CALL `example_procedure`(10001, '支持中文', 1);

    DROP PROCEDURE IF EXISTS `example_procedure`;

• 存储过程的游标

  • SP模版

    declare var_done int default 0;

    declare var_number bigint(20);

    declare cur cursor for

      select 1;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_done = 1;

    open cur;

    fetch cur into var_number;

    cur_loop:

      while var_done=0 do

     

        if var_number is null then

        leave cur_loop;

        end if;

         

        ...

         

        fetch cur into var_number;

        

      end whild cur_loop;

    close cur;

• 存储过程游标模版

  • SP模版

    /*

    ** SAAS-0000

    ** by

    ** Description:

    */

    DROP PROCEDURE IF EXISTS `example_procedure`;

    DELIMITER //

    CREATE PROCEDURE `example_procedure`()

    BEGIN

        

      declare var_done int default 0;

      declare var_number bigint(20);

      declare cur cursor for

        select 1;

      DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_done = 1;

      open cur;

      fetch cur into var_number;

      cur_loop:

        while var_done=0 do

       

          if var_number is null then

          leave cur_loop;

          end if;

           

          ...

           

          fetch cur into var_number;

           

        end while cur_loop;

      close cur;

    END;//

    DELIMITER ;

    CALL `example_procedure`();

    DROP PROCEDURE IF EXISTS `example_procedure`;

• 拼语句并执行

  • SET @sqlstmt = CONCAT('CREATE INDEX ', given_index, ' ON ', databaseName, '.', given_table, ' (',given_columns,')');

    PREPARE st FROM @sqlstmt;

    EXECUTE st;

    DEALLOCATE PREPARE st;

• 如果不存在则插入

  • 如果不存在`column1` = 'value1'的记录, 就插入

    INSERT IF NOT EXISTS

    INSERT INTO `table_name` (`column1`, `column2`, `column3`) 

    SELECT 'value1', 'value2', 'value3'

    FROM dual

    WHERE NOT EXISTS(SELECT * FROM `table_name` WHERE `column1` = 'value1' );

• 无则插入, 有则更新

  • 如果不存在`column1` = 'value1'的记录, 就插入, 然后更新记录`column1` = 'value1'的值

    INSERT OR UPDATE

    UPDATE `table_name` SET `column2`='value2', `column3`='value3', `column4`='value4' WHERE `column1` = 'value1';

    INSERT INTO `table_name`(`column2`, `column3`, `column4`)

    SELECT 'value2', 'value3', 'value4'

    FROM dual

    WHERE NOT EXISTS(SELECT * FROM `table_name` WHERE `column1` = 'value1');

• insert on duplicate key update: 如果键重复则更新, 不重复则插入

  • 需要唯一约束支持

    INSERT ON DUPLICATE KEY UPDATE

    INSERT INTO `table_name` (`column1`,`column2`,`column3`)

    VALUES ('value1', 'value2, 'value3')

    ON DUPLICATE KEY UPDATE `column1`=VALUES(`column1`),`column2`=VALUES(`column2`),`column3`=VALUES(`column3`);

• 判断table是否存在

  • 仅能用于存储过程中

    if table exists

    DECLARE tableIsThere INTEGER;

    DECLARE databaseName VARCHAR(20);

      

    SET databaseName='elearning';

    IF @MLN_DB_NAME IS NOT NULL THEN

      SET databaseName=@MLN_DB_NAME;

    END IF;

      

    SELECT COUNT(1) INTO tableIsThere

    FROM information_schema.`TABLES`

    WHERE TABLE_SCHEMA = databaseName

    AND   table_name = 'account';

      

    IF tableIsThere = 0 THEN

      ...

    END IF;

• 判断index是否存在

  • 仅能用于存储过程中

    if index exists

    DECLARE indexIsThere INTEGER;

    DECLARE databaseName VARCHAR(20);

      

    SET databaseName='elearning';

    IF @MLN_DB_NAME IS NOT NULL THEN

      SET databaseName=@MLN_DB_NAME;

    END IF;

      

    SELECT COUNT(1) INTO indexIsThere

    FROM INFORMATION_SCHEMA.STATISTICS

    WHERE table_schema = databaseName

    AND   table_name = 'given_table_name'

    AND   index_name = 'given_index_name';

     

    IF indexIsThere = 0 THEN

      ...

    END IF;

• 判断column是否存在

  • 仅能用于存储过程中

    if column exists

    DECLARE columnIsThere INTEGER;

    DECLARE databaseName VARCHAR(20);

      

    SET databaseName='elearning';

    IF @MLN_DB_NAME IS NOT NULL THEN

      SET databaseName=@MLN_DB_NAME;

    END IF;

      

    SELECT COUNT(1) INTO columnIsThere

    FROM INFORMATION_SCHEMA.COLUMNS

    WHERE table_schema = databaseName

    AND   table_name = 'given_table_name'

    AND   column_name = 'given_column_name';

     

    IF columnIsThere = 0 THEN

      ...

    END IF;

    
    

• select exists(): 判断是否存在

  • 有则返回1, 无则返回0

    SELECT EXISTS

    SELECT EXISTS(SELECT 1 FROM `table_name` WHERE ...);

• 根据account_id或者staff_id获取user.id

  • a

    DROP FUNCTION IF EXISTS `getUserIdByAccountId`;

    CREATE FUNCTION `getUserIdByAccountId` (

      `given_account_id` BIGINT(20)

    )

    RETURNS BIGINT(20)

    BEGIN

      DECLARE `userId` BIGINT(20);

       

      SELECT s.sso_account_id

      INTO userId

      FROM account a

      INNER JOIN staff s ON s.id = a.staff_id

      WHERE a.id = `given_account_id`;

       

      IF userId IS NULL THEN

        SET userId = 0;

      END IF;

       

      RETURN userId;

    END;

    DROP FUNCTION IF EXISTS `getUserIdByStaffId`;

    CREATE FUNCTION getUserIdByStaffId (

      `given_staff_id` BIGINT(20)

    )

    RETURNS BIGINT(20)

    BEGIN

      DECLARE `userId` BIGINT(20);

       

      SELECT s.sso_account_id

      INTO userId

      FROM staff s

      WHERE s.id = `given_staff_id`;

       

      IF userId IS NULL THEN

        SET userId = 0;

      END IF;

       

      RETURN userId;

    END;