第六章静态查询分析：使用自定义的变量_使用自定义变量的查询无法使用查询缓存-优快云博客

本文链接：https://blog.youkuaiyun.com/u012129607/article/details/78725823

使用自定义的变量：
缺点：

使用自定义变量的查询，不能使用查询缓存。
生命周期在一个连接中有效，不能用他们来做连接间的通信
如果使用连接池会有变量污染的情况
自定义变量在 mysql 5.0之前是大小写敏感，版本问题，以及代码问题
不能显式的声明自定义变量，定义的时候如果整数类型就赋值为 0，浮点型 0.0 ，字符型 ‘’
：= 优先级很低，在与其他sql 同个连接中执行时最好加 ()

使用场景：

查询运算时，计算总数和平均值
模拟GROUP语句中的函数 FIRST() LAST()
计算一个大表的MD5散列值
编写一个样本处理函数，当样本中的数值超过某个边界值时候，将其变为0
模拟读写游标
在SHOW 语句的WHERE 字句中加入变量值

查排名这种先group by，然后再查临时表里面的数据

SET @curr_cnt:=0,@prev_cnt:=0,@rank:=0;
SELECT actor_id,
  @curr_cnt:= cnt AS cnt,
  @rank :=IF(@prev_cnt <> @curr_cnt,@rank+1,@rank) AS rank,
  @prev_cnt :=@curr_cnt AS dummy
FROM (
    SELECT actor_id,COUNT(*) AS cnt
    FROM film_actor
    GROUP BY actor_id
    ORDER BY cnt DESC
    LIMIT 10
)as der;

eg2:
查询刚刚插入的数据

UPDATE t1 SET lastUpdate = NOW() WHERE id=1 AND @now := NOW();
SELECT @now;

统计更新和插入的数量
INSERT INTO ON DUPLICATE KEY UPDATE 与 REPLACE 区别。转自：http://blog.itpub.net/29733787/viewspace-1399583/
INSERT INTO ON DUPLICATE KEY UPDATE 缺点：
1. 当主键或唯一键冲突时，执行更新操作，否则执行插入操作，auto_increment始终累加。
2. 更新操作受多个唯一键影响，在MySQL5.6.6之后的SBR中被标记为不安全的
3. DELAYED选项将被忽略
4. MySQL5.6.6之前，对于表级锁存储引擎(MyISAM)，该语句会锁定分区表中所有分区。MySQL5.6.6之后，只会锁定所更新的分区(InnoDB无影响)
5. INSERT … SELECT ON DUPLICATE KEY UPDATE可能导致主从数据不一致，因此该语句在MySQL5.6.4之后的SBR中被标记为不安全的。

REPLACE 特点：
1. 当主键或唯一键冲突时，执行删除操作，然后执行插入操作，否则直接执行插入操作。
2. 使用REPLACE时，必须同时拥有INSERT及DELETE权限
3. 删除操作受多个唯一键影响，受影响的行数是删除和插入的行的总和
4. REPLACE … SELECT可能导致主从数据不一致，因此该语句在MySQL5.6.4之后的SBR中被标记为不安全的。
5. REPLACE表不支持子查询中查询同一个表
REPLACE INTO test SET a = 14 ,b= (select b from test where b = 12) ,c=’12’,d=12;
6. MySQL5.6.6之前，对于表级锁存储引擎(MyISAM)，该语句会锁定分区表中所有分区，即使使用REPLACE … PARTITION语句。MySQL5.6.6之后，如不更新分区列，只锁定与WHERE子句匹配的分区，如更新分区列，整个表将被锁定

REPLACE算法（LOAD DATA… REPLACE）：

尝试向表中插入新记录
因为主键或唯一索引中的重复键错误导致插入失败
从表中删除与重复键值冲突的记录
再次向表中插入新记录

总结：
INSERT INTO ON DUPLICATE KEY UPDATE
可以自定义的更新一些字段，并且可以在update字段中做一些运算。
replate
只能运算好。然后更新替换或者插入

避免遇到重复值时插入失败。

INSERT INTO t1 (c1, c2) VALUES (4,4),(2,1)
ON DUPLICATE KEY UPDATE
     c1 = VALUES(c1) + (0 * (@x :=@x+1))

引入自定义变量，使条件和赋值发生在同一时间。

SET @rownum :=0; SELECT actor_id, @rownum AS rownum FROM actor WHERE
(@rownum := @rownum +1) <=1;

**MYSQL 无需在返回值中新增额外的列，这样的函数有
LEAST GREATEST LENGTH ISNULL NULLIFL IF COALESCE

GREATEST 在一组数据中，取出最大值
LEAST 在一组数据中，取出最小值
COALESCE 在一组参数中，选择第一个已经被定义的变量**

偷懒写 Uninon all

SELECT id FROM users WHERE ID =123
UNION ALL
SELECT id FROM users_archived WHERE id =123;

即使users表中已经找到了记录，上面的查询还是回到users_archived 中再查一遍。

可以写成

SELECT GREATEST(@found :=-1, id) AS id,'users' AS which_tbl
FROM users WHERE id =1 
UNION ALL
    SELECTG id,'users_archived' FROM users_archived WHERE id =1 AND @found IS NULL
UNION ALL
    SELECT 1,'reset' FROM DUAL WHERE (@found := NULL) IS NOT NULL

首先赋值，
最后一句用于重置@found 的值，来防止影响下一次循环。

使用MYSQL 来执行队列 ——-不研究这个了………………现在基本都用redis或memcache实现。

SELECT FOR UPDATE  为了防止同时有两个线程执行代码导致数据覆盖的问题。

BEGIN;
SELECT id FROM unsend_emails
    WHERE owner =0 AND status ='unsend'
    LIMIT 10 FOR UPDATE;
UPDATE unsend_emails
    SET status ='claimed', owner = CONNECTION_ID()
WHERE id IN(123,456,789);
COMMIT;

#不同条件更新不同的值demo
UPDATE vip_member 
SET 
   start_at = CASE
              WHEN end_at < NOW() 
                 THEN NOW()
              ELSE start_at
              END,
   end_at = CASE
            WHEN end_at < NOW()
               THEN DATE_ADD(NOW(), INTERVAL #duration:INTEGER# MONTH)
            ELSE DATE_ADD(end_at, INTERVAL #duration:INTEGER# MONTH)
            END,
   active_status=1,
   updated_at=NOW()
WHERE uid=#uid:BIGINT#
LIMIT 1;