标签：mysql

mysql cpu使用率过高解决方法

1 找出运行比较多的mysql语句 进行优化http://newmiracle.cn/?p=1810 ps:这种解决的时候开下就好 解决好了 就关闭吧 毕竟占很多资源
2 找到运行超过1秒的mysql语句 进行优化 http://newmiracle.cn/?p=1589

mysql批量更新写法

  $namedmp=filter($_POST['namedmp']);
          $namedsp=filter($_POST['namedsp']);
          $nameula=filter($_POST['nameula']);
          $namenva=filter($_POST['namenva']);
          $namedcrm=filter($_POST['namedcrm']);


          $sql='UPDATE wz_chanpinjieshao
    SET titile = CASE id 
        WHEN 1 THEN ?
        WHEN 2 THEN ?
        WHEN 3 THEN ?
        WHEN 4 THEN ?
        WHEN 5 THEN ?
    END
WHERE id IN (1,2,3,4,5)';
        $stmt = \Db::getStmt($sql);
        $isOk=$stmt->execute(array($namedmp,$namedsp,$nameula,$namenva,$namedcrm));
        if($isOk){
            echo json_encode(array('success' => 1, 'msg' => '更新成功', 'data' => ''));
            exit();
        }else{
            echo json_encode(array('success' => 1, 'msg' => '网络繁忙', 'data' => ''));
            exit();
        }

更新多个字段sql语句

UPDATE wz_chanpinjieshao 
  SET title = CASE id WHEN 1 THEN 11 WHEN 2 THEN 22 WHEN 3 THEN 33 WHEN 4 THEN 44 WHEN 5 THEN 55 END,
     summary = CASE id WHEN 1 THEN 11 WHEN 2 THEN 22 WHEN 3 THEN 33 WHEN 4 THEN 44 WHEN 5 THEN 55 END 
     WHERE id IN (1,2,3,4,5)

下面是封装版

 public function updatechanpinjieshao(){
        $nameupdate_ziduan_list=explode(',',$_POST['nameupdate_ziduan']);
        $namelist=array();
        $duogeziduan='';
        foreach ($nameupdate_ziduan_list as $k=>$v) {

            $namedmp = filter($_POST['namedmp_' . $v]);
            array_push($namelist,$namedmp);
            $namedsp = filter($_POST['namedsp_' . $v]);
            array_push($namelist,$namedsp);
            $nameula = filter($_POST['nameula_' . $v]);
            array_push($namelist,$nameula);
            $namenva = filter($_POST['namenva_' . $v]);
            array_push($namelist,$namenva);
            $namedcrm = filter($_POST['namedcrm_' . $v]);
            array_push($namelist,$namedcrm);

            $duogeziduan.= $v . ' = CASE id WHEN 1 THEN ? WHEN 2 THEN ? WHEN 3 THEN ?  WHEN 4 THEN ?  WHEN 5 THEN ? END,';
        }
            $duogeziduan=rtrim($duogeziduan,",");

             $sql = 'UPDATE wz_chanpinjieshao SET '.$duogeziduan.' WHERE id IN (1,2,3,4,5)';


            $stmt = \Db::getStmt($sql);

            $isOk = $stmt->execute($namelist);

        if($isOk){
            echo json_encode(array('success' => 1, 'msg' => '更新成功', 'data' => ''));
            exit();
        }else{
            echo json_encode(array('success' => 1, 'msg' => '网络繁忙', 'data' => ''));
            exit();
        }

    }

mysql查看正在运行的语句 并且查看运行最多的mysql语句

MySQL 打开 general log 后，所有的查询语句都会记录在 general log 文件，文件为只读方式，但这样general log文件会非常大，所以默认是关闭的。
但有时需要查错等原因，暂时需要打开general log。
打开方法：

 select version();//我的是5.6是支持一个日志的开启。
     
     set global general_log=1;//开启查询日志
     
     set global log_output='TABLE'；//general_log支持输出到table： 如果开启了table就会输出到数据库表中 不会输出到文件中
 
     select * from mysql.general_log\G;//查询所有sql语句的使用
      
     select * from (select argument, count(*) as number from mysql.general_log  group by argument)t 
     order by number DESC;//降序排列使用最多的sql语句，值是mysql 的基本功哈。

建议开了1分钟后可以关了 可以进行优化了 然后优化完了 可以清除表再开启测试

set global general_log = off; // 关闭查询日志
show global variables like '%general%';

mysql left join 右表数据不唯一的情况解决方法

member 表
id	username
1	fdipzone
2	terry



member_login_log 表
id	uid	logindate
1	1	2015-01-01
2	2	2015-01-01
3	1	2015-01-02
4	2	2015-01-02
5	2	2015-01-03

select a.id, a.username, b.logindate  
from member as a   
left join (select uid, max(logindate) as logindate from member_login_log group by uid) as b  
on a.id = b.uid; 

ps:使用left join的两个表，最好是1:1 或 1:0的关系，这样可以保证A表的记录全部显示，B表显示符合条件的记录。
如果B表符合条件的记录不唯一，就需要检查表设计是否合理了。

mysql navcat备份使用详解

点击备份 然后新建备份 然后选择要备份的表 就可以了

以后这个表删除了 内容变更了 都可以点击 还原备份就可以了

mysql互换表中两列数据

update product set original_price=price,price=original_price;

上面sql语句显然不可取
因为先执行original_price=price , original_price的值已经更新为price，
然后执行price=original_price，

正确如下

update product as a, product as b set a.original_price=b.price, a.price=b.original_price where a.id=b.id;

a和b个人觉得应该是2个临时表 最后一定要用where 不用where会出现不对应的情况

mysql You can’t specify target table for update in FROM clause解决方法
出现这个错误的原因是不能在同一个sql语句中，先select同一个表的某些值，然后再update这个表。

mysql> update message set content='Hello World' where id in(select min(id) from message group by uid);
ERROR 1093 (HY000): You can't specify target table 'message' for update in FROM clause

因为在同一个sql语句中，先select出message表中每个用户消息的最小id值，然后再更新message表，因此会出现 ERROR 1093 (HY000): You can’t specify target table ‘message’ for update in FROM clause 这个错误。

解决方法：select的结果再通过一个中间表select多一次，就可以避免这个错误

update message set content='Hello World' where id in( select min_id from ( select min(id) as min_id from message group by uid) as a );

mysql 连接闪断自动重连的方法(用在后台运行中的PHP代码)
当mysql断开连接 $_instance这个还是有值得 所以会报错 MySQL server has gone away 这个地方需要捕捉异常才可以或许到
需要 清空连接 $_instance 这样就可以重新连接 就会报错了

<?php
// 数据库操作类
class DB{

    // 保存数据库连接
    private static $_instance = null;

    // 连接数据库
    public static function get_conn($config){
        if(isset(self::$_instance) && !empty(self::$_instance)){
            return self::$_instance;
        }

        $dbhost = $config['host'];
        $dbname = $config['dbname'];
        $dbuser = $config['user'];
        $dbpasswd = $config['password'];
        $pconnect = $config['pconnect'];
        $charset = $config['charset'];

        $dsn = "mysql:host=$dbhost;dbname=$dbname;";
        try {
            $h_param = array(
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,   //设置错误级别
            );
            if ($charset != '') {
                $h_param[PDO::MYSQL_ATTR_INIT_COMMAND] = 'SET NAMES ' . $charset; //设置默认编码
            }
         
            if ($pconnect) {
                $h_param[PDO::ATTR_PERSISTENT] = true;   //是否是长连接
            }
            $conn = new PDO($dsn, $dbuser, $dbpasswd, $h_param);

        } catch (PDOException $e) {
            throw new ErrorException('Unable to connect to db server. Error:' . $e->getMessage(), 31);
        }

        self::$_instance = $conn;
        return $conn;
    }

    // 执行查询
    public static function query($dbconn, $sqlstr, $condparam){
        $sth = $dbconn->prepare($sqlstr);
        try{
            $sth->execute($condparam);
        } catch (PDOException $e) {
            echo $e->getMessage().PHP_EOL;
            self::reset_connect($e->getMessage()); // 出错时调用重置连接
        }
        $result = $sth->fetchAll(PDO::FETCH_ASSOC);
        return $result;
    }

    // 重置连接
    public static function reset_connect($err_msg){
        if(strpos($err_msg, 'MySQL server has gone away')!==false){
            self::$_instance = null;
        }
    }

}
?>

PDO 查询mysql返回字段int变为String型解决方法
使用PDO查询mysql数据库时，执行prepare,execute后，返回的字段数据全都变为字符型。

例如id在数据库中是Int的，查询后返回是String型。

对于PHP这种弱类型的语言，影响不大。在做API返回数据时，如果类型与数据库不一致，对于Java和Objective C这些强类型，影响就很大了。

<?php
$pdo = new PDO($dsn, $user, $pass, $param);

// 在创建连接后，加入
$pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
?>

这两句设置的作用
PDO::ATTR_STRINGIFY_FETCHES 提取的时候将数值转换为字符串。
PDO::ATTR_EMULATE_PREPARES 启用或禁用预处理语句的模拟。

mysql 查看当前使用的配置文件my.cnf的方法

ps aux|grep mysql|grep 'my.cnf'

如果上面的命令没有输出，表示没有设置使用指定目录的my.cnf。

如果没有设置使用指定目录的my.cnf，mysql启动时会读取安装目录根目录及默认目录下的my.cnf文件。
mysql –help|grep ‘my.cnf’
/etc/my.cnf, /etc/mysql/my.cnf, /usr/local/etc/my.cnf, ~/.my.cnf 这些就是mysql默认会搜寻my.cnf的目录，顺序排前的优先。

如果没有设置使用指定目录my.cnf文件及默认读取目录没有my.cnf文件，表示mysql启动时并没有加载配置文件，而是使用默认配置。

需要修改配置，可以在mysql默认读取的目录中，创建一个my.cnf文件(例如:/etc/my.cnf)，把需要修改的配置内容写入，重启mysql后即可生效。