mysql实用小技巧记录-- 干货

1.根据查询出来的结果修改表信息update
Q.更新每个用户签到的天数
S.场景：上线阶段需要修复数据

CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL DEFAULT '' COMMENT '用户名称',
`sign_days` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '签到天数',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user_sign` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '用户id',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '调用日期，格式：yyyy-mm-dd   (使用CURDATE())',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `user` (`id`, `name`) VALUES (1, '张三'),(2,'李四'),(3,'王五');
INSERT INTO `user_sign` (`user_id`, `create_time`) VALUES 
(1, '2018-05-28 22:55:53'),(1, '2018-05-29 22:55:53'),(1, '2018-05-30 22:55:53'),(1, '2018-05-31 22:55:53'),
(2, '2018-05-27 22:55:53'),(2, '2018-05-29 22:55:53'),(3, '2018-05-25 22:55:53'),(3, '2018-05-26 22:55:53'),(3, '2018-05-30 22:55:53');

-- 连表查看修复数据
update  user u inner join (select u.id ,count(*) cnt from user u 
left join user_sign us on u.id = us.user_id
group by u.id ) us on u.id = us.id set u.sign_days =  us.cnt


2.在查询的结果集里面添加筛选条件 count(distinct if()) 
Q.在30号之后签到的用户有几个
S.场景：查询结果需要去重且条件不能写在where里面
select count(distinct if(create_time > '2018-5-30',user_id,null)) as '人数' from user_sign 


3.查询天数 group_concat(column_name separator '@' ) 
Q.每个用户签到的时间。
S.场景：查询结果对应多条记录，在展示上面指向展示成一条记录
select group_concat(create_time separator '@') from user_sign group by user_id

4.强制使用索引
S.场景：发现创建了索引但是未能命中
提供一个思路，mysql在查询过程中可能出现创建了索引但是未命中的情况。可以在查询的末尾使用 force index(`idx_name`)强制使用索引
select * from table_name force index (index_name) where conditions;
https://blog.csdn.net/zixiao217/article/details/77164163

5.mysql阻塞之后如何快速定位且杀掉阻塞的线程
S.场景:产生慢SQL之后阻塞了主流程，导致后续SQL全部阻塞。需要找到产生慢SQL的用户，将其所有进程全部杀死
select concat('KILL ',id,';') from information_schema.processlist where user='root' 
https://stackoverflow.com/questions/1903838/how-do-i-kill-all-the-processes-in-mysql-show-processlist