[TOC] ## dsn ``` mysql:dbname=demo;host=127.0.0.1[;port=3306][;charset=utf8] ``` ## 数据库可以远程登录 ``` mysql> GRANT ALL ON *.* TO 'username'@'%' IDENTIFIED BY 'password' mysql> flush privileges; ``` ## HAVING 用法 用于配合group方法完成从分组的结果中筛选 `select user_id from im_log_201905 GROUP BY USER_ID HAVING count(user_id)>500` `$this->field('username,max(score)')->group('user_id')->having('count(test_time)>3')->select(); ` ## group_concat - 分组排序功能 [参考网址](https://www.cnblogs.com/zhwbqd/p/4205821.html) 1. 简单demo ``` #以id 进行分组 ,把相同id 的name 用 , 隔开 select id,group_concat(name) as names from aa group by id; +------+--------------------+ | id| names | +------+--------------------+ |1 | 10,20,20| |2 | 20 | |3 | 200,500| +------+--------------------+ ``` 2. 如:获取所有用户最近的一笔消费 ```sql //通过对 money的create_time排序来获取最近一笔输出 select t.user_id, group_concat( t.money order by t.create_time desc ) moneys ... group by t.user_id ``` ## 对表中的字段进行替换 ``` //对write_price表中所有phone的157 替换为168 update write_price set phone=replace(phone, 157, 168); ``` ## 开启全局日志 ``` mysql> SHOW VARIABLES LIKE 'general%'; //查看是否开启 mysql> set GLOBAL general_log='ON'; //开启 ``` > 日志路径为 `SHOW VARIABLES LIKE 'general%'` 显示的目录 ## 开启慢查询 1. 查看慢查询的状态和保存位置 ``` mysql> show variables like 'slow_query%'; ``` 2. 查看 慢查询事件 ``` mysql> show variables like 'long_query_time'; ``` 3. 设置慢查询 > 慢日志输出的内容有两个,第一执行时间过长(大于设置的long_query_time阈值);第二未使用索引,或者未使用最优的索引。 方法一: mysql> set global slow_query_log='ON'; //设置慢查询日志存放的位置 mysql> set global slow_query_log_file='/usr/local/mysql/data/slow.log'; //查询超过1秒就记录 mysql> set global long_query_time=1; 方法二: `my.ini` slow_query_log = ON slow_query_log_file = /usr/local/mysql/data/slow.log long_query_time = 1 //================================// 分析慢查询语句 分析出使用频率最高的前50条慢sql: /usr/local/services/mysql/bin/mysqldumpslow -s c -t 50 VM_166_154-slow.log 如只需分析处理速度最慢的10条sql: /usr/local/services/mysql/bin/mysqldumpslow -t 10 VM_166_154-slow. 4. 重启 mysqk 5. 测试 `select sleep(2);` ## 关闭查询缓存 添加`SQL_NO_CACHE` `SELECT SQL_NO_CACHE * form table_name` ## 不精确的count `SHOW TABLE STATUS like "im_msg_201905" ` ## 判断表是否存在 `SHOW TABLES LIKE "im_msg_20190"` ## cpu 占用过高 `show full processlist 查看慢查询` ## 时间字段|日期时间类型 |占用空间 |日期格式 |最小值 |最大值 |零值表示| |---|---|---|---|---|---| |DATETIME |8 bytes |YYYY-MM-DD HH:MM:SS |1000-01-01 00:00:00| 9999-12-31 23:59:59.999999| |TIMESTAMP |4 bytes |YYYY-MM-DD HH:MM:SS |1970-01-01 00:00:01| 2038-01-19 03:14:07| |DATE | 4 bytes| YYYY-MM-DD| 1000-01-01|9999-12-31| 0000-00-00| |TIME | 3 bytes| HH:MM:SS| -838:59:59 |838:59:59| 00:00:00| |EAR | 1 bytes| YYYY| 1901| 2155 |0000| > 对于TIMESTAMP,它把客户端插入的时间从当前时区转化为UTC(世界标准时间)进行存储。查询时,将其又转化为客户端当前时区进行返回。 > 而对于DATETIME,不做任何改变,基本上是原样输入和输出。 而对于DATETIME,不做任何改变,基本上是原样输入和输出。 ## hash(索引) 与 树(tree) - 在单条查询记录时.hash 比 tree 快 - 排序查询(`group by` , `order by`, `< >`) tree比hash快 - InnoDB并不支持哈希索引 ## update 只设置一次起作用 `update socre=new_xxx form user where socre=xxx` ## mysql5.7 以上 取消严格模式 `/etc/mysql/conf.d/disable_strict_mode.cnf ` ``` [mysqld] sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION ``` ## 循环执行的sql操作的 过程 ``` DROP PROCEDURE IF EXISTS proc_initData; DELIMITER $ CREATE PROCEDURE proc_initData() BEGIN DECLARE i INT DEFAULT 1; DECLARE c VARCHAR(10); DECLARE dept VARCHAR(10); DECLARE tree_id VARCHAR(10); set c="hh3k2"; set dept="7700164"; set tree_id="05D6CA4F-50AC-984C-87AE-D6727AB62"; WHILE i<=10 DO INSERT INTO hs_user(user_id,user_login,user_name,user_status,USER_PWD,USER_PWD_TYPE) VALUES(concat(c,i),concat(c,i),concat(c,i),1,'202cb962ac59075b964b07152d234b70',1); insert into hs_user_tree(tree_id,dept_id,user_id,user_name) values(concat(tree_id,i),dept,concat(c,i),concat(c,i)); SET i = i+1; END WHILE; END $ CALL proc_initData(); ```