mysql数据库笔记（一）

笔记1：mysql修改字符集
最近线上碰见需要修改表的字符集，从utf8转为utfmb4（想要修改生效，前提是数据库本身的字符集要支持utf8mb4）。
网上搜到两个sql，测试下来，发现第一条sql是会修改原有的存量数据，但是第二条sql不会。

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4;
ALTER TABLE table_name CHARSET=utf8mb4;

后来找到了如下的文章：http://www.fordba.com/mysql-alter-table-charset.html

修改表默认字符集的行为，只是针对新加的字段的在没有指定字符集的时候，给该字段确定字符集。老的字段还是原先的字符集，因此在使用中会出现表的字符集明明是utf8mb4的，但是确存不了emoji。因此，推荐不用要这种方式修改表的默认字符集

笔记2：mysql命令change和modify
当需要修改列名，使用change

 ALTER TABLE t1 CHANGE a b INTEGER;

修改列类型，使用change，必须带上新的列名和老的列名，即使两者相同。或者使用modify修改列类型

 ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;
 ALTER TABLE t1 modify b BIGINT NOT NULL;

笔记3：mysql查看阻塞源头sql
参考链接：http://www.cnblogs.com/kerrycode/p/8948335.html

SELECT b.trx_mysql_thread_id             AS 'blocked_thread_id'
      ,b.trx_query                      AS 'blocked_sql_text'
      ,c.trx_mysql_thread_id             AS 'blocker_thread_id'
      ,c.trx_query                       AS 'blocker_sql_text'
      ,( Unix_timestamp() - Unix_timestamp(c.trx_started) ) 
                              AS 'blocked_time'
FROM   information_schema.innodb_lock_waits a 
    INNER JOIN information_schema.innodb_trx b 
         ON a.requesting_trx_id = b.trx_id 
    INNER JOIN information_schema.innodb_trx c 
         ON a.blocking_trx_id = c.trx_id 
WHERE  ( Unix_timestamp() - Unix_timestamp(c.trx_started) ) > 4; 

代码中的c.id=XXX是上面sql中查出来的blocker_thread_id。

SELECT a.sql_text, 
       c.id, 
       d.trx_started 
FROM   performance_schema.events_statements_current a 
       join performance_schema.threads b 
         ON a.thread_id = b.thread_id 
       join information_schema.processlist c 
         ON b.processlist_id = c.id 
       join information_schema.innodb_trx d 
         ON c.id = d.trx_mysql_thread_id 
where c.id=XXXXX
ORDER  BY d.trx_started;

注意：第二个sql查出来的阻塞sql并不一定是源头sql，如果阻塞源头sql是一个会话中执行多条sql，那么查出来的是该会话中的最后一条sql。具体请查看参考链接
关于上述sql中用到的系统表，可以参考：
https://blog.youkuaiyun.com/and1kaney/article/details/51213979。
http://www.ywnds.com/?p=8841
这里简单说明如下
innodb_lock_waits：存储阻塞在Innodb中的事务，包括申请资源的事务，以及阻塞该事务的事务。
requesting_trx_id： 申请锁资源的线程ID
requesting_lock_id： 申请的锁的ID
blocking_trx_id： 阻塞requesting_trx_id的线程ID
blocking_lock_id： 阻塞requesting_trx_id的锁的ID

**innodb_trx：**包含了正在InnoDB引擎中执行的所有事务的信息，包括waiting for a lock和running的事务

**performance_schema.events_statements_current：**可看到每一个session正在执行的sql，哪怕它依旧执行完成了，只是没有提交。但是只能查看到一个事务执行的最后那个sql。
**performance_schema.threads：**监控服务器所有连接
**information_schema.processlist：**显示系统中正在运行的所有进程，也就是当前正在执行的查询。大多数用户可以查看他们自己的进程，但是如果他们拥有process权限，就可以查看所有人的进程，包括密码

information_schema.processlist和events_statements_current可以通过performance_schema.threads表来关联

笔记4：mysql自增id，跳跃增加
segmentfault.com/a/1190000017268633

笔记5：join操作中的on和where
1、on的语义是按照on中定义的条件做第一次筛选，将筛选结果做合并形成临时表
2、where的语义是在临时表中按照where条件做第二次筛选
所以on的优先级高于where，作为连接的字段条件要放在on的条件中。
对于left join 和right join，on中如果有左表和右表的相关条件是会忽略的。
测试用例：

a表
| id | is_deleted |
| 1 | N |
| 2 | N |
| 3 | N |
| 4 | Y |

b表
| id | type | a_id|
| 5 | 1 | 2 |
| 6 | 2 | 3 |
| 7 | 2 | 10 |

语句1

select a.id,a.is_deleted,b.id,b.type,b.a_id 
from a left join b 
on a.id = b.a_id
where a.is_deleted='N' 

结果
| a.id | a.is_deleted | b.id | b.type | b.a_id |
| 1 | N | null | null | null |
| 2 | N | 5 | 1 | 2 |
| 3 | N | 6 | 2 | 3 |

语句2

select a.id,a.is_deleted,b.id,b.type,b.a_id 
from a left join b 
on a.id = b.a_id and a.is_deleted='N' 

结果
| a.id | a.is_deleted | b.id | b.type | b.a_id |
| 1 | N | null | null | null |
| 2 | N | 5 | 1 | 2 |
| 3 | N | 6 | 2 | 3 |
| 4 | Y | null | null | null |

语句3

select a.id,a.is_deleted,b.id,b.type,b.a_id 
from a left join b 
on a.id = b.a_id and a.is_deleted='N'  and b.type=2

结果
| a.id | a.is_deleted | b.id | b.type | b.a_id |
| 1 | N | null | null | null |
| 2 | N | null | null | null |
| 3 | N | 6 | 2 | 3 |
| 4 | Y | null | null | null |

个人总结一下：
left join 左表全部进入临时表，on后面的a表相关的条件没有生效，b表条件生效
所以对于left join来说，连接的字段，右表筛选字段放在on后面
左表字段，临时表的字段筛选放在where后面

对于inner join，ab表的on条件可以都生效，可以减少临时表大小，where后面跟临时表的字段筛选条件

笔记6、索引列为null值
索引不存储NULL值。这句话其实比不严谨。如果采用比较严谨的方式来说：B树索引不存储索引列全为空的记录。如果把这句话用在单列索引上，就是前面提到的B树索引不存储NULL。

如果字段a，有50条记录为null，50条不为null，在a上建立单列索引，那么只有不为null的50条会建立索引，通过explain查看，会发现ref的级别是const的，说明是直接命中。

如果是建立索引（b，a） ，且所有记录的b全部不为null，那么100条记录都会建立索引。

笔记7、update的set字段使用select出来的结果集

UPDATE table_r r INNER JOIN table_u u ON r.col_1= u.col_2 SET r.col_3 = u.col_4;

update table_r r inner join (select col_2,col_4 from table_u) u ON r.col_1= u.col_2 SET r.col_3 = u.col_4;