数据库脚本参考

• 存储过程模版

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

    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 ;