MySQL的一些小技巧（持续更新）

1.巧用RAND()提取随机行

在MySQL中，产生随机数的方法是RAND()函数，可以利用这个函数与ORDER BY子句一起完成随机抽取某些行的功能。它的原理其实就是ORDER BY RAND()能够把数据随机排序。

2.利用GROUP BY的WITH ROLLUP子句做统计

在SQL语句中，使用GROUP BY的WITH ROLLUP子句可以检索出更多的分组聚合信息。它不仅能像一般的GROUP BY语句那样检索出各组的聚合信息，还能检索出本组类整体聚合信息。

当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，因为它们两者是互相排斥的。

WITH ROLLUP反映的是一个OLAP思想，也就是说这一个GROUP BY语句执行完成后可以满足用户想要得到的任何一个分组以及分组组合的聚合信息值。

3.删除表中的重复数据

在需要给表中某些字段加唯一索引时，而字段中又存在需要重复清理数据的问题，不少DBA都应该遇到过。一般在处理时总是想在数据库中只保留一条，其他的删除，但是这样的SQL写出来总是效率不高，怎么办？其实可以转换思路，把重复的都选出一条出来，存到一张临时表，然后删除原表中所有存在重复的，再把临时表的数据库全部插入原库，这是比较通用并且高效的做法。

4.大量数据导入MySQL数据库加快速度

对于MyISAM存储引擎的表，可以通过以下方式快速的导入大量的数据。

ALTER TABLE tbl_name DISABLE KEYS;
loading the data
ALTER TABLE tbl_name ENABLE KEYS;

DISABLE KEYS和ENABLE KEYS用来打开或者关闭MyISAM表非唯一索引的更新。

InnoDB因为主键聚集索引的关系，如果没有主键或者主键非序列的情况下，导入会越来越慢，如何快速的迁移数据到InnoDB？借助MyISAM的力量是很靠谱的。

先关闭InnoDB的Buffer Pool，把内存空出来，建一张没有任何索引的MyISAM表，然后只管插入吧，concurrent_insert=2，在文件末尾并发插入。插入完成后，ALTER TABLE把索引加上，记得还有ENGINE=InnoDB，就把MyISAM转到InnoDB了，这样的速度远比直接往InnoDB里插乱序数据来得快。

5.使用ENUM和auto_increment，按照enum分类进行分类编号

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
);
 
INSERT INTO animals (grp,name) VALUES 
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

查询条件为:select * from animals order by grp,id

结果为

+--------+----+---------+

| grp    | id | name    |

+--------+----+---------+

| fish   |  1 | lax     |

| mammal |  1 | dog     |

| mammal |  2 | cat     |

| mammal |  3 | whale   |

| bird   |  1 | penguin |

| bird   |  2 | ostrich |

+--------+----+---------+