【汇总】msyql内部函数

 

DECLARE file_path VARCHAR(100) DEFAULT '/var/lib/mysql/logs/mysqllog/';

 

SELECT f_get_close_sub_draws_time(0) INTO @intervalH_times;

IF(sub_draws_name < @now_sub_draws_name AND _do_today = 1,4,_status) AS status

SELECT SQL_NO_CACHE LAST_INSERT_ID() INTO @insert_id;

 

 

 

 

REPLACE INTO user_share_sum SELECT * FROM config_user_share_sum;

 

 

REPLACE INTO order_ued
                           (order_id, sub_draws_name, draws_date, user_id, game_id, order_time, order_amount, discount
                           , win_result, odds_set, `status`, time_stamp, win_flag, number, number_name, odds)
                     (SELECT id, sub_draws_name,DATE(order_time)+0 AS draws_date, user_id, game_id, order_time, order_amount, discount,
                         win_result, odds_set, `status`, NULL, win_flag, number, number_name, odds
                      FROM order_curr
                      );

===============转换为数字 -0

SET _where_orderid = CONCAT(' AND o.id= ',RIGHT(_order_id,6)-0);

 

======================= 转义


SET _where = CONCAT('draws_name =\'',_draws_name,'\'');

 

===================== 定义处理程序

格式：
    DECLARE handler_type HANDLER FOR
    condition_value[,...] sp_statement 
   
   
    handler_type: 
        CONTINUE | EXIT | UNDO 
       
       
    condition_value: 
        SQLSTATE [VALUE] sqlstate_value |condition_name  | SQLWARNING  | NOT FOUND  | SQLEXCEPTION  | mysql_error_code

其中，handler_type参数指明错误的处理方式，该参数有3个取值。这3个取值分别是CONTINUE、EXIT和UNDO。CONTINUE表示遇到错误不进行处理，继续向下执行；EXIT表示遇到错误后马上退出；UNDO表示遇到错误后撤回之前的操作，MySQL中暂时还不支持这种处理方式。


DROP PROCEDURE IF EXISTS `p_write_log`$$
CREATE  PROCEDURE `p_write_log`(IN _file_name VARCHAR(200),OUT result_value INT)
BEGIN


    DECLARE EXIT HANDLER FOR NOT FOUND
    BEGIN
        SET result_value = 0;
    END;

   
    DECLARE EXIT HANDLER FOR SQLWARNING
    BEGIN
        SET result_value = 0;
    END;

   
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SET result_value = 0;
    END;
END$$

 

======================= LAST_INSERT_ID();

INSERT INTO operation_log(user_account
                              ,sub_draws_name
                              ,draws_date
                              ,custom_game_id
                              ,operation_type
                              ,log_time
                              ,ip)
                       VALUES(_user_account
                              ,_draw_name
                              ,_draw_date
                              ,_game_id
                              ,_operation_type
                              ,NOW()
                              ,inet_aton(_ip));

 SET _last_insert_id = LAST_INSERT_ID();
       
       
======================ROW_COUNT() 返回影响的行数

UPDATE sub_draws SET init_status = 1 WHERE draws_name = _draws_name AND sub_draws_name = _sub_draws_name;
SET _query_rows = ROW_COUNT();
 IF _query_rows > 0 THEN

 

 

 

 

==================== SELECT ... INTO  多个值


SELECT SQL_NO_CACHE id,sub_draws_name,draws_name,start_time,end_time,status,init_status INTO _sub_draws_id,_sub_draws_name,_draws_name,_start_time,_end_time,_status,_init_status FROM sub_draws WHERE draws_name = _draws_name AND sub_draws_name = _sub_draws_name;

SELECT SQL_NO_CACHE IFNULL(order_id,0) INTO @max_deal_order_id FROM order_deal WHERE `type`=0;

 

==================== 格式化赋值

    SET @status = _status;
    SET @v_sql= CONCAT('UPDATE ',order_tb,' SET status = ?,order_amount = order_amount*IF(status<3,1,-1)
                        WHERE id=?;');
    PREPARE stmt FROM @v_sql;
    EXECUTE stmt USING @status,@order_id;
    DEALLOCATE PREPARE stmt;

 

 

====================  if  elseif  elseif  end if

 

mysql> select if(tim="1","mon","aft") from t1;

 

 

IF _opt_type = 'add' THEN
        ...
ELSEIF _opt_type = 'upd' THEN
        ...
   
ELSEIF _opt_type = 'del' THEN
    ...
   
END IF;

 

 

 

IF 0=IS_NEW AND (
            EXISTS(SELECT 1 FROM order_curr WHERE win_flag=-1 LIMIT 1) OR
            EXISTS(SELECT 1 FROM short_covering_user WHERE win_flag=-1 LIMIT 1) OR
            EXISTS(SELECT 1 FROM short_covering_corp WHERE win_flag=-1 LIMIT 1)
        ) THEN
            SET IS_NEW = 1;
        END IF;



IF EXISTS ( SELECT 1 FROM short_covering_corp WHERE sub_draws_name=_sub_draws_name AND STATUS=5 LIMIT 1) THEN
        ...
end if

 

 

==================== WHILE

WHILE _cate_id <= 21 DO
 
    CALL p_serve(_cate_id,@result);
   
    SET _cate_id=_cate_id+1;
END WHILE;

 

 

==================== on duplicate key update

假如有一张书目表，字段为id，name，count（库存）
现要插入一条新纪录，如果数据库没有这条纪录，插入
若已经存在，则更新库存。
解决方法：mysql 的 on duplicate key update 语法。

 

 

INSERT INTO draws_result
               (drs_date, number1, number2, `status` )
VALUES (_drs_date, _number1, _number2, _status)
ON DUPLICATE KEY UPDATE
            number1 = _number1,
            number2 = _number2
            `status` = _status;

 

 

INSERT INTO item_betting_sum(user_id
                                 ,game_id
                                 ,number
                                 ,item_sum)
VALUES(_member_id
                                 ,_game_id
                                 ,_number
                                 ,_order_amount
                                )
 ON DUPLICATE KEY UPDATE item_sum = item_sum + _order_amount;

 

 

 

 

==================== lpad rpad 字符串左右填充


lpad  函数从左边对字符串使用指定的字符进行填充。从其字面意思也可以理解，l是left的简写，pad是填充的意思，所以lpad就是从左边填充的意思。

　　lpad( string, padded_length, [ pad_string ] )
　　string 准备被填充的字符串；
    padded_length 填充之后的字符串长度，也就是该函数返回的字符串长度，如果这个数量比原字符串的长度要短，lpad函数将会把字符串截取成从左到右的n个字符;
    pad_string 填充字符串，是个可选参数，这个字符串是要粘贴到string的左边，如果这个参数未写，lpad函数将会在string的左边粘贴空格。
 
　　示例1：
　　SQL> select lpad('abcde',10,'x') from dual;
　　xxxxxabcde

以0填充game_id，得到结果为3位：

SET __game_id = LPAD(_game_id,3,'0');

 

==================== WHILE 循环

    SET _num = 1;
    WHILE _num <= 3 DO
        SET _cur_odds_set = ELT(_num,'A','B','C');
        IF _os_set != _cur_os_set THEN
            。。。
        END IF;
        SET _num = _num + 1;       
    END WHILE;

 

==================== LEAVE 语句退出循环

    IF _last_jump_odds = 0 THEN
        LEAVE L_END;
    END IF;



CREATE PROCEDURE myProc()
 BEGIN
     DECLARE i,j INT DEFAULT 1;
     outer_loop: LOOP
          SET j=1;
          inner_loop: LOOP
               SELECT CONCAT(i," times ", j," is ",i*j);
               SET j=j+1;
               IF j>12 THEN
                    LEAVE inner_loop;
               END IF;
          END LOOP inner_loop;
          SET i=i+1;
          IF i>12 THEN
               LEAVE outer_loop;
          END IF;
     END LOOP outer_loop;
 END$$

 

 

 

==================== FLOOR (X) 返回不大于X 的最大整数值。

    mysql> select FLOOR(1.23);
            -> 1
    mysql> select FLOOR(-1.23);
            -> -2

    注意返回值被变换为一个BIGINT
   
   

==================== CEILING (X) 返回不小于X 的最小整数值。

    mysql> select CEILING(1.23);
            -> 2
    mysql> select CEILING(-1.23);
            -> -1

    注意返回值被变换为一个BIGINT ！
   
==================== ROUND (X) 返回参数X 的四舍五入的一个整数。

    mysql> select ROUND(-1.23);
            -> -1
    mysql> select ROUND(-1.58);
            -> -2
    mysql> select ROUND(1.58);
            -> 2
    

    注意返回值被变换为一个BIGINT !
   
==================== ROUND(X,D)
    返回参数X 的四舍五入的有D 为小数的一个数字。如果D 为0 ，结果将没有小数点或小数部分。

    mysql> select ROUND(1.298, 1);
            -> 1.3
    mysql> select ROUND(1.298, 0);
            -> 1

    注意返回值被变换为一个BIGINT !

 

 

 

 

====================  GROUP_CONCAT



GROUP_CONCAT()是MySQL数据库提供的一个函数，通常跟GROUPBY一起用

语法:
GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])



1.例如:
SELECT student_id, GROUP_CONCAT(courses_id) AS courses FROM student_courses WHERE student_id=2 GROUP BY student_id;

+------------+---------+
| student_id | courses |
+------------+---------+
|    2       | 3,4,5   |
 +------------+---------+
 
 
2.当然分隔符还可以自定义，默认是以“,”作为分隔符，若要改为“|||”，则使用SEPARATOR来指定，例如：

SELECT student_id, GROUP_CONCAT(courses_id SEPARATOR '|||') AS courses FROM student_courses WHERE student_id=2 GROUP BY student_id;

+------------+---------+
| student_id | courses |
+------------+---------+
|   2        | 3|||4|||5   
 +------------+---------+


3.除此之外，还可以对这个组的值来进行排序再连接成字符串，例如按courses_id降序来排：

SELECT student_id, GROUP_CONCAT(courses_id ORDER BY courses_id DESC) AS courses FROM student_courses WHERE student_id=2 GROUP BY student_id;

+------------+---------+
| student_id | courses |
+------------+---------+
|       2    | 5,4,3  
 +-----------+---------+


4.需要注意的：

a.int字段的连接陷阱

当你用group_concat的时候请注意，连接起来的字段如果是int型，一定要转换成char再拼起来，
否则在你执行后(ExecuteScalar或者其它任何执行SQL返回结果的方法)返回的将不是一个逗号隔开的串，
而是byte[]。

该问题当你在SQLyog等一些工具中是体现不出来的，所以很难发现。

select group_concat(ipaddress) from t_ip 返回逗号隔开的串
select group_concat(id) from t_ip 返回byte[]
select group_concat(CAST(id as char)) from t_dep 返回逗号隔开的串
select group_concat(Convert(id , char)) from t_dep 返回逗号隔开的串

附Cast,convert的用法：
CAST(expr AS type), CONVERT(expr,type) , CONVERT(expr USING transcoding_name)
CAST() 和CONVERT() 函数可用来获取一个类型的值，并产生另一个类型的值。

这个类型 可以是以下值其中的 一个：

BINARY[(N)]
CHAR[(N)]
DATE
DATETIME
DECIMAL
SIGNED [INTEGER]
TIME
UNSIGNED [INTEGER]

b.长度陷阱
用了group_concat后，select里如果使用了limit是不起作用的.
用group_concat连接字段的时候是有长度限制的，并不是有多少连多少。但你可以设置一下。

使用group_concat_max_len系统变量，你可以设置允许的最大长度。
程序中进行这项操作的语法如下，其中 val 是一个无符号整数：
SET [SESSION | GLOBAL] group_concat_max_len = val;
若已经设置了最大长度，则结果被截至这个最大长度。

在SQLyog中执行 SET GLOBAL group_concat_max_len = 10 后，重新打开SQLyog，设置就会生效。

 

====================  insert 和 INSERT IGNORE

 

#select表数据插入到另一个表

 

INSERT tmp_gamedata(user_id,sort_flag)
(
SELECT user_id,IF(leaf_user=user_id,IF(4=level,1,2),1) AS sort_flag
    FROM tmp_orderwinloss
    GROUP BY user_id,sort_flag
);

 

NSERT IGNORE会忽略数据库中已经存在 的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。

 

INSERT IGNORE INTO re_user(user_id,game_id,number)
 (SELECT user_id,@game_id,@number FROM share_sum WHERE member_id = @member_id AND `level` <> 5
);

 

 

 

====================  IF     ELSEIF     ELSE      END IF

 

    IF 'B' = LEFT(_order_id,1) THEN
        ...
    ELSEIF 'Y' = LEFT(_order_id,1) THEN
        ...
    ELSE
        ...
    END IF;

 

====================  CONVERT


SELECT sss_set,CONVERT(game_id,CHAR ) AS game_id,number

 

 

==================== CASE

第一种是CASE 后面不带表达式的；
CASE WHEN expression THEN 操作1
          WHEN expression THEN 操作2
           .......
          ELSE 操作n
END

第二种是CASE 后面带表达式的(而此时WHEN 后面的则是该表达式可能的值)
通用。
CASE expression
    WHEN  expression值1 THEN  操作1
    WHEN  expression值2 THEN 操作2
                .......
                ELSE 操作n
END


SELECT od.order_id,od.win_flag ,(CASE od.win_flag WHEN 0 THEN 0 WHEN 1 THEN od.odds ELSE 1 END) AS d_odds

 

 

 

mysql> select case tim
  -> when "1" then "mon"
  -> when "2" then "aft"
  -> end
  -> as tim
  -> from t1;

 

 

 

 

====================字符串截取函数：left(), right(), substring(), substring_index()。还有 mid(), substr()




1. 字符串截取：left(str, length)

mysql> select left('sqlstudy.com', 3);
+-------------------------+
| left('sqlstudy.com', 3) |
+-------------------------+
| sql                     |
+-------------------------+

2. 字符串截取：right(str, length)

mysql> select right('sqlstudy.com', 3);
+--------------------------+
| right('sqlstudy.com', 3) |
+--------------------------+
| com                      |
+--------------------------+

3. 字符串截取：substring(str, pos); substring(str, pos, len)

3.1 从字符串的第 4 个字符位置开始取，直到结束。

mysql> select substring('sqlstudy.com', 4);
+------------------------------+
| substring('sqlstudy.com', 4) |
+------------------------------+
| study.com                    |
+------------------------------+

3.2 从字符串的第 4 个字符位置开始取，只取 2 个字符。

mysql> select substring('sqlstudy.com', 4, 2);
+---------------------------------+
| substring('sqlstudy.com', 4, 2) |
+---------------------------------+
| st                              |
+---------------------------------+

3.3 从字符串的第 4 个字符位置（倒数）开始取，直到结束。

mysql> select substring('sqlstudy.com', -4);
+-------------------------------+
| substring('sqlstudy.com', -4) |
+-------------------------------+
| .com                          |
+-------------------------------+

3.4 从字符串的第 4 个字符位置（倒数）开始取，只取 2 个字符。

mysql> select substring('sqlstudy.com', -4, 2);
+----------------------------------+
| substring('sqlstudy.com', -4, 2) |
+----------------------------------+
| .c                               |
+----------------------------------+

我们注意到在函数 substring(str,pos, len)中， pos 可以是负值，但 len 不能取负值。

4. 字符串截取：substring_index(str,delim,count)

4.1 截取第二个 '.' 之前的所有字符。

mysql> select substring_index('www.sqlstudy.com.cn', '.', 2);
+------------------------------------------------+
| substring_index('www.sqlstudy.com.cn', '.', 2) |
+------------------------------------------------+
| www.sqlstudy                                   |
+------------------------------------------------+

4.2 截取第二个 '.' （倒数）之后的所有字符。

mysql> select substring_index('www.sqlstudy.com.cn', '.', -2);
+-------------------------------------------------+
| substring_index('www.sqlstudy.com.cn', '.', -2) |
+-------------------------------------------------+
| com.cn                                          |
+-------------------------------------------------+

4.3 如果在字符串中找不到 delim 参数指定的值，就返回整个字符串

mysql> select substring_index('www.sqlstudy.com.cn', '.coc', 1);
+---------------------------------------------------+
| substring_index('www.sqlstudy.com.cn', '.coc', 1) |
+---------------------------------------------------+
| www.sqlstudy.com.cn                               |
+---------------------------------------------------+

 

 

 

 

==================== SQL_NO_CACHE和SQL_CACHE

 

SQL_NO_CACHE select 禁止缓存


SELECT语句中指定查询缓存的选项，对于那些肯定要实时的从表中获取数据的查询，或者对于那些一天只执行一次的查询，我们都可以指定不进行查询缓存，使用SQL_NO_CACHE 选项。

对于那些变化不频繁的表，查询操作很固定，我们可以将该查询操作缓存起来，这样每次执行的时候不实际访问表和执行查询，只是从缓存获得结果，可以有效地改善查询的性能，使用SQL_CACHE 选项。


mysql> select sql_no_cache id,name from test3 whereid < 2;

mysql> select sql_cache id,name from test3 where id< 2;

 

 

 

 

==================== IFNULL 和 IF

 

 

IFNULL(expr1,expr2)         
如果expr1不是NULL，IFNULL()返回expr1 ，否则它返回expr2。IFNULL()返回一个数字或字符串值，取决于它被使用的上下文环境。        
mysql> select IFNULL(1,0);    
                     -> 1    
mysql> select IFNULL(0,10);    
                     -> 0    
mysql> select IFNULL(1/0,10);    
                     -> 10    
mysql> select IFNULL(1/0,yes);    
                     -> yes    


IF(exPR1,expr2,expr3)
如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL)，则 IF()的返回值为expr2; 否则返回值则为 expr3。IF() 的返回值为数字值或字符串值，具体情况视其所在语境而定。

mysql> SELECT IF(1>2,2,3);
-> 3

mysql> SELECT IF(1<2,yes ,no);
-> yes

mysql> SELECT IF(STRCMP(test,test1),no,yes);
-> no
       
       
       
       
IF(1=sort_flag,IFNULL(su.user_name,\'\') ,su.mem_name) AS parent_name

 

UPDATE latest SET ods = IF( ods - jp_ods < _min_limit,_min_limit,ods - jp_ods) WHERE ods_set = _ods_set

 

SET _ods_new = IF(change_ods_a < _min_limit,_min_limit,change_ods_a);

 

 

 

 

 

IF EXISTS( SELECT 1 FROM ds WHERE status < 3 AND ds_name < CURDATE()) THEN
        UPDATE ds SET status = 3 WHERE status < 3 AND ds_name < CURDATE();
END IF;

 

 

==================== declare 定义变量，set 为变量赋值，@同时定义和赋值

DECLARE v_index INT;
SET v_index = 0;

 

SET @v_sql ="..."

 

直接调用存储过程函数

SET _test_a = f_get_latest(_set,_game_id,_number);

 

==================== union  和union all

 

多表联合字段数必须一致，否则会提示：

The used SELECT statements have a different number of columns

 

 

 

CREATE TABLE `a` (
  `id` int(11) NOT NULL DEFAULT '0',
  `a` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
"id" "a"
"1"    "1"
"2"    "2"

CREATE TABLE `b` (
  `id` int(1) NOT NULL DEFAULT '0',
  `b` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

"id" "b"
"1"    "111"
"2"    "2222"

 

>SELECT * FROM a UNION SELECT * FROM b;

"id" "a"
"1"    "1"
"2"    "2"
"1"    "111"
"2"    "222"

 

 

UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果 。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION

这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。

 

而UNION ALL只是简单的将两个结果合并后就返回 。这样，如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。

从效率上说，UNION ALL 要比UNION快很多 ，所以，如果可以确认合并的两个结果集中不包含重复的数据的话，那么就使用UNION ALL

 

 

==================== distinct

select distinct name,age from student;

 

如果只查询一个列的话,是可以查询得到不重复的记录,但是如果要查询多列的话,就会出错,

但只要在select distinct a,b from TABLE group by a order by b 就可以实现不重复的列的查找了.

 

 

==================== concat

使用方法：

CONCAT(str1,str2,…) 

select concat('11','22','33')。

返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。

注意：
如果所有参数均为非二进制字符串，则结果为非二进制字符串。
如果自变量中含有任一二进制字符串，则结果为一个二进制字符串。
一个数字参数被转化为与之相等的二进制字符串格式；若要避免这种情况，可使用显式类型 cast, 例如：
SELECT CONCAT(CAST(int_col AS CHAR), char_col)

MySQL的concat函数可以连接一个或者多个字符串,如
mysql> select concat('10');
+--------------+
| concat('10') |
+--------------+
| 10   |
+--------------+
1 row in set (0.00 sec)

mysql> select concat('11','22','33');
+------------------------+
| concat('11','22','33') |
+------------------------+
| 112233 |
+------------------------+
1 row in set (0.00 sec)

MySQL的concat函数在连接字符串的时候，只要其中一个是NULL,那么将返回NULL
mysql> select concat('11','22',null);
+------------------------+
| concat('11','22',null) |
+------------------------+
| NULL   |
+------------------------+
1 row in set (0.00 sec)

 

 

 

#拼接可变的库名

SET @today_count = CONCAT('SELECT * AS order_counts FROM ',_db_name );

 

 

#截取后确保为数值

CONCAT(' AND o.id= ',RIGHT(_order_id,6)-0 );

 

 

 

==================== ELT(N,str1,str2,str3,...)

如果N= 1，返回str1，如果N= 2，返回str2，等等。如果N小于1或大于参数个数，返回NULL。ELT()是FIELD()反运算。
mysql> select ELT(1, 'ej', 'Heja', 'hej', 'foo');
    -> 'ej'
mysql> select ELT(4, 'ej', 'Heja', 'hej', 'foo');
    -> 'foo'

 

 

 

==================== FIELD(str,str1,str2,str3,...)
返回str在str1, str2, str3, ...清单的索引。如果str没找到，返回0。FIELD()是ELT()反运算。
mysql> select FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');
    -> 2
mysql> select FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo');
    -> 0