mysql命令速记

binlog
20150730
=========================================
1.列出binlog及大小
SHOW MASTER LOGS
2.当前binlog位置
SHOW MASTER STATUS
3.列出binlog信息
show binlog events in 'mysql-bin.0000xx' from 13634\G

20150731
=========================================
1.查询事件位置
mysqlbinlog mysql-bin.000005|egrep '^# at'
2.查询连接id
SELECT CONNECTION_ID()；
show full processlist;

20150804
=========================================
1.hash分片方式
SELECT CRC32(111)%100;
2.表结构优化
SELECT * FROM wifidog_page_access_recorder PROCEDURE ANALYSE();

20150810
=========================================
外键查询
SELECT
  CONCAT(table_schema, '.', table_name) fk_tab,                   -- 外键所在schema及表名
  CONCAT(referenced_table_schema,'.',referenced_table_name) ref,  -- 被引用表的schema,表名
  REFERENCED_COLUMN_NAME -- 被引用表的列名
FROM
  information_schema.`KEY_COLUMN_USAGE`
WHERE referenced_table_name IS NOT NULL
  AND table_name = 'xxx'
  AND column_name = 'yyy'

库表名大小写控制
lower_case_table_names=1  -- 在my.cnf中设置，或强制以 -O lower_case_table_names=1 参数启动 mysqld，这样MySQL 将在创建与查找时将所有的表名自动转换为小写字符

20150814
=========================================
字符串转换为日期
SELECT STR_TO_DATE('14 August 2015 18:11:31', '%d %M %Y %H:%i:%s') date_time
+---------------------+
| date_time           |
+---------------------+
| 2015-08-14 18:11:31 |
+---------------------+

20150821
=========================================
查询表信息
SHOW TABLE STATUS LIKE 'user' \G
                Rows: 6
 Avg_row_length: 129
       Data_length: 776
Max_data_length: 281474976710655
     Index_length: 2048

20150922
=========================================
修改列字符集校验
ALTER TABLE `radius`.`radcheck` MODIFY username VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_general_ci;

20151021
=========================================
SELECT WEEK(NOW()),YEARWEEK(NOW()),UNIX_TIMESTAMP(NOW()),FROM_UNIXTIME(1445407465);