Mysql用法汇总

mysql用法总结及整理

1、group by ...HAVING 获取分组大于某个总数的的前多少位

SELECT user_id, count(*) as total FROM `blog_index_tbl` WHERE  add_time BETWEEN UNIX_TIMESTAMP('2016-01-01 00:00:00') AND UNIX_TIMESTAMP('2016-12-31 23:59:59')  GROUP BY user_id  HAVING total > 1 ORDER BY total DESC;

2、MySQL开发实践8问，你能hold住几个？

最近研发的项目对DB依赖比较重，梳理了这段时间使用MySQL遇到的8个比较具有代表性的问题，答案也比较偏自己的开发实践，没有DBA专业和深入，有出入的请使劲拍砖！...

1. MySQL读写性能是多少，有哪些性能相关的配置参数？

2. MySQL负载高时，如何找到是由哪些SQL引起的？

3. 如何针对具体的SQL做优化？

4. SQL层面已难以优化，请求量继续增大时的应对策略？

5. MySQL如何做主从数据同步？

6. 如何防止DB误操作和做好容灾？

7. 该选择MySQL哪种存储引擎，Innodb具有什么特性？

8. MySQL内部结构有哪些层次？

3、MySQL表复制

复制表结构+复制表数据

mysql> create table t3 like t1;
mysql> INSERT INTO t3 SELECT  * t1;

注意：如果需要把表中的某一个字段拷贝到另一个表中对应的字段处，就可以使用相应的字段。

4、MySQL事务处理

MyIsam 引擎不支持事务处理，必须使用 innoDB

然后进行 commit 命令提交，数据才能被真正删除，否则该操作会锁行，其他的不能进行操作。

1、关闭自动提交功能：

SET autocommit=0;

2、删除id为1的用户：

DELETE FROM test_tbl WHERE id=1;

3、在表界面手动删除id=1的用户

在开启事务的情况下，另一个用户在表界面手动删除id=1的用户，会出现锁行的情况出现

4、提交事务后，删除的数据才会被从表中真正的删除。

commit

综上，如果有事务，则其他操作会被锁行，等到事务提交后，才能对锁行的进行编辑、删除操作。

关于事务的一篇文章，大家可以看看：

MySQL默认操作模式就是 autocommit 自动提交模式。这就表示除非显式地开始一个事务，否则每个查询都被当做一个单独的事务自动执行。我们可以通过设置autocommit的值改变是否是自动提交autocommit模式。

通过以下命令可以查看当前autocommit模式

mysql> show variables like 'autocommit';
Variable_name	Value
autocommit	ON

1 row in set (0.04 sec)

从查询结果中，我们发现Value的值是ON，表示autocommit开启。我们可以通过以下SQL语句改变这个模式

mysql> set autocommit = 0;

值0和OFF都是一样的，当然，1也就表示ON。通过以上设置autocommit=0，则用户将一直处于某个事务中，直到执行一条commit提交或rollback语句才会结束当前事务重新开始一个新的事务。

举个例子：
张三给李四转账500元。那么在数据库中应该是以下操作：

• 1，先查询张三的账户余额是否足够

• 2，张三的账户上减去500元

• 3，李四的账户上加上500元

以上三个步骤就可以放在一个事务中执行提交，要么全部执行要么全部不执行，如果一切都OK就commit提交永久性更改数据；如果出错则rollback回滚到更改前的状态。

利用事务处理就不会出现张三的钱少了李四的账户却没有增加500元或者张三的钱没有减去李四的账户却加了500元。

MySQL默认的存储引擎是MyISAM，MyISAM存储引擎不支持事务处理，所以改变autocommit没有什么作用。但不会报错，所以要使用事务处理的童鞋一定要确定你所操作的表示支持事务处理的，如InnoDB。如果不知道表的存储引擎可以通过查看建表语句查看建表的时候有没有指定事务类型的存储引擎，如果没有指定存储引擎默认则是MyISAM不支持事务的存储引擎。

当然，事务处理是为了保障表数据原子性、一致性、隔离性、持久性。这些都是要消耗系统资源的，要谨慎选择。

php中使用事物实例

$state = 0;
  // 添加事物处理
try {
    // 开启事物
    $GLOBALS['db']->beginTransaction();

    // 更新管理平台密码
    $state = $GLOBALS['db']->query("update admin_user set password='$password_confirm' where user_id=$user_id");
    if($state != true)
    {
        throw new Exception ('更新管理平台密码失败！');
    }

    $ret = $this->modify_ldap_pwd($user_name, $user_password_old, $user_password_confirm);
    if(!$ret)
    {
        throw new Exception ('更新LDAP密码失败！');
    }

    // 提交事物
    $GLOBALS['db']->commit();
    $state = 1;
}
catch (Exception $e)
{
    // 回滚
    $GLOBALS['db']->rollBack();
}

4、MySQL表外键

一个表的外键必定是另外一个表的主键，只有innodB支持外键和事务处理。

5、删除表数据主键从1开始

在数据库应用，我们经常要用到唯一编号，以标识记录。在MySQL中可通过数据列的AUTO_INCREMENT属性来自动生成。mysql支持多种数据表，每种数据表的自增属性都有差异，这里将介绍各种数据表里的数据列自增属性。

数据库中设置了自增列,有时候需要清楚数据库从新录入数据.最常见的做法就是使用sql语句"delete 表明名"或是直接选中数据,然后删除数据.但是再次插入数据的时候,你就会发现自增列会从上次删除数据的最大值的下一个值开始,而不是从1开始的.有没有一种感觉,感觉挺苦恼的.
现在就交你一种方法,让你删除数据后,再次插入记录的时候,自增列从1开始.

truncate table 你的表名 // 这样不但将数据全部删除，而且重新定位自增的字段

truncate table `goods_tbl`;

mysql 小数点精度问题

今天在设计数据表时，突然发现原来FLOAT是很不靠谱的，所以在这里建议大家换成DOUBLE类型，
原因是：

在mysql手册中讲到，在MySQL中的所有计算都是使用双精度完成的，使用float(单精度)会有误差，出现意想不到的结果。
在我们查询数据时，MySQL使用64位十进制数值的精度执行DECIMAL操作，float(5.54) = 5.54 如果出现精度丢失，这个是不等的。这样，本来我们应该能查到的数据就会莫名其妙的消失。

---

相关文章：
mysql查询更新时的锁表机制分析(只介绍了MYISAM)
MySQL事务autocommit自动提交