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

bitsCN.com

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.MySQL中的一些函数

CONCAT(str1,str2,...) 返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。

mysql> SELECT CONCAT('My', 'S', 'QL');        -> 'MySQL'mysql> SELECT CONCAT('My', NULL, 'QL');        -> NULLmysql> SELECT CONCAT(14.3);        -> '14.3'

group_concat函数，将取得的值用逗号连接。

select group_concat(id) from table_name;

得到的结果是（1，2，3，4，5）
CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END 在第一个方案的返回结果中， value=compare-value。而第二个方案的返回结果是第一种情况的真实结果。如果没有匹配的结果值，则返回结果为ELSE后的结果，如果没有ELSE 部分，则返回值为 NULL。
IF(expr1,expr2,expr3)函数如果expr1为True，则返回expr2，否则返回expr3。 expr1 作为一个整数值进行计算，就是说，假如你正在验证浮点值或字符串值， 那么应该使用比较运算进行检验。

mysql> SELECT IF(1>2,2,3);        -> 3mysql> SELECT IF(1 'yes'mysql> SELECT IF(STRCMP('test','test1'),'no','yes');        -> 'no'

IFNULL(expr1,expr2) 假如expr1 不为 NULL，则 IFNULL() 的返回值为 expr1; 否则其返回值为 expr2。IFNULL()的返回值是数字或是字符串，具体情况取决于其所使用的语境。

mysql> SELECT IFNULL(1,0);        -> 1mysql> SELECT IFNULL(NULL,10);        -> 10mysql> SELECT IFNULL(1/0,10);        -> 10mysql> SELECT IFNULL(1/0,'yes');        -> 'yes'

NULLIF(expr1,expr2) 如果expr1 = expr2 成立，那么返回值为NULL，否则返回值为 expr1。这和CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END相同。

mysql> SELECT NULLIF(1,1);        -> NULLmysql> SELECT NULLIF(1,2);        -> 1

coalesce函数，返回参数中第一个不为空的值

select coalesce(a,b,c) from table_name;

如果a不为null，则选择a；如果a为null ，则选择b；如果b为null，则选择c；如果a、b、c都为null，则返回null。
GREATEST(value1,value2,...) 当有2或多个参数时，返回值为最大(最大值的)参数。比较参数所依据的规律同LEAST()相同。

mysql> SELECT GREATEST(2,0);        -> 2mysql> SELECT GREATEST(34.0,3.0,5.0,767.0);        -> 767.0mysql> SELECT GREATEST('B','A','C');        -> 'C'

UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date) 若无参数调用，则返回一个Unix timestamp ('1970-01-01 00:00:00' GMT 之后的秒数) 作为无符号整数。若用date 来调用UNIX_TIMESTAMP()，它会将参数值以'1970-01-01 00:00:00' GMT后的秒数的形式返回。date 可以是一个DATE 字符串、一个 DATETIME字符串、一个 TIMESTAMP或一个当地时间的YYMMDD 或YYYMMDD格式的数字。

mysql> SELECT UNIX_TIMESTAMP();        -> 882226357mysql> SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');        -> 875996580

TO_DAYS(date) 给定一个日期date, 返回一个天数 (从年份0开始的天数 )。

mysql> SELECT TO_DAYS(950501);        -> 728779mysql> SELECT TO_DAYS('1997-10-07');        -> 729669

MD5(str)，返回字符串str的MD5值。常用于对应用中的数据进行加密。 select MD5('123456')
INET_ATON(IP地址)，返回IP地址的网络字节序表示 INET_NTOA(num)，返回网络字节序代码的ip地址。

4.删除表中的重复数据

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

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

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

ALTER TABLE tbl_name DISABLE KEYS;loading the dataALTER 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里插乱序数据来得快。 bitsCN.com