MySql 小节

1.

UNION 用于合并两个SQL语句查询的结果

合并的两个SQL结果中必须字段个数和名字一致（可以用伪名）

需要注意的是合并后的结果是没有重复记录的，相当于用了distingct

如果想得到全部的结果可以用UNION ALL语句

2.

模仿split的存储过程

DELIMITER $$

DROP PROCEDURE IF EXISTS `proc_split`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_split`(
    inputstring VARCHAR(1000),
    delim CHAR(1)
)
BEGIN
    DECLARE strlen INT;
    DECLARE last_index INT;
    DECLARE cur_index INT;
    DECLARE cur_char VARCHAR(200);
    DECLARE len INT;
    SET cur_index=1;
    SET last_index=0;
    SET strlen=LENGTH(inputstring); 
    WHILE(cur_index<=strlen) DO   
    BEGIN
        IF SUBSTRING(inputstring FROM cur_index FOR 1)=delim OR cur_index=strlen THEN
            SET len=cur_index-last_index-1;
            IF cur_index=strlen THEN
               SET len=len+1;
            END IF;
           
            set did =  SUBSTRING(inputstring FROM (last_index+1) FOR len);
           
           
            SET last_index=cur_index;
        END IF;
        SET cur_index=cur_index+1;
    END;
    END WHILE;
END$$

DELIMITER ;

CALL proc_split("'da','db','dc'",',');

3.in的效率问题

          --a  DELETE t1 FROM rls_system_area t1 INNER JOIN rls_release t2 ON t1.system_id = t2.system_id AND t1.release_id = t2.release_id
                WHERE t2.system_id = prm_system_id AND system_area_cd <> 700 AND t2.demand_id = tmp_demand_id;

          --b DELETE FROM rls_system_area
                 WHERE 'release_id' IN (SELECT release_id FROM rls_release t2 WHERE t2.system_id = prm_system_id AND t2.demand_id = demand_id)
                 AND system_area_cd <> '700' AND system_id = prm_system_id

rls_system_area 40000条数据

rls_release 20000条数据

1) a的效率要比b的效率高很多

2)消除数据关联表的情况下 要加消除的表名或伪名:DELETE t1 FROM rls_system_area t1 INNER JOIN rls_release t2 ........