使用技巧

命令行时存储结果

mysql> tee output.txt
Logging to file 'output.txt'
mysql> notee
Outfile disabled.
或者
mysql> \T output.txt
Logging to file 'output.txt'
mysql> \t
Outfile disabled.
这个类似于sqlplus的spool功能，可以将命令行中的结果保存到外部文件中。如果指定已经存在的文件，则结果会附加到文件中。

命令行乱码问题:

在MySQL命令行输入:show variables like 'character_set_%';
查看当前配置的编码；
在MySQL命令行输入 :set names utf8;
则等同于
set character_set_client=utf8;
set character_set_results=utf8;
set character_set_connection=utf8;
这三条命令
在命令行插入数据之前
执行命令set names gbk;或者set names gb2312;
再插入
查询数据之前
执行命令set names gbk;或者set names gb2312;
完了之后再set names utf8;
这样就能正常插入和查询中文并且保持数据库编码为utf8

更新用户权限
- flush privileges; (在mysql交互中执行)
- mysqladmin flush-privileges (在操作系统中运行)
- mysqladmin reload (不推荐)
mysql表随着更新和删除操作的发生, 优化表:
- optimize table tablename
- myisamchk -r table
- myisamchk —sort-index —sort-records=1 pathtomysqldatadirectory//.MYI

监控sql语句

代码如下:

#!/bin/bash
#this script used montor mysql network traffic.echo sql
tcpdump -i eth0 -s 0 -l -w - dst port 3306 | strings | perl -e '
 while(<>) { chomp; next if /^[^ ]+[ ]*$/;
     if(/^(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER|CALL)/i)
     {
         if (defined $q) { print "$q\n"; }
         $q=$_;
     } else {
         $_ =~ s/^[ \t]+//; $q.=" $_";
     }
 }'

常用功能

解析sql语句执行:

show status like 'Handler_read%';
SHOW INDEX FROM 201406_world_cup;
explain select * from 201406_world_cup where game_code='2014071210192';

sql查询相关度文章

<?php
$keyword = ![pic]('hello','hello2');
    $field = array('title', 'keywords', 'product_model'); //需要做匹配查询的字段
    $s = array(4, 3, 1); //权重,title字段匹配积分4分，content字段匹配积2分，最后按积分排序
    $keyword_len = count($keyword);
    $field_len = count($field);
    $c = 0;
    for($i=0; $i < $keyword_len; $i++)
    {
        for($j=0; $j < $field_len; $j++)
        {
            $clause![pic]($c) = " (".$field![pic]($j)." LIKE '%".$keyword![pic]($i)."%') ";
            $score![pic]($c) = " IF(LOCATE('".$keyword![pic]($i)."', ".$field![pic]($j)."), ".$s![pic]($j).", 0) ";
            $c++;
        }
    }

    $sql = "SELECT *, (" . implode("+", $score) . ") AS score FROM `#@__archives`
        WHERE arcrank > -1 AND (" . implode(" OR ", $clause) . ") ORDER BY score DESC";

    var_dump($sql);

容易踩坑

mysql left join 问题

SELECT `ced`.*, `re`.`crm_name`
FROM `table_a` `ced`
LEFT JOIN `table_b` `re`
ON re.resource_id=ced.resource_id
and ced.bill_id='e3e84c4c-0c60-e511-856c-00505688367e'
WHERE `ced`.`resource_id` IN (47, 35)
ORDER BY `ced`.`resource_id`

Left join时需要注意, 如果是左表的条件过滤, 需要添加到where中, 否则不生效。
right join同理
on 后面为match-condition
where 为where-condition
Left, right join时on条件对匹配数据进行筛选, 即只对副表起过滤作用
详见: http://dev.mysql.com/doc/refman/5.7/en/join.html