mysql精华

# 动态sql
CREATE DEFINER = CURRENT_USER PROCEDURE `test`(IN USER_ID varchar(10), IN USER_NAME varchar(10))
BEGIN                                                        　　　　　　　　　　　　　　　　　　　　　　    
       set @SQL_FOR_SELECT = CONCAT("select * from  user  where userId = '",USER_ID,"' and userName = '",USER_NAME,"'");   -- 拼接查询sql语句
       PREPARE stmt FROM @SQL_FOR_SELECT;       -- 预处理动态sql语句
       EXECUTE stmt ;                           -- 执行sql语句
       deallocate prepare stmt;                 -- 释放prepare

END;


#创建临时表

DROP TEMPORARY TABLE IF EXISTS tmp_order; -- 先删一下再说，比较保险
create temporary table tmp_order(
　　id int primary key,
　　name varchar(20) 
)Engine=InnoDB default charset utf8;

CREATE TEMPORARY TABLE dw.tmp_order AS ( SELECT * FROM dw.f_gdmx_all WHERE orderno IS NULL LIMIT 1 );-- 懒人方法创建临时表不建议使用


#导出数据字典

SELECT
TABLE_SCHEMA,
TABLE_NAME AS '表名',
COLUMN_NAME AS '字段名',
COLUMN_TYPE AS '数据类型',
IS_NULLABLE AS '允许为空',
EXTRA AS '额外信息',
COLUMN_COMMENT AS '字段说明'
FROM
information_schema.`COLUMNS`
WHERE
TABLE_SCHEMA = 'api'


#关联删除

DELETE  sap_ybtx from sap_ybtx  INNER JOIN sap_ybtx_notin notin  on notin.mandt=sap_ybtx.mandt and 
notin.gjahr=sap_ybtx.gjahr and notin.buzei=sap_ybtx.buzei and notin.bukrs=sap_ybtx.bukrs and notin.belnr=sap_ybtx.belnr


#批量更新
UPDATE f_order_finish f ,orderno_cf c  set f.cf=1 where f.orderno=c.orderno


#存储过程循环处理数据
CREATE DEFINER=`dw`@`%` PROCEDURE `test`()
BEGIN
   DECLARE done INT DEFAULT FALSE;
	 DECLARE xx VARCHAR(64);
	 
    DECLARE cur_list CURSOR FOR   
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
    OPEN cur_list;
    read_loop : LOOP
        FETCH cur_list INTO xx;
        IF done THEN
            LEAVE read_loop;
        END IF;
			 
        COMMIT;
    END LOOP read_loop;
CLOSE cur_list;

#字符串分割成多行
SELECT 
  SUBSTRING_INDEX(SUBSTRING_INDEX('7654,7698,7782,7788',',',help_topic_id+1),',',-1) AS num 
FROM 
    mysql.help_topic 
WHERE 
    help_topic_id < LENGTH('7654,7698,7782,7788')-LENGTH(REPLACE('7654,7698,7782,7788',',',''))+1



 自定义函数字符串分割函数
CREATE DEFINER=`root`@`%` PROCEDURE `str_to_list`(IN str varchar(4000), IN split varchar(255))
BEGIN
    SELECT substring_index(substring_index(a.chain, split, b.help_topic_id + 1), split, - 1) AS list
    FROM (select str as chain) a
             JOIN mysql.help_topic b ON b.help_topic_id < (length(a.chain) - length(replace(a.chain, split, '')) + 1);

END


#创建内存表

CREATE TABLE `abc` ENGINE= MEMORY select * from `sap_zbkpf_bseg` limit 1;


#存储过程函数赋值

	SELECT
				ooi.orderId,
				ooi.state,
				ooi.insuranceOrderId,
				MIN(ooi.mainStartTime) AS mainStartTime, -- 生效日期 需取最早开始生效日期(主部件和非主部件的)
				ooi.mainEndTime,
				ooi.notMainStartTime,
				ooi.notMainEndTime,
				ooi.brandName
		into 	@orderId,@state,@insuranceOrderId,@mainStartTime,@mainEndTime,	@notMainStartTime,@notMainEndTime,@brandName


#实现排名

SELECT t.*, @rownum := @rownum + 1 AS rownum
FROM (SELECT @rownum := 0) r, (SELECT * FROM testsort ORDER BY score DESC) AS t;


SELECT t.*, @rownum := @rownum + 1 AS rownum
FROM (SELECT @rownum := 0) r, testsort AS t
ORDER BY t.score DESC;

#强制转类型
cast( `oyss`.`salegroupcode` AS signed ) signed 为数字


#补全前导零
CREATE DEFINER=`root`@`%` FUNCTION `firstAddZro`(str VARCHAR(10)) RETURNS varchar(10) CHARSET utf8mb4
BEGIN
	 declare result varchar(10);
	 set result=str;
		WHILE LENGTH(result)<10 DO
			set result=CONCAT('0',result);
		END WHILE;

	RETURN result;
END

 #指定索引
 select * from user force index(username_index) where  username='zhanqi'

 #存储过程开启事物(看这个注释<-- 这里>)
CREATE DEFINER=`root`@`%` PROCEDURE `test`()
BEGIN
    BEGIN
        DECLARE error TINYINT DEFAULT 1;
        DECLARE DONE INT DEFAULT FALSE;
        DECLARE field_loginName VARCHAR(50);

        DECLARE cur_list CURSOR FOR (SELECT a.loginName from asd_oa_empinfo  where a.operType <> 'delete');

        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET error = - 1; -- 这里

        START TRANSACTION; -- 这里

        OPEN cur_list;
        read_loop :
        LOOP
            FETCH cur_list INTO field_loginName;

            IF DONE THEN
                LEAVE read_loop;
            END IF;

          -- 这里
            IF error = - 1 THEN
                ROLLBACK;
            ELSE
                COMMIT;
            END IF;

        END LOOP read_loop;
        CLOSE cur_list;
        SELECT error;

    END;
END